Oracle PL/SQL/Object Oriented Database/Ref

Column "REF" an object type

  
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT (
  2    street_1      VARCHAR2(40),
  3    street_2      VARCHAR2(40),
  4    city          VARCHAR2(40),
  5    state_abbr    VARCHAR2(2),
  6    zip_code      VARCHAR2(5),
  7    phone_number  VARCHAR2(10),
  8    MEMBER PROCEDURE ChangeAddress (
  9      st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
 10      state IN VARCHAR2, zip IN VARCHAR2),
 11    MEMBER FUNCTION getStreet (line_no IN number) RETURN VARCHAR2,
 12    MEMBER FUNCTION getCity RETURN VARCHAR2,
 13    MEMBER FUNCTION getStateAbbr RETURN VARCHAR2,
 14    MEMBER FUNCTION getPostalCode RETURN VARCHAR2,
 15    MEMBER FUNCTION getPhone RETURN VARCHAR2,
 16    MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2)
 17  );
 18  /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY address AS
  2    MEMBER PROCEDURE ChangeAddress (
  3      st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
  4      state IN VARCHAR2, zip IN VARCHAR2) IS
  5    BEGIN
  6      IF (st_1 IS NULL) OR (cty IS NULL) OR
  7         (state IS NULL) OR (zip IS NULL)
  8         OR (upper(state) NOT IN ("AK","AL","AR","AZ","CA","CO",
  9                                  "CT","DC","DE","FL","GA","HI",
 10                                  "IA","ID","IL","IN","KS","KY",
 11                                  "LA","MA","MD","ME","MI","MN",
 12                                  "MO","MS","MT","NC","ND","NE",
 13                                  "NH","NJ","NM","NV","NY","OH",
 14                                  "OK","OR","PA","RI","SC","SD",
 15                                  "TN","TX","UT","VA","VT","WA",
 16                                  "WI","WV","WY"))
 17  OR (zip <> ltrim(to_char(to_number(zip),"09999"))) THEN
 18       RAISE_application_error(-20001,"The new Address is invalid.");
 19      ELSE
 20        street_1 := st_1;
 21        street_2 := st_2;
 22        city := cty;
 23        state_abbr := upper(state);
 24        zip_code := zip;
 25      END IF;
 26    END;
 27
 28    MEMBER FUNCTION getStreet (line_no IN number)
 29      RETURN VARCHAR2 IS
 30    BEGIN
 31      IF line_no = 1 THEN
 32        RETURN street_1;
 33      ELSIF line_no = 2 THEN
 34        RETURN street_2;
 35      ELSE
 36        RETURN " ";   
 37      END IF;
 38    END;
 39
 40    MEMBER FUNCTION getCity RETURN VARCHAR2 IS
 41    BEGIN
 42      RETURN city;
 43    END;
 44
 45    MEMBER FUNCTION getStateAbbr RETURN VARCHAR2 IS
 46    BEGIN
 47      RETURN state_abbr;
 48    END;
 49
 50    MEMBER FUNCTION getPostalCode RETURN VARCHAR2 IS
 51    BEGIN
 52      RETURN zip_code;
 53    END;
 54
 55    MEMBER FUNCTION getPhone RETURN VARCHAR2 IS
 56    BEGIN
 57      RETURN phone_number;
 58    END;
 59
 60    MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2) IS
 61    BEGIN
 62      phone_number := newPhone;
 63    END;
 64  END;
 65  /
Type body created.
SQL> set echo on
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE appartment AS OBJECT (
  2    BldgName          VARCHAR2(40),
  3    BldgAddress       address,
  4    BldgMgr           INTEGER,
  5    MEMBER PROCEDURE  ChangeMgr (NewMgr IN INTEGER),
  6    ORDER MEMBER FUNCTION Compare (OtherAppartment IN appartment)
  7        RETURN INTEGER
  8    );
  9  /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY appartment AS
  2    MEMBER PROCEDURE  ChangeMgr(NewMgr IN INTEGER) IS
  3      BEGIN
  4        BldgMgr := NewMgr;
  5      END;
  6
  7    ORDER MEMBER FUNCTION Compare (OtherAppartment IN appartment)
  8    RETURN INTEGER IS
  9        BldgName1     VARCHAR2(40);
 10        BldgName2     VARCHAR2(40);
 11      BEGIN
 12        BldgName1 := upper(ltrim(rtrim(BldgName)));
 13        BldgName2 := upper(ltrim(rtrim(OtherAppartment.BldgName)));
 14
 15        IF BldgName1 = BldgName2 THEN
 16          RETURN 0;
 17        ELSIF BldgName1 < BldgName2 THEN
 18          RETURN -1;
 19        ELSE
 20          RETURN 1;
 21        END IF;
 22      END;
 23  END;
 24  /
Type body created.
SQL> CREATE TABLE employee(
  2           emp_id           INTEGER,
  3           emp_name         VARCHAR2(32),
  4           supervised_by    INTEGER,
  5           pay_rate         NUMBER(9,2),
  6           pay_type         CHAR,
  7           emp_bldg         REF appartment);
Table created.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL> drop type appartment;
Type dropped.
SQL> --

Create table with ref

 

SQL> CREATE OR REPLACE TYPE address_obj 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 ADDRESS_OBJ;
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF address_obj scope is address_table);
Table created.
SQL>
SQL>
SQL> INSERT INTO client SELECT "Walsh", REF(aa) FROM address_table aa;
0 rows created.
SQL>
SQL> -- SELECTing Individual Columns in TCROs
SQL>
SQL>
SQL> SELECT c.name, c.address.city FROM client c;
no rows selected
SQL>
SQL>
SQL>
SQL> drop table client;
Table dropped.
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>

