Performing DML on the purchases Table

The purchases table contains a NUMBER column named id , along with two REF columns named customer and product . The purchases table is defined as follows :

 CREATE TABLE purchases (id NUMBER PRIMARY KEY,  customer REF person_typ SCOPE IS object_customers,  product REF product_typ SCOPE IS object_products); 

In this section, you ll see how to perform SQL DML statements to insert and update a row in the purchases table. You ll also see how to select rows from the purchases table.

Inserting a Row into the purchases Table

As I mentioned, each object in an object table has a unique object identifier that you can store in a REF column. You can access this object identifier using the REF() function and store the returned object identifier in a REF column. The following example inserts a row into the purchases table. Notice the use of the REF() function to read the object identifiers for the rows from the object_ customers and object_products tables:

 INSERT INTO purchases (id,  customer,  product) VALUES (1,  (SELECT REF(oc) FROM object_customers oc WHERE oc.id = 1),  (SELECT REF(op) FROM object_products op WHERE op.id = 1)); 

This example records that customer #1 purchased product #1.

Selecting a Row from the purchases Table

The following example selects the row from the purchases table. Notice that the customer and product columns contain long strings of numbers and letters . These are the object identifiers for the rows in the object_customers and object_products tables:

  SELECT *   FROM purchases;  ID ---------- CUSTOMER --------------------------------------------------------------------- PRODUCT ---------------------------------------------------------------------  1 0000220208662E2AB6256711D6A1B50010A4E7AE8A662E2AB3256711D6A1B50010A4E 7AE8A 0000220208662E2AB4256711D6A1B50010A4E7AE8A662E2AB2256711D6A1B50010A4E 7AE8A 

You can access the rows in the object tables that are pointed to by REF column values using the DEREF() function; this function accepts a REF column as a parameter. The following example uses the DEREF() function to access the rows pointed to by the customer and product columns of the purchases table:

  SELECT DEREF(customer), DEREF(product)   FROM purchases;  DEREF(CUSTOMER)(ID, FIRST_NAME, LAST_NAME, DOB, PHONE,  ADDRESS(STREET, CITY, STATE, ZIP)) ------------------------------------------------------ DEREF(PRODUCT)(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID) -------------------------------------------------------- PERSON_TYP(1, 'John', 'Brown', '01-FEB-55', '800-555-1211',  ADDRESS_TYP('2 State Street', 'Beantown', 'MA', '12345')) PRODUCT_TYP(1, 'Pasta', '20 oz bag of pasta', 3.95, 10) 

Updating a Row in the purchases Table

The following example updates the row in the purchases table. Notice that the product column is changed to point to product #2 in the object_products table.

  UPDATE purchases SET product = (   SELECT REF(op) FROM object_products op WHERE op.id = 2   ) WHERE id = 1;  1 row updated.