ANALYSIS
Lines 5_7 of the first segment retrieve the address object into the emp_addr variable for the employee whose phone number you want to change. In line 10 of the first segment, the setPhone method is used to update the phone number. At this point, only the object in memory has the updated phone number. Lines 12_14 of the first segment update the employee's record, storing the new value of the address object.

It is also possible to update an employee's phone number using only one update, rather than the three steps of retrieve, modify, and store shown in Listing 13.5. This can be accomplished by creating an entirely new address object and assigning it to the employee's home_address field. Listing 13.6 shows the phone number for employee number 598 being modified using this method.

INPUT/
OUTPUT
Listing 13.6. Updating an address object.

1: UPDATE employee
2:    SET home_address  = address(home_address.street_1,
3:                        home_address.street_2, home_address.city,
4:                        home_address.state_abbr, home_address.zip_code,
5:                        `5173433333')
6:  WHERE emp_id = 598;

1 row updated.

COMMIT;

Commit complete.

ANALYSIS
The SET clause of this UPDATE statement uses the information from the existing address object, plus a new phone number, to instantiate an entirely new address object. This is done in lines 2_5 by calling the object type's constructor and passing attributes of the original address as arguments. The home_address column is then set to the value of this new object.

Object Tables

NEW TERM
Another way to store objects is in an object table. An object table is a relational table defined so that each column in the table matches one of the attributes of the object. Consequently each row of the table is used to store one instance of an object. In addition to the columns for the object's attributes, an object table also has an additional column that is used to contain an object identifier. This object identifier is an Oracle-generated value that uniquely identifies each object in the database.

Take a look at Listing 13.7. It defines a building type for the sample database and then creates an object table that can store instances of that type.

INPUT/
OUTPUT
Listing 13.7. The building object.

 1: CREATE OR REPLACE TYPE building AS OBJECT (
 2:   BldgName          VARCHAR2(40),
 3:   BldgAddress       address,
 4:   BldgMgr           INTEGER,
 5:   MEMBER PROCEDURE  ChangeMgr (NewMgr IN INTEGER),
 6:   ORDER MEMBER FUNCTION Compare (OtherBuilding IN building)
 7:       RETURN INTEGER
 8:   );

Type created.

 1: CREATE OR REPLACE TYPE BODY building AS
 2:   MEMBER PROCEDURE  ChangeMgr(NewMgr IN INTEGER) IS
 3:     BEGIN
 4:       BldgMgr := NewMgr;
 5:     END;
 6:
 7:   ORDER MEMBER FUNCTION Compare (OtherBuilding IN building)
 8:   RETURN INTEGER IS
 9:       BldgName1     VARCHAR2(40);
10:       BldgName2     building.BldgName%TYPE;
11:     BEGIN
12:       --Grab the two building names for comparison.
13:       --Make sure that we don't get messed up by leading/trailing
14:       --spaces or by case.
15:       BldgName1 := upper(ltrim(rtrim(BldgName)));
16:       BldgName2 := upper(ltrim(rtrim(OtherBuilding.BldgName)));
17:
18:       --Return the appropriate value to indicate the order of
19:       --this object vs OtherBuilding.
20:       IF BldgName1 = BldgName2 THEN
21:         RETURN 0;
22:       ELSIF BldgName1 < BldgName2 THEN
23:         RETURN -1;
24:       ELSE
25:         RETURN 1;
26:       END IF;
27:     END;
28: END;
29: /

Type body created.

CREATE TABLE buildings OF building;

Table created.

ANALYSIS
Lines 1_8 of the first segment contain the building object's type definition. As you can see, the building object has three attributes containing the building's name, the building's address, and the employee ID of the building manager. The second attribute is interesting because it itself is an object. Objects can be nested in this manner to any level.

The ORDER function, in line 6 of the first segment, enables you to compare two objects of type building for equality or to see which is greater. You decide what "equality" means when you write the function. The keyword ORDER tells Oracle which member function to call when doing comparisons. Comparing objects using order functions is described later in this chapter in the section titled "Comparing Objects."

Lines 1 through 29 of the second segment define the object body, which contains the definitions for the two member functions ChangeMgr and Compare.

The last command in the listing, shown in the third segment, is very important. This is a new form of the CREATE TABLE statement, which creates an object table for objects of type building. You will be using this table later in this chapter. Go ahead and look at the table's structure by typing this command:

describe buildings

When you use an object table to store your objects, they have visibility outside the table. Other objects can be linked to them, referencing them by their object identifiers. Another advantage of object tables is that they can also be queried just like any other relational tables. This gives you some flexibility and enables you to mix and match relational and object-oriented methods in your software development projects.

Storing Objects in an Object Table

You can insert information about buildings into the object table you just created by using a SQL INSERT statement. Instead of a values list containing separate values for each attribute, use the building object's constructor to create an object. This one object becomes the only value in the values list.

Type in the statements shown in Listing 13.8 in order to insert a few building objects. These will be used in later examples showing you how to update object tables and how to link objects in the database.

INPUT/
OUTPUT
Listing 13.8. Inserting some building objects.

1: INSERT INTO buildings
2:   values (building(`Victor Building',
3:           address(`203 Washington Square',' `,'Lansing',
             Â'MI','48823',' `),
4:           597));

1 row created.

1: INSERT INTO buildings
2:   values (building(`East Storage Shed',
3:           address(`1400 Abbott Rd','','Lansing','MI','48823',''),
4:           598));

                                                            continues

Listing 13.8. continued

1 row created.

1: INSERT INTO buildings
2:   values (building(`Headquarters Building',
3:           address(`150 West Jefferson','','Detroit','MI','48226',''),
4:           599));

1 row created.

SELECT * from buildings;

BLDGNAME
----------------------------------------
BLDGADDRESS(STREET_1, STREET_2, CITY, STATE_ABBR, ZIP_CODE, PHONE_NUMBER)
-------------------------------------------------------------------------
  BLDGMGR
---------
Victor Building
ADDRESS(`203 Washington Square', ` `, `Lansing', `MI', `48823', ` `)
      597

East Storage Shed
ADDRESS(`1400 Abbott Rd', NULL, `Lansing', `MI', `48823', NULL)
      598

Headquarters Building
ADDRESS(`150 West Jefferson', NULL, `Detroit', `MI', `48226', NULL)
      599

COMMIT;

Commit complete.

ANALYSIS
In each of the preceding inserts, the building constructor was called in order to instantiate a building object. Because one of the building attributes is an address object, the address constructor was also called to create that object. The SELECT statement at the end shows you that the building data was inserted properly.