Delete records

 

SQL> CREATE OR REPLACE TYPE address_obj 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 ADDRESS_OBJ;
Table created.
SQL>
SQL> INSERT INTO address_table VALUES (ADDRESS_OBJ("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF address_obj scope is address_table);
Table created.
SQL>
SQL> INSERT INTO client SELECT "Walsh", REF(aa) FROM address_table aa;
1 row created.
SQL>
SQL>
SQL> -- SELECTing Individual Columns in TCROs
SQL>

SQL>
SQL>
SQL> -- Deleting Referenced Rows
SQL>
SQL>
SQL> SELECT * FROM address_table;
STREET               CITY                 ST ZIP
-------------------- -------------------- -- -----
1 A St.              Mobile               AL 36608

SQL>
SQL> -- Now delete a row from Address_table:
SQL>
SQL> DELETE FROM address_table WHERE zip = "32563";
0 rows deleted.
SQL>
SQL> -- And now, SELECT from the Client table that contains a reference to the Address_table:
SQL>
SQL> SELECT * FROM client;
NAME      ADDRESS
--------- --------------------------------------------------
Walsh     0000220208707EBDF1B9E344FDB604B14DA624CBA048316BEA
          1E2D4128AC1F28472E126764

SQL>
SQL> SELECT name, DEREF(address)
  2  FROM client;
NAME
---------
DEREF(ADDRESS)(STREET, CITY, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Walsh
ADDRESS_OBJ("1 A St.", "Mobile", "AL", "36608")

SQL>
SQL>
SQL>
SQL> drop table client;
Table dropped.
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>
SQL>

Insert with ref

 

SQL> CREATE OR REPLACE TYPE address_obj 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 ADDRESS_OBJ;
Table created.
SQL>
SQL> INSERT INTO address_table VALUES (ADDRESS_OBJ("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF address_obj scope is address_table);
Table created.
SQL>
SQL>
SQL> INSERT INTO client SELECT "Walsh", REF(aa) FROM address_table aa;
1 row created.
SQL>
SQL> -- SELECTing Individual Columns in TCROs
SQL>
SQL>
SQL> SELECT c.name, c.address.city FROM client c;
NAME      ADDRESS.CITY
--------- --------------------
Walsh     Mobile
SQL>
SQL>
SQL>
SQL> drop table client;
Table dropped.
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>

List to list using object references

    
SQL> CREATE OR REPLACE TYPE bookType AS OBJECT
  2     (book_id          VARCHAR2(10),
  3      book_title       VARCHAR2(30))
  4  /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE authorType AS OBJECT
  2     (author_id        VARCHAR2(10),
  3      author_name      VARCHAR2(30))
  4  /
Type created.
SQL>
SQL> CREATE TABLE Book OF bookType
  2     (book_id NOT NULL,
  3      PRIMARY KEY (book_id));
Table created.
SQL>
SQL> CREATE TABLE Author OF authorType
  2     (author_id NOT NULL,
  3      PRIMARY KEY (author_id));
Table created.
SQL>
SQL> CREATE TABLE Write
  2     (Book             REF bookType,
  3      Index_Book       NUMBER NOT NULL,
  4      Author           REF authorType);
Table created.
SQL>
SQL> CREATE TABLE Written_By
  2     (Author           REF authorType,
  3      Index_Author     NUMBER NOT NULL,
  4      Book             REF bookType);
Table created.
SQL>
SQL>
SQL> drop type bookType force;
Type dropped.
SQL> drop type authorType force;
Type dropped.
SQL>
SQL>
SQL> drop table Book;
Table dropped.
SQL> drop table Author;
Table dropped.
SQL> drop table Write;
Table dropped.
SQL> drop table Written_By;
Table dropped.

MAKE_REF and user view

    
SQL>
SQL>
SQL> create type addressType as object
  2  (Street  VARCHAR2(50),
  3   City    VARCHAR2(25),
  4   State   CHAR(2),
  5   Zip     NUMBER);
  6  /
SQL> create type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL> create table emp
  2  (cid NUMBER   primary key,
  3   Name        VARCHAR2(25),
  4   Street      VARCHAR2(50),
  5   City        VARCHAR2(25),
  6   State       CHAR(2),
  7   Zip         NUMBER);
SQL>
SQL>
SQL> create or replace type empType as object
  2  (cid NUMBER,
  3   Name        VARCHAR2(25),
  4   Street      VARCHAR2(50),
  5   City        VARCHAR2(25),
  6   State       CHAR(2),
  7   Zip         NUMBER);
  8  /
SQL>
SQL> create view empView of empType
  2  with object OID (cid) as
  3  select cid, Name, Street, City, State, Zip
  4  from emp;
SQL>
SQL> create table emp_CALL
  2  (cid   NUMBER,
  3   Call_Number   NUMBER,
  4   Call_Date     DATE,
  5   constraint emp_CALL_PK primary key (cid, Call_Number),
  6   constraint emp_CALL_FK foreign key (cid)references emp(cid));
SQL>
SQL>
SQL> create view empCallView as
  2  select MAKE_REF(empView, cid) cid,
  3         Call_Number,
  4         Call_Date
  5    from emp_CALL;
SQL>
SQL> select DEREF(CCOV.cid)
  2    from empCallView CCOV
  3   where Call_Date = TRUNC(SysDate);
SQL>
SQL>
SQL> drop view empCallView;
SQL> drop view empView;
SQL> drop table emp_CALL;
SQL> drop table emp;
SQL>
SQL>
SQL> drop type personType;
SQL>
SQL> drop type addressType;
SQL>