Object References and Object Identifiers : Object Reference Column « Object Oriented « Oracle PL/SQL Tutorial

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

1. You use object references to model relationships between object tables, rather than foreign keys.
2. Object references are defined using the REF type.
3. Object references are basically pointers to objects in an object table.
4. Each object in an object table has a unique object identifier (OID) that you can then store in a REF column.
5. The SCOPE IS clause restricts the object reference to point to objects in a specific table.
6. The following example creates a table named purchases that contains two REF columns:

SQL>

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    MEMBER FUNCTION getByDate RETURN DATE
  9  );
 10  /
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  /
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  /
SQL> CREATE TABLE object_employee OF PersonType;

Table created.

SQL>
SQL>
SQL> CREATE TABLE object_products OF ProductType;

Table created.

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

Table created.

SQL>
SQL> drop table purchases;

Table dropped.

SQL>
SQL> drop table object_employee;

Table dropped.

SQL>
SQL> drop table object_products;

Table dropped.