Storing objects in typed table rows

This topic provides information about storing objects in typed table rows.

About this task

When storing objects as rows in a table, each column of the table contains one attribute of the object. Just as with non-typed tables, you must provide data for all columns that are defined as NOT NULL, including the object identifier column. Because the object identifier column is a REF type, which is strongly typed, you must cast the user-provided object identifier values using the system-generated cast function (which was created for you when you created the structured type). For example, you can store an instance of a person, in a table that contains a column for name and a column for age. First, here is an example of a CREATE TABLE statement for storing instances of Person.

   CREATE TABLE Person OF Person_t
      (REF IS Oid USER GENERATED)

To insert an instance of Person into the table, you can use the following syntax:

   INSERT INTO Person (Oid, Name, Age)
      VALUES(Person_t('a'), 'Andrew', 29);

Table 1. Person typed table
Oid	Name	Age	Address
a	Andrew	29

Your program accesses attributes of the object by accessing the columns of the typed table:

   UPDATE Person
      SET Age=30
      WHERE Name='Andrew';

After the previous UPDATE statement, the table looks like this:

Table 2. Person typed table after update
Oid	Name	Age	Address
a	Andrew	30

Because there is a subtype of Person_t called Employee_t, instances of Employee_t cannot be stored in the Person table and must be stored in a subtable. The following CREATE TABLE statement creates the Employee subtable under the Person table:

   CREATE TABLE Employee OF Employee_t UNDER Person
      INHERIT SELECT PRIVILEGES
      (SerialNum WITH OPTIONS NOT NULL,
      Dept WITH OPTIONS SCOPE BusinessUnit);

And, again, an insert into the Employee table looks like this:

   INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary)
      VALUES (Employee_t('s'), 'Susan', 39, 24001, 37000.48)

Table 3. Employer typed subtable
Oid	Name	Age	Address	SerialNum	Salary	Dept
s	Susan	39		24001	37000.48

If you run the following query, the information for Susan is returned:

   SELECT *
      FROM Employee
      WHERE Name='Susan';

You can access instances of both employees and people just by executing your SQL statement on the Person table. This feature is called substitutability. By executing a query on the table that contains instances that are higher in the type hierarchy, you automatically get instances of types that are lower in the hierarchy. In other words, the Person table logically looks like this to SELECT, UPDATE, and DELETE statements :

Table 4. Person table contains Person and Employee instances
Oid	Name	Age	Address
a	Andrew	30	(null)
s	Susan	39	(null)

If you run the following query, you get an object identifier and Person_t information about both Andrew (a person) and Susan (an employee):

   SELECT *
      FROM Person;