0140-0142

Previous Table of Contents Next

Page 14O

This PL/SQL block constructs a new Person_Type object with the Address attribute initially set to NULL and inserts it into the Persons table. It then constructs a new Address_Type object and passes it to the add_address method of the Person_Type instance. This PL/SQL block highlights the use of constructors, which are generated automatically for every object type. Constructors must be passed a value for each attribute of the object type, ordered as in the type declaration. NULL can be used to initialize any object attribute, including attributes that are objects themselves . The methods of an object are invoked in the same manner as packaged procedures, except that they are accessed through a particular instance of the object instead of through the object type itself. You can use the PL/SQL block shown in Listing 7.4 to display the data that was just inserted.

Listing 7.4. Using object types in PL/SQL.

 DECLARE     ThePerson   Person_Type;     TheAddr     Address_Type; BEGIN     SELECT VALUE(p) INTO ThePerson FROM Persons p WHERE ID = (SELECT MAX(ID) FROM Persons);     DBMS_OUTPUT.PUT_LINE(ThePerson.LastName', `ThePerson.FirstName);     TheAddr := ThePerson.get_address();     DBMS_OUTPUT.PUT_LINE(TheAddr.StreetAddr1);     DBMS_OUTPUT.PUT_LINE(TheAddr.City', `TheAddr.State'  `TheAddr.Zip); END; 

Notice the use of the VALUE operator. As the name implies, it is used to access an object by value. This example also shows how an object's attributes are accessed. You also can use this notation for assignment, as in this code:

 TheAddr.City := `NEW YORK'; 

Depending on the nature of the application, you might want to present a completely object-oriented interface. Using the example in Listing 7.4, you could have accomplished this by providing additional Person_Type methods to encapsulate DML operations. In addition to providing better encapsulation, this also provides an additional layer of abstraction between client applications and the physical structure of the database. Rights to the Persons table would be revoked , and an object would be created to allow client applications to retrieve lists of objects through simple SQL queries. Only select rights would be granted to users of this view. All changes to individual objects would be handled by methods of the object, to which developers and users would be granted the EXECUTE privilege. This design simplifies the client application and provides greater flexibility in the physical design of the database, because it hides the physical structure and the complexity of the SQL and DML statements required to access it. This approach provides object views to retrieve lists and individual objects as needed.

Object Views

Oracle8 Object views are extensions of the standard views provided in previous versions of Oracle. In addition to column data, Object views expose object types, object REFs, VARRAYs,

Page 141

and nested tables. Similar to standard views, DML operations can be performed against Object views, provided that INSTEAD OF triggers are provided.

An Object view exposes at least one object and must contain an object identifier. If the view is based on an object table, the WITH OBJECT OID clause is not necessary, because a unique identifier is generated automatically for the objects in an object table. You can use the WITH OBJECT OID clause to create an Object view from a relational table or to specify a unique ID other than the generated key for object tables. Using the Persons table as an example, you could specify a view against this table as the following:

 CREATE VIEW ov_persons  (ThePerson, City, State) AS     SELECT VALUE(p),            City,            State     FROM Persons p, Addresses     WHERE Address = (SELECT REF(a) FROM Addresses a); 

This view returns a Person_Type object and some basic address information using the nested Address_Type reference in Persons to join to the Addresses table. Note that no OID is specified, so DML statements against this view are not possible. The Person_Type object returned includes all columns of the Persons table, including the Address_Type reference. This reference is not particularly useful unless accessed in a PL/SQL block where it can be manipulated or dereferenced in a SELECT statement. You can use SELECT statements, such as the following statement, to access individual attributes of Person_Type in the first column position:

 SELECT ThePerson.LastName', `ThePerson.FirstName "Full Name" FROM ov_persons WHERE ThePerson.LastName LIKE `S%'; 

The following PL/SQL block displays the full street address from the view:

 DECLARE TheAddr Address_Type; BEGIN SELECT DEREF(ThePerson.Address) INTO TheAddr FROM ov_persons; DBMS_OUTPUT.PUT_LINE(TheAddr.StreetAddr1); DBMS_OUTPUT.PUT_LINE(TheAddr.StreetAddr2); DBMS_OUTPUT.PUT_LINE(TheAddr.City', `TheAddr.State'  `TheAddr.Zip); END; 

You can use a combination of user -defined types and Object views to build an object-oriented interface to access relational tables. Using the Person_Type and Address_Type example, suppose that the Addresses and Persons tables are not object tables but relational tables. Each row in the Persons table contains a foreign key (AddrID) to the Addresses table, and Person_Type has a nested Address_Type (instead of a nested REF Address_Type, as in previous examples).

You could use an Object view to expose Person_Type objects from the relational structures, as demonstrated here:

 CREATE VIEW ov_person OF Person_Type     WITH OBJECT OID(ID) AS     SELECT p.ID      ID,          LastName,          FirstName, 

Page 142

 Address_Type(a.ID, StreetAddr1, StreetAddr2, City, State, Zip)     FROM     Persons p,          Addresses a     WHERE     p.AddrID = a.ID; 

Note that the nested Address_Type must be constructed from the relational table within the view. The WITH OBJECT OID clause is required and must be based on a unique ID.

TIP

Relational tables without primary keys or unique indexes are not suitable for this technique. Using Object views and object types to "hide" relational structures can simplify the process of porting an existing Oracle relational database to an Oracle8 object-relational database.

Object views also can be updateable through the use of INSTEAD OF triggers. Consider the ov_person view defined in the previous example. Only the data stored in the Persons relational table can be modified directly through the view. However, you could use INSTEAD OF triggers to allow DML operations on the full view, including the nested Address_Type. You could use the trigger shown in Listing 7.5 to handle updates on this view.

Listing 7.5. Using a trigger to handle updates on your Object view.

 CREATE TRIGGER tr_ov_person_upd INSTEAD OF UPDATE ON ov_person FOR EACH ROW BEGIN     UPDATE Address SET     StreetAddr1 = :new.Address.StreetAddr1,                 StreetAddr2 = :new.Address.StreetAddr2,                 City = :new.Address.City,                 State =    :new.Address.State,                 Zip = :new.Address.Zip             WHERE ID = :old.Address.ID;     UPDATE Persons SET     LastName = :new.LastName,                        FirstName = :new.FirstName             WHERE ID = :old.ID; END; 

This trigger is invoked whenever an UPDATE statement is issued against the ov_person view. Unlike table triggers, INSTEAD OF triggers are executed instead of the DML statement. In most other respects, the syntax of INSTEAD OF triggers is similar to table triggers. Both use the correlation names :old and :new and may perform DML transactions on other tables and views, including object tables and views. Note that in Listing 7.5, it is assumed that the IDs of the relational tables will not be updated. The trigger is designed to accommodate DML UPDATEs such as this one:

 UPDATE ov_person SET Address = Address_Type(1, '22 E. WASHINGTON', null, `CHICAGO', `IL', 60605) WHERE ID=1; 

Previous Table of Contents Next