Retrieving and Updating Objects in an Object Table

When retrieving and updating data in an object table, you can choose to treat the table as a normal, relational table, and simply write conventional SELECT, UPDATE, and DELETE queries against it. For example, if you simply wanted to retrieve a list of building names, you could execute the query shown in Listing 13.9.

INPUT/
OUTPUT
Listing 13.9. A simple SELECT against the building table.

SELECT BldgName
  FROM buildings

ORDER BY BldgName;

BLDGNAME
----------------------------------------
East Storage Shed
Headquarters Building
Victor Building

ANALYSIS
Notice that the query is simply a traditional, relational query against the buildings table. Even though buildings is a table of objects, you can still treat it like any other relational table.

Being able to execute a traditional, non_object-oriented query against an object table can be very handy if you have both object-oriented and non_object-oriented programs accessing the same database. A non_object-oriented program would simply treat the building table as if it were any other relational table. It's also worth noting that if you were doing a mass update, it would be more efficient to write one SQL UPDATE statement than it would be to write a

PL/SQL loop to retrieve each object, update it, and save it again.

The VALUE Operator
As you move into object-oriented programming, you will want to retrieve building objects from the building table. With an embedded object, such as address, this was done by retrieving the object column into an object variable. Refer to Listing 13.5, line 5, for an example of this. However, the building object table has no column of type building. It simply has a column for each attribute. So what do you select in order to retrieve the building object? In order to retrieve building objects, you will need to use Oracle8's new VALUE operator. The VALUE operator takes a correlation variable as its argument and returns the value of the object stored in the selected row(s) of the table. Take a look at Listing 13.10, which retrieves all the building objects from the database.

INPUT/
OUTPUT
Listing 13.10. Retrieving from an object table.

 1: SET SERVEROUTPUT ON
 2: DECLARE
 3:   this_building   building;
 4:
 5:   CURSOR all_buildings IS
 6:     SELECT value (b) AS bldg
 7:       FROM buildings b
 8:      ORDER BY b.BldgName;
 9:
10: BEGIN
11:   FOR one_building IN all_buildings LOOP
12:     --Grab a copy of the building object.
13:     this_building := one_building.bldg;
14:     dbms_output.put_line(this_building.BldgName || ` is located in `
15:                      || this_building.BldgAddress.city
16:                      || ` ` || this_building.BldgAddress.state_abbr);
17:   END LOOP;
18:
19:   COMMIT;
20: END;
21: /

East Storage Shed is located in Lansing MI
Headquarters Building is located in Detroit MI
Victor Building is located in Lansing MI

PL/SQL procedure successfully completed.

ANALYSIS
In this example, a cursor was declared (lines 5_8) based on a SQL statement that would select building objects. Notice in line 6 that the result column is given the alias bldg. This makes it easy to reference the object retrieved by the cursor, and is used in line 13. The remainder of the code consists of a simple CURSOR FOR loop that retrieves each building object and displays each building's name and location. Notice in lines 15 and 16 how the dot notation is used to navigate from the building object, to the address object, and finally to the city name and state abbreviation.

Updating an Object Table
The SQL UPDATE statement is used to update object tables. There are two basic parts to an UPDATE statement. The first part contains the SET clause, and specifies which table and column you are updating. The second part contains the WHERE clause, and specifies the search condition used to identify the rows to update. Both of these now have object-oriented variations.

Consider the SET clause first. In order to update the building table, you could write an UPDATE statement that began like this:

UPDATE building
  SET BldgName = `Some Name'
  ...

However, if you wanted to update an entire building object, you would want your UPDATE statement to look like this:

UPDATE building b
  SET b = building(...)
  ...

In the preceding code snippet, the building table has been given a correlation name of b. This correlation name is used to represent the object stored in each row of the table, and new building objects can be assigned to it. In the preceding example, the building constructor was used to generate a new building object.

In addition to setting the new values, there is also the question of the WHERE clause. A traditional WHERE clause for the building table might look like this:

WHERE BldgName = `Victor Building'

In this case the building name is being used to identify the row to be changed. If you are writing a PL/SQL program, you have another option. You can use the object identifier to uniquely identify the row to be changed. This method makes use of the REF operator, which retrieves an object's unique identifier. Here is a short example:

UPDATE building b
...
WHERE REF(b) = SELECT REF(b2) FROM building b2
                WHERE BldgName = `Victor Building'

This example is a bit contrived. Normally you would not use a subselect to retrieve the object identifier. If you were writing PL/SQL code, for example, you might already have the object identifier as a result of having retrieved that object. The use of the REF operator is described more fully in the next section, which is titled "Nesting and Sharing Objects."

The options you have just seen for updating can be mixed and matched, giving you at least four different ways to code UPDATE statements against an object table. The SQL statements in Listing 13.11 show two ways to update the buildings table.

INPUT/
OUTPUT
Listing 13.11. Updating an object table.

1: --For the first update, treat buildings as a traditional table.
2:  UPDATE buildings
3:    SET BldgName = `Software Research'
4:    WHERE BldgName = `Headquarters Building';

1 row updated.

1:  --This update calls the constructor in the SET clause.
2:  UPDATE buildings b
3:     SET b = building(BldgName,BldgAddress,598)
4:   WHERE BldgName = `Victor Building';

                                                           continues