Updating a Row in the object reference table : Object Reference Column « Object Oriented « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Object Oriented
3. Object Reference Column

SQL>
SQL>
SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
  2    street VARCHAR2(15),
  3    city   VARCHAR2(15),
  4    state  CHAR(2),
  5    zip    VARCHAR2(5)
  6  )
  7  /

Type created.

SQL>
SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
  2    id         NUMBER,
  3    first_name VARCHAR2(10),
  4    last_name  VARCHAR2(10),
  5    dob        DATE,
  6    phone      VARCHAR2(12),
  7    address    AddressType
  8  )
  9  /

Type created.

SQL>
SQL> CREATE TABLE object_customers OF PersonType
  2  /

Table created.

SQL>
SQL> INSERT INTO object_customers VALUES (
  2    PersonType(1, 'John', 'White', '04-FEB-1945', '800-555-5555',
  3      AddressType('2 Ave', 'town', 'MA', '12345')
  4    )
  5  );

1 row created.

SQL>
SQL> INSERT INTO object_customers (
  2    id, first_name, last_name, dob, phone,
  3    address
  4  ) VALUES (
  5    2, 'James', 'Green', '05-FEB-1968', '800-555-4444',
  6    AddressType('3 Ave', 'Town', 'CA', '12345')
  7  );

1 row created.

SQL>
SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
  2    id          NUMBER,
  3    name        VARCHAR2(15),
  4    description VARCHAR2(22),
  5    price       NUMBER(5, 2),
  6    days_valid  NUMBER
  7  )
  8  /

Type created.

SQL>
SQL> CREATE TABLE object_products OF ProductType
  2  /

Table created.

SQL>
SQL> INSERT INTO object_products (
  2    id, name, description, price, days_valid
  3  ) VALUES (
  4    1, 'AAA', 'BBB', 2.99, 5
  5  );

1 row created.

SQL>
SQL> CREATE TABLE purchases (
  2    id       NUMBER PRIMARY KEY,
  3    customer REF PersonType  SCOPE IS object_customers,
  4    product  REF ProductType SCOPE IS object_products
  5  )
  6  /

Table created.

SQL>
SQL> INSERT INTO purchases (
  2    id,
  3    customer,
  4    product
  5  ) VALUES (
  6    1,
  7    (SELECT REF(oc) FROM object_customers oc WHERE oc.id = 1),
  8    (SELECT REF(op) FROM object_products  op WHERE op.id = 1)
  9  );

1 row created.

SQL> select * from purchases;

 ID  CUSTOMER                                                                    PRODUCT
-----------------------------------------------------------------------------------------
  1  000022020818702007EFFB4B20A2177B51CC69DC1F5F9D95B91B624DC88DB51BA9B83230D8  0000220208DC78B0F6B61E431FABCBEF463314969A9109C12B203A4E03B462604D027BE27E


SQL>
SQL> UPDATE purchases SET product = (
  2    SELECT REF(op) FROM object_products op WHERE op.id = 2
  3  ) WHERE id = 1;

1 row updated.

SQL>
SQL> drop table purchases;

Table dropped.

SQL>
SQL> drop table object_products;

Table dropped.

SQL>
SQL> drop table object_customers;

Table dropped.

SQL> drop type persontype;

Type dropped.

SQL> drop type addresstype;

Type dropped.

SQL>