Oracle PL/SQL Tutorial/Object Oriented/Object Reference Column

Материал из SQL эксперт
Версия от 10:04, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

CREATE a Table that References Our Row Objects

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>


Inserting a Row into the Object Reference table

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>


Object References and Object Identifiers

  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.


Reference column

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>


Selecting a Row from the Object reference table

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>


Updating a Row in the object reference table

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>


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.

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>


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

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>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/Object_Oriented/Object_Reference_Column&oldid=2987»