Просмотр исходного текста страницы Oracle PL/SQL Tutorial/Object Oriented/Object Reference Column

== CREATE a Table that References Our Row Objects==



   
  <!-- start source code -->
   
    <source lang="sql">
SQL>
SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
  2  street VARCHAR2(20),
  3  city VARCHAR2(20),
  4  state CHAR(2),
  5  zip CHAR(5))
  6  /
Type created.
SQL>
SQL> CREATE TABLE address_table OF addressType
  2  /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF addressType scope is address_table)
  3  /
Table created.
SQL>
SQL> DESC client;
 Name         Null?    Type
 --------
 NAME                  VARCHAR2(20)
 ADDRESS               REF OF ADDRESSTYPE
SQL>
SQL> drop table client;
Table dropped.
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop type addresstype;
Type dropped.
SQL>
SQL></source>
    
   

== Inserting a Row into the Object Reference table==



   
  <!-- start source code -->
   
    <source lang="sql">
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", "City", "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  );
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>
SQL> select * from purchases;
 ID         CUSTOMER                                                                           PRODUCT
-----------------------------------------------------------------------------------------------
  1        0000220208F78428F66B3B4357A7E2EE26FB982C67E070E7307E2E4FE6A60A03B82FDF9C49          0000220208473CFDED543D404C920B9AB1E2A37337F5BD01A93C83419084C662D11524FB08

SQL>
SQL> drop table purchases;
Table dropped.
SQL>
SQL> drop table object_products;
Table dropped.
SQL>
SQL> drop table object_customers;
Table dropped.
SQL>
SQL> drop type persontype;
Type dropped.
SQL> drop type addresstype;
Type dropped.
SQL></source>
    
   

== Object References and Object Identifiers==

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


   
  <!-- start source code -->
   
    <source lang="sql">
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.</source>
    
   

== Reference column==



   
  <!-- start source code -->
   
    <source lang="sql">
SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
  2  street VARCHAR2(20),
  3  city VARCHAR2(20),
  4  state CHAR(2),
  5  zip CHAR(5))
  6  /
Type created.
SQL>
SQL> CREATE TABLE address_table OF addressType
  2  /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF addressType scope is address_table)
  3  /
Table created.
SQL>
SQL>
SQL> DESC client;
 Name    Null?    Type
 ---------
 NAME             VARCHAR2(20)
 ADDRESS          REF OF ADDRESSTYPE
SQL>
SQL> INSERT INTO client VALUES ("Jones",null);
1 row created.
SQL>
SQL> SELECT *
  2  FROM client;
NAME                 ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL>
SQL> UPDATE client SET address =
  2  (SELECT REF(aa)
  3  FROM address_table aa
  4  WHERE aa.city LIKE "Mob%")
  5  WHERE name = "Jones"
  6
SQL> select * from client;
NAME                 ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL> DEREF (Dereference) the Row Addresses
SQL> SELECT name, DEREF(address)
  2  FROM client;
NAME
--------------------
DEREF(ADDRESS)(STREET, CITY, STATE, ZIP)
-----------------------------------------
Jones

SQL>
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table client;
Table dropped.
SQL>
SQL> drop type addresstype;
Type dropped.
SQL>
SQL></source>
    
   

== Selecting a Row from the Object reference table==



   
  <!-- start source code -->
   
    <source lang="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;
Table created.
SQL>
SQL> INSERT INTO object_customers VALUES (
  2    PersonType(1, "John", "White", "04-FEB-1945", "800-555-5555",
  3      AddressType("2 Ave", "City", "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  00002202084DE5F9FDE0704131B2DA2477DE28C548B2AF244ECB1E42B686FFFBAD0C5A8874  0000220208A772FB501EEF4A958FA182D42AF06C9072FFE34E8F8E4FC99B4C52F83340B041

SQL>
SQL> drop table purchases;
Table dropped.
SQL>
SQL> drop table object_products;
Table dropped.
SQL>
SQL> drop table object_customers;
Table dropped.
SQL>
SQL> drop type persontype;
Type dropped.
SQL>
SQL> drop type addresstype;
Type dropped.
SQL></source>
    
   

== Updating a Row in the object reference table==



   
  <!-- start source code -->
   
    <source lang="sql">
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></source>
    
   

== You can access the rows in the object tables that are pointed to by REF column values using t REF() function; this function accepts a REF column as a parameter.==



   
  <!-- start source code -->
   
    <source lang="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", "City", "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  );
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>
SQL> select * from purchases;
 ID  CUSTOMER                                                                     PRODUCT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
  1  000022020861FDF674DA3D4B1088D187CB56924C27CD0C0B2EF27A4E5AB731BDCEE0BF1441   0000220208F8102DD78DC54DD0BB75E4AEA43E91BF05A396E7F9534DAE9CE3FE3F78872A74

SQL>
SQL> SELECT DEREF(customer), DEREF(product)
  2  FROM purchases;
DEREF(CUSTOMER)(ID, FIRST_NAME, LAST_NAME, DOB, PHONE, ADDRESS(STREET, CITY, STATE, ZIP))
-------------------------------------------------------------------------------------------
DEREF(PRODUCT)(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID)
------------------------------------------------------------------------------------------
PERSONTYPE(1, "John", "White", "04-FEB-45", "800-555-5555", ADDRESSTYPE("2 Ave", "City", "MA", "12345"))
PRODUCTTYPE(1, "AAA", "BBB", 2.99, 5)

SQL>
SQL> drop table purchases;
Table dropped.
SQL>
SQL> drop table object_products;
Table dropped.
SQL>
SQL> drop table object_customers;
Table dropped.
SQL>
SQL> drop type persontype;
Type dropped.
SQL> drop type addresstype;
Type dropped.
SQL></source>
    
   

== You can access this object identifier using the REF() function and store the returned objectifier in a REF column.==



   
  <!-- start source code -->
   
    <source lang="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", "AA", "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", "City", "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  );
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   00002202089E1785E0E6A5410BA917BACC9F93530B35C60A986EAC46F580144901E1EC0F94  00002202088CDC8C88E9AB403D85085FAE2649DF6E02D3BDE222D24D1E85421901D500B4A2

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></source>