0137-0139

Previous Table of Contents Next

Page 137

 CREATE TYPE Address_Type (     StreetAddr1    VARCHAR2(80),     StreetAddr2    VARCHAR2(80),     City        VARCHAR2(30),     State        CHAR(2),     Zip        NUMBER(9)); 

This type can be used as a row object or a column object. It is used as a row object in the following statement, which creates a table of Address_Type objects:

 CREATE TABLE Addresses OF Address_Type; 

A unique identifier is assigned to each row object. This ID enables row objects to be accessed by reference using the REF operator. Column objects, on the other hand, are not assigned unique identifiers. They exist only in the context of the table in which they are stored and cannot be accessed by reference. The following DDL creates a table that uses Address_Type to store column objects:

 CREATE TABLE Persons (     ID        NUMBER(10) PRIMARY KEY,     LastName    VARCHAR2(30),     FirstName    VARCHAR2(20),     Address        Address_Type); 

In this context, the data for each address column object is stored in the Persons table. There is no unique identifier for an Address_Type object and no way to access an Address_Type object by reference.

Alternatively, an application could use the previous declaration of the Addresses table and define the Persons table as the following:

 CREATE TABLE Persons (     ID        NUMBER(10) PRIMARY KEY,     LastName    VARCHAR2(30),     FirstName    VARCHAR2(20),     Address        REF Address_Type); 

The only difference between this version of the Persons table and the previous version of the Persons table is that only references to Address_Type objects are stored. The actual data for the Address_Type objects is stored externally in the Addresses table. This method allows multiple rows of Persons to reference the same address. It also provides an implied integrity constraint, because only references to valid Address_Types (stored in Addresses or some other object table) can be inserted.

Object types may be nested. Instead of declaring a Persons table containing Address_Type objects, for example, a Person_Type object type containing an Address_Type object can be declared, as shown here:

 CREATE TYPE Person_Type (     ID        NUMBER(10) PRIMARY KEY,     LastName    VARCHAR2(30) NOT NULL,      FirstName    VARCHAR2(20),       Address        Address_Type); 

Page 138

Note that the attributes of an object type can contain constraints as if they were table columns . If a table of row objects will be created, though, it is preferable to add constraints to the table in which the objects will be stored, as shown here:

 CREATE TABLE Persons OF Person_Type (ID PRIMARY KEY, LastName NOT NULL); 

Even though a primary key is defined for the Persons table, a unique ID will be generated for each object stored in the Persons table to be used by Oracle internally to resolve object references. The use of primary or foreign key constraints in object tables is not necessary if the entire design of the system is based on object types. Such constraints can be used to provide relational access paths to ease integration with existing structures migrated from previous versions of Oracle, however. Primary keys on object tables also can be useful in providing an object-comparison routine.

Object types may define methods in addition to attributes. Methods can be used to perform complex calculations, operate on nested objects, and so on. Object methods are defined in the type specification using the MEMBER keyword. A special type of member function used in object comparisons is designated using the MAP keyword. MAP functions must return a scalar datatype, and their use implies an order. Oracle can compare objects for equality by performing a field-by-field comparison, so MAP functions are not required to support this comparison. The following type declaration defines a MAP method and methods to operate on a nested object:

 CREATE TYPE Person_Type (     ID        NUMBER(10),     LastName    VARCHAR2(30),     FirstName    VARCHAR2(20),     Address        REF Address_Type,     MAP MEMBER FUNCTION get_int_ID RETURN NUMBER,     MEMBER FUNCTION get_address RETURN Address_Type,     MEMBER PROCEDURE add_address(NewAddr IN Address_Type),     MEMBER PROCEDURE change_address(NewAddr IN Address_Type),     MEMBER PROCEDURE remove_address(OldAddr IN Address_Type)); 

Given this declaration, the MAP function might return the integer ID. Assuming that the ID is generated by an Oracle sequence, this would order the objects based on when they were created. Note that the declaration holds a nested REF to another object type. Additional member functions were provided to operate on the nested object, which provides a layer of abstraction between the developers using Person_Type and the physical location of the address data. These methods also eliminate the need to use the REF and DEREF operators in the development of client applications.

Similar to the Oracle package supported in previous versions, the type declaration is independent of its implementation. Type bodies, like package bodies, are implemented in PL/SQL and may include private variables and methods. For types that have no members , a body does not need to be implemented. The Person_Type, declared in the previous example, might be implemented as shown in Listing 7.3.

Page 139

Listing 7.3. Creating a new object type.

 CREATE OR REPLACE TYPE BODY Person_Type (     MAP MEMBER FUNCTION get_int_ID RETURN NUMBER IS     BEGIN         RETURN SELF.ID;     END;     MEMBER FUNCTION get_address RETURN Address_Type    IS     BEGIN     DECLARE         AddrOut Address_Type;     BEGIN         SELECT DEREF(Address) INTO AddrOut FROM Persons WHERE ID = SELF.ID;         RETURN AddrOut;     END;     END get_address;     MEMBER PROCEDURE add_address(NewAddr IN Address_Type) IS     BEGIN         INSERT INTO Addresses VALUES (NewAddr);         UPDATE Persons SET Address =             (SELECT REF(a) FROM Addresses a WHERE ID = NewAddr.ID)         WHERE ID = SELF.ID;         COMMIT;     END;     .     .     . END; 

This partial implementation demonstrates some of the new operators for working with Oracle8 object types. REF and DEREF, as mentioned previously, are used to create a reference to an object type and to dereference an object reference, respectively. The SELF keyword works similar to the C++ or Java this pointer; it references the current instance of the object. Note that in the add_address implementation, a row must be inserted into the Addresses table before a valid reference to it can be obtained. The following PL/SQL block demonstrates the use of this object type:

 DECLARE     NewPerson Person_Type;     NewAddr   Address_Type;     NewID      NUMBER; BEGIN     SELECT PersonIDs.NEXTVAL INTO NewID FROM dual;     NewPerson := Person_Type(NewID, `DOE', `JOHN', NULL);     INSERT INTO Persons VALUES (NewPerson);     SELECT AddressIDs.NEXTVAL INTO NewID FROM dual;     NewAddr := Address_Type(NewID, `1 S. Main', null, `ANYWHERE', `NY', 10203);     NewPerson.add_address(NewAddr); END; 
Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net