Type Inheritance

With the release of the Oracle9 i database, you can use object type inheritance . This allows you to define hierarchies of database types. For example, you might want to define a business person object type and have that type inherit existing attributes from person_typ . The business person type could extend person_typ with attributes to store the person s job title and the name of the company they work for. For person_typ to be inherited from, it must be defined using the NOT FINAL clause:

 CREATE TYPE person_typ AS OBJECT (id NUMBER,  first_name VARCHAR2(10),  last_name VARCHAR2(10),  dob DATE,  phone VARCHAR2(12),  address address_typ) NOT FINAL; / 

The NOT FINAL clause indicates that person_typ can be inherited from when defining another type. The default is FINAL , meaning that the object type cannot be inherited from.

Note  

I ve provided a SQL*Plus script named object_schema2.sql in the SQL directory that creates a user named object_user2 with a password of object_password . The object_schema2.sql script creates the types, tables, and performs the various INSERT statements shown in the rest of this chapter. You can run the object_ schema2.sql script if you are using an Oracle9 i database or above.

To have a new type inherit attributes and methods from an existing type, you use the UNDER clause when defining your new type. Our example business person type, which I ll name business_person_typ , uses the UNDER clause to inherit the attributes from person_typ :

 CREATE TYPE business_person_typ UNDER person_typ (title VARCHAR2(20),  company VARCHAR2(20)); / 

In this example, person_typ is known as the supertype , and business_person_typ is known as the subtype . You can then use business_person_typ when defining column objects or object tables. For example, the following statement creates an object table named object_business_customers :

 CREATE TABLE object_business_customers OF business_person_typ; 

The following example inserts a row into object_business_customers . Notice that the two additional title and company attributes are supplied:

 INSERT INTO object_business_customers VALUES (business_person_typ(1, 'John', 'Brown', '01-FEB-1955', '800-555-1211',  address_typ('2 State Street', 'Beantown', 'MA', '12345'),  'Manager', 'XYZ Corp')); 

The final example selects this row:

  SELECT *   FROM object_business_customers;  ID FIRST_NAME LAST_NAME DOB PHONE ---------- ---------- ---------- --------- ------------ ADDRESS(STREET, CITY, STATE, ZIP) -------------------------------------------------------- TITLE COMPANY -------------------- --------------------  1 John Brown 01-FEB-55 800-555-1211 ADDRESS_TYP('2 State Street', 'Beantown', 'MA', '12345') Manager XYZ Corp