4.2 Object Columns and Object Tables
You can use object types to define columns in a table. Such columns are known as object columns because they contain objects rather than scalar values. You can also define tables so that each row in the table represents an instance of an object. Such tables are known as object tables. In this section, you will learn to create both object columns and object tables. You will learn how to add, retrieve, and modify rows containing object columns, and how to add, retrieve, and modify rows in object tables.
4.2.1 Object Columns
An object column is a column in a table that is defined using an object type. (Objects stored in an object column are sometimes referred to as column objects.) The following example creates a table named customers. It contains one object column named customer, which is defined using the t_customer object type:
CREATE TABLE customers ( customer t_customer );
The customer column in this table is used to store objects of the t_customer type. You can see the structure of the table from SQL*Plus by entering the command DESC customers. For example:
SQL> DESC customers; Name Null? Type ------------------ -------- ---------------------------- CUSTOMER T_CUSTOMER
Notice in this example that a single column of type t_customer appears as a column in the table structure.
4.2.2 Object Tables
An object table is a table in which each row contains one object of a given object type. Such objects are known as row objects. You create an object table using the CREATE TABLE OF statement. For example, the following statement creates an object table named customers2 based on the t_customer object type:
CREATE TABLE customers2 OF t_customer;
You can see the structure of this table by entering the command DESC customers2 from SQL*Plus. Notice that the attributes for the t_customer object type appear as columns in the table structure:
SQL> DESC customers2; Name Null? Type ------------------ -------- ------------ ID NUMBER FIRST_NAME VARCHAR2(10) LAST_NAME VARCHAR2(10) DOB DATE PHONE VARCHAR2(15) ADDRESS T_ADDRESS
Because each attribute for the t_customer object type appears to be contained in a separate column in the customers2 table, you can treat the rows in the table in two possible ways:
Although this distinction between how rows seem to be stored may be somewhat abstract, you will soon see that it is important when adding or retrieving rows from an object table. This is because you must use a slightly different syntax for your SQL statements depending on which approach you decide to take.
4.2.3 Adding Objects to Tables
When adding a new row object or column object to a table, you must place the values for the object in a constructor for the object type. In the case of Oracle object types, the name of the constructor is always the same as the name of the object type. The following example shows how to add a row to the customers table. Recall that the customers table is the one with the object column. Notice the use of the t_customer constructor together with the embedded t_address constructor to populate the attributes of the customer object column.
INSERT INTO customers (customer) VALUES ( t_customer(1, 'John', 'Smith', '01-JAN-1965', '650-555-1212', t_address('1 Anystreet', 'Anytown', 'CA', '12345') ) ); When adding a row to the customers2 object table, in which each row represents one object, you can either treat the table as consisting of rows in which each row is a t_customer object, or as a multiple-column table in which each column represents an attribute of the t_customer object type. In reality, each row is always a t_customer object, but you have a choice in how you treat the table in your SQL statements. If you wish to treat each row as an object, include the t_customer constructor in the INSERT statement:
INSERT INTO customers2 VALUES ( t_customer(1, 'John', 'Smith', '01-JAN-1965', '650-555-1212', t_address('1 Anystreet', 'Anytown', 'CA', '12345') ) ); If you wish to treat the customers2 table as consisting of multiple columns, omit the t_customer constructor:
INSERT INTO customers2 (id, first_name, last_name, dob, phone, address) VALUES ( 1, 'John', 'Smith', '01-JAN-1965', '650-555-1212', t_address('1 Anystreet', 'Anytown', 'CA', '12345') ); You can see from this example that although the t_customer constructor is omitted, the t_address constructor is still used. That's because the objects are nested. When you treat the customers2 table as a multi-column table, all the t_customer attributes become columns in the table. The address attribute is a nested object, so it becomes an object column, hence the need for the t_address constructor.
4.2.4 Retrieving Objects
When referencing an individual attribute in an object column, you must use an alias for the table in your SELECT statement. The SELECT statement in the following example retrieves the customer object from the customers table where the id attribute is equal to 1. Notice the use of the alias c for the customers table. Also notice in the output that the attributes for the selected customer and address objects appear within constructors. This is SQL*Plus's way of indicating that objects are being returned.
SQL> SELECT c.customer 2 FROM customers c 3 WHERE c.customer.id = 1; CUSTOMER(ID, FIRST_NAME, LAST_NAME, DOB, PHONE, ADDRESS(STREET, CITY, STATE, ZIP)) ---------------------------------------------------- T_CUSTOMER(1, 'John', 'Smith', '01-JAN-65', '650-555-1212', T_ADDRESS('1 Anystreet', 'Anytown', 'CA', '12345')) In addition to retrieving entire objects, you can also retrieve individual object attributes through the table alias and the object column name. The following example selects the street attribute for the embedded address object from the customers table where the id attribute is equal to 1. Notice the use of the dot notation to navigate from the table alias c to the customer object, then to the nested address object, and finally to the street attribute:
SQL> SELECT c.customer.address.street 2 FROM customers c 3 WHERE c.customer.id = 1; CUSTOMER.ADDRES --------------- 1 Anystreet
When retrieving a row object from an object table (as opposed to an object column), you can select the row using the VALUE operator in a SELECT statement. The VALUE operator accepts a table alias as a parameter and causes Oracle to treat the row as an object. The following statement uses the VALUE operator and selects the row from the customers2 table where the id attribute is equal to 1:
SQL> SELECT VALUE(c) 2 FROM customers2 c 3 WHERE c.id = 1; VALUE(C)(ID, FIRST_NAME, LAST_NAME, DOB, PHONE, ADDRESS(STREET, CITY, STATE, ZIP ----------------------------------------------- T_CUSTOMER(1, 'John', 'Smith', '01-JAN-65', '650-555-1212', T_ADDRESS('1 Anystreet', 'Anytown', 'CA', '12345')) Alternately, because object tables can also be treated as relational tables, you can include each object attribute in a SELECT statement as you would a column of the same name. The following example selects all the attributes for customer #1 from the customers2 table. The results are returned as columns.
SQL> SELECT * 2 FROM customers2 3 WHERE id = 1; ID FIRST_NAME LAST_NAME DOB PHONE --- ---------- ---------- --------- ------------ ADDRESS(STREET, CITY, STATE, ZIP) -------------------------------------------------- 1 John Smith 01-JAN-65 650-555-1212 T_ADDRESS('1 Anystreet', 'Anytown', 'CA', '12345') The customers2 table has a nested object named address. To retrieve individual attributes from the address object, you can use dot notation to navigate from the table to the object to the specific attribute that you want. However, you cannot use the table name itself; instead, you must use a table alias. The following example selects the street attribute for customer #1 from the customers2 table. Notice the use of the table alias c in the dot notation leading up to the street attribute.
SQL> SELECT c.address.street 2 FROM customers2 c 2 WHERE c.id = 1; ADDRESS.STREET --------------- 1 Anystreet
4.2.5 Invoking Object Methods
Recall that the t_customer object type contains an object method named get_age( ), which returns the age of the customer in years. You invoke object methods in a manner similar to that in which you saw attributes retrieved in the previous section. The SELECT statement in the following example invokes the get_age( ) method for the customer object column from the customers table where the id attribute is equal to 1. Once again, notice the use of the table alias and the object column name in the dot notation leading up to the method invocation.
SQL> SELECT c.customer.get_age( ) 2 FROM customers c 3 WHERE c.customer.id = 1; C.CUSTOMER.GET_AGE( ) -------------------- 36
In the next example, the customers2 object table is used. This example invokes the get_age( ) method for the row from the customers2 table where the id attribute is equal to 1. Notice that the object method is invoked directly from the table alias c.
SQL> SELECT c.get_age( ) 2 FROM customers2 c 3 WHERE c.id = 1; C.GET_AGE( ) ----------- 36
4.2.6 Modifying Objects
You can modify an attribute of an object using an UPDATE statement. If you are modifying an object column, then you must use a table alias, which allows you to access the object's attributes via the object column name. The following statement updates the first_name attribute of the customer column in the customers table where the id attribute is equal to 1. Notice the use of the table alias c and the customer object column name, through which the first_name attribute is modified.
UPDATE customers c SET c.customer.first_name = 'Jason' WHERE c.customer.id = 1;
If you are modifying a row object stored in an object table, then you don't have to use a table alias in the UPDATE statement. This is because you can treat the object attributes as regular columns. The following example performs the same update as before, but for the customers2 table. This time, the table alias is omitted.
UPDATE customers2 SET first_name = 'Jason' WHERE id = 1;
You must always use a table alias when modifying an attribute of an embedded object, whether that embedded object is within an object table or within an object column in a relational table. When modifying an attribute in an object column, you must navigate from the table alias to the object column, and then to the object attribute. The following statement updates the street attribute of the embedded address object for customer #1 in the customers table:
UPDATE customers c SET c.customer.address.street = '2 Main Street' WHERE c.customer.id = 1
When dealing with an object table, the task of modifying an attribute of an embedded object is much the same. The difference is that you have one fewer entity to navigate through. The following example performs the same update as before, but for the customers2 table. Notice that the dot notation is shorter because no object column is involved.
UPDATE customers2 c SET c.address.street = '2 Main Street' WHERE c.id = 1;
