/
rem ************************************************************************
rem ** This is a SQL*Plus sript to demonstrate object and object REF 
rem ** manipulation in Pro*C.
rem ** Run this script before executing OTT for the objdemo1.pc program
rem ************************************************************************

CONNECT scott/tiger

rem ** Always drop your objects in reverse dependency order

DROP TABLE employees;
DROP TABLE persons;

DROP TYPE  person;
DROP TYPE  address;


rem ** Create the TYPES, TYPED TABLES and TABLES we need

rem ** Create an abstract data type (ADT) for Address

CREATE TYPE address AS OBJECT
( 
  street        VARCHAR(200),
  city          VARCHAR(20),
  state         CHAR(2),
  zip_code      CHAR(5)
);
/
 
rem ** Create a person ADT containing an embedded Address ADT

CREATE TYPE person AS OBJECT
( 
  name    VARCHAR(20),
  ssn     NUMBER,
  addr    address
);
/

rem ** Create a typed table for person objects 

CREATE TABLE persons OF person;


rem ** Create a relational table with two columns that are REFs to person 
rem ** objects, as well as a column which is an Address ADT.

CREATE TABLE  employees
( empnumber            INTEGER PRIMARY KEY,
  person_data     REF  person,
  manager         REF  person,
  office_addr          address,
  salary               NUMBER
);


rem ** Now let's put in some sample data

rem ** Insert 2 objects into the persons typed table

INSERT INTO persons VALUES (
            person('Alison Laurence', 123456,
            address('100 Geary Street', 'San Francisco', 'CA','94013')));
INSERT INTO persons VALUES (
            person('William Bates', 234567,
            address('123 Main Street', 'Anytown', 'WA', '97818')));

rem ** Put a row in the employees table

INSERT INTO employees (empnumber, office_addr, salary) VALUES 
            (1001,
             address('500 Oracle Parkway', 'Redwood City', 'CA', '94065'),
             50000); 

rem ** Set the manager and person REFs for the employee
 
UPDATE employees 
  SET manager =  
    (SELECT REF(p) FROM persons p WHERE p.name = 'Alison Laurence');

UPDATE employees 
  SET person_data =  
    (SELECT REF(p) FROM persons p WHERE p.name = 'William Bates');


rem ** select the data back out ...

rem SELECT empnumber, salary,
rem     DEREF(person_data),
rem     DEREF(manager),
rem        office_addr.state,
rem        office_addr.zip_code
rem        FROM employees 
rem             WHERE empnumber = 1001;

COMMIT;

EXIT;
