Listing 13.11. continued

1 row updated.

COMMIT;

Commit complete.

ANALYSIS
The first update, lines 1_4 of the first segment, treats the building object table as if it were a normal relational table. The second update, lines 1_4 of the second segment, is more interesting. It updates the entire building object and features a call to the building constructor in line 4. The first two arguments passed to the building constructor are actually attributes of the object being updated. The third argument of 598 represents a new building manager assignment. Especially notice in line 4 of the second segment that the correlation name of the table is used as the target of the SET clause. This tells Oracle8 that the entire building object is being updated.

Deleting Objects from an Object Table

Objects can be deleted from an object table through the use of the DELETE statement. The same issues apply to the WHERE clause of a DELETE statement as apply to the WHERE clause of an UPDATE statement. The WHERE clause of a DELETE can use the REF operator and an object identifier to delete a specific object, or it can specify conditions for one or more attributes of the object.

Nesting and Sharing Objects

Objects can be nested inside other objects. They can also be shared, or referenced, by one or more other objects. You have already seen two examples of nested objects. The building object, created in Listing 13.7, contains a nested address object. The employee table also contains an address object, added in Listing 13.3.

Dot Notation

NEW TERM
When objects are nested, it is possible to navigate through the objects using standard dot notation. You already know that in a SQL statement, you can refer to a specific field using the following notation:

TableName.FieldName

The same notation can also be used to reference a specific object attribute, for example:

ObjectVarName.AttributeName

You have already seen examples of this in Listing 13.2, lines 24_29, 33_38, and 42_47. When you have objects that themselves contain objects, you can use this dot notation to navigate your way down to a specific attribute. That's because the nested object is itself an attribute of the containing object, and also contains attributes of its own. So if you had a building object and wanted to know what city the building was in, you could reference

BldgVar.BldgAddress.City

Listing 13.12 shows a brief example of using dot notation on a building object in order to print the city and state.

INPUT/
OUTPUT
Listing 13.12. Using dot notation.

 1: SET SERVEROUTPUT ON
 2: DECLARE
 3:   this_building     building;
 4: BEGIN
 5:   --Retrieve a building object so we can print the attribute values.
 6:   SELECT value(b) INTO this_building
 7:     FROM buildings b
 8:    WHERE BldgName = `East Storage Shed';
 9:
10:   COMMIT;
11:
12:   dbms_output.put_line(this_building.BldgName
13:                       || ` ` || this_building.BldgAddress.city
14:                       || ` ` || this_building.BldgAddress.state_abbr);
15: END;
16: /

East Storage Shed Lansing MI

PL/SQL procedure successfully completed.

ANALYSIS
Line 12 uses dot notation to reference the building name attribute, while lines 13 and 14 use it to navigate through the nested address object to get the city name and state abbreviation.

Object References

NEW TERM
When you store an object as an attribute of another object, the nested object is said to have no visibility outside of the parent. This means that it exists only within the context of the parent object, and that same object cannot be referenced or made part of any other object. It makes sense to nest objects when you are dealing with something like a building address because each building has its own unique address, not shared with any other building.

Consider the case, though, where you want to specify the building in which an employee works. You could modify the employee table and add a column of type building, for example:

ALTER TABLE employee
  ADD (EmpBldg    building);

But this solution has a big problem. Each employee will have their own private building object. If you have 1,000 employees in a building, then there will be 1,000 separate building objects, one for each employee, all containing redundant information. Clearly there has to be another way to deal with this situation, and indeed there is. The solution is to store only a reference to a building object in each employee's record.

NEW TERM
Each object stored in an object table is identified by a unique, system-generated, object identifier. It isn't necessary to know the precise nature of this identifier, and in fact Oracle does not document it. What's important is that given an object identifier, you can easily retrieve the object in question from the database.

As stated earlier, the solution to the problem of relating employees to buildings is to create a reference to the building object in each employee record. The REF keyword is used to do this, and Listing 13.13 shows how.

INPUT/
OUTPUT
Listing 13.13. Creating a reference to building.

ALTER TABLE employee
  ADD (emp_bldg    REF building);

Table altered.

As you can see, this listing adds one column of type REF building to the employee record. The use of the keyword REF tells Oracle that the column will contain only a reference to a building object and not the building object itself.

The REF and DEREF Operators
Oracle8 introduces two new SQL operators named REF and DEREF. The REF operator can be used in a SQL statement to return the object identifier for an object. The DEREF operator does just the opposite. It is used in a SQL statement to retrieve the actual object referenced by an object identifier. Doing this is referred to as dereferencing an object identifier.

The REF operator is used in Listing 13.14 to retrieve a reference to a specific building so that it can be stored in an employee's record. The DEREF operator is used in Listing 13.15 in order to retrieve an employee's building name and display it.