Mega Code Archive
Object References and Object Identifiers
<![CDATA[
You use object references to model relationships between object tables, rather than foreign keys.
Object references are defined using the REF type.
Object references are basically pointers to objects in an object table.
Each object in an object table has a unique object identifier (OID) that you can then store in a REF column.
The SCOPE IS clause restricts the object reference to point to objects in a specific table.
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> 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.
]]>