12 Using PL/SQL With Object Types

Example 12-1 Working With Object Types

CREATE TYPE address_typ AS OBJECT ( 
   street          VARCHAR2(30),
   city            VARCHAR2(20),
   state           CHAR(2),
   postal_code     VARCHAR2(6) );
/
CREATE TYPE employee_typ AS OBJECT (
  employee_id       NUMBER(6),
  first_name        VARCHAR2(20),
  last_name         VARCHAR2(25),
  email             VARCHAR2(25),
  phone_number      VARCHAR2(20),
  hire_date         DATE,
  job_id            VARCHAR2(10),
  salary            NUMBER(8,2),
  commission_pct    NUMBER(2,2),
  manager_id        NUMBER(6),
  department_id     NUMBER(4),
  address           address_typ,
  MAP MEMBER FUNCTION get_idno RETURN NUMBER,
  MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) );
/
CREATE TYPE BODY employee_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN employee_id;
  END;
  MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(first_name || ' '  || last_name);
    DBMS_OUTPUT.PUT_LINE(address.street);
    DBMS_OUTPUT.PUT_LINE(address.city || ', '  || address.state || ' ' ||
                         address.postal_code);   
  END;
END;
/
CREATE TABLE employee_tab OF employee_typ;

Example 12-10 Declaring a Nested Table in SQL

CREATE TYPE CourseList AS TABLE OF VARCHAR2(10)  -- define type
/
CREATE TYPE student AS OBJECT (  -- create object
   id_num  INTEGER(4),
   name    VARCHAR2(25),
   address VARCHAR2(35),
   status  CHAR(2),
   courses CourseList);  -- declare nested table as attribute
/
CREATE TABLE sophomores of student
  NESTED TABLE courses STORE AS courses_nt;

The identifier courses represents an entire nested table. Each element of courses stores the name of a college course such as 'Math 1020'.

Example 12-11 Creating a Table with a Varray Column

-- Each project has a 16-character code name.
-- We will store up to 50 projects at a time in a database column.
CREATE TYPE ProjectList AS VARRAY(50) OF VARCHAR2(16);
/
CREATE TABLE dept_projects (  -- create database table
   dept_id  NUMBER(2),
   name     VARCHAR2(15),
   budget   NUMBER(11,2),
-- Each department can have up to 50 projects.
   projects ProjectList);

Example 12-12 Varray Constructor Within a SQL Statement

BEGIN
  INSERT INTO dept_projects
    VALUES(60, 'Security', 750400,
      ProjectList('New Badges', 'Track Computers', 'Check Exits'));
END;
/

Example 12-13 Nested Table Constructor Within a SQL Statement

CREATE TABLE sophomores of student
  NESTED TABLE courses STORE AS courses_nt;
BEGIN
   INSERT INTO sophomores
      VALUES (5035, 'Janet Alvarez', '122 Broad St', 'FT',
         CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100'));
END;
/

Example 12-15 Using INSERT, UPDATE, DELETE, and SELECT Statements With Nested Tables

CREATE TYPE dnames_tab AS TABLE OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_tab) 
   NESTED TABLE dept_names STORE AS dnames_nt;
BEGIN
   INSERT INTO depts VALUES('Europe', dnames_tab('Shipping','Sales','Finance'));
   INSERT INTO depts VALUES('Americas', dnames_tab('Sales','Finance','Shipping'));
   INSERT INTO depts VALUES('Asia', dnames_tab('Finance','Payroll'));
   COMMIT;
END;
/
DECLARE
-- Type declaration is not needed, because PL/SQL can access the SQL object type
-- TYPE dnames_tab IS TABLE OF VARCHAR2(30); not needed
-- Declare a variable that can hold a set of department names
   v_dnames dnames_tab;
-- Declare a record that can hold a row from the table
-- One of the record fields is a set of department names
   v_depts depts%ROWTYPE;
   new_dnames dnames_tab;
BEGIN
-- Look up a region and query just the associated department names
   SELECT dept_names INTO v_dnames FROM depts WHERE region = 'Europe';
   FOR i IN v_dnames.FIRST .. v_dnames.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Department names: ' || v_dnames(i));
   END LOOP;
-- Look up a region and query the entire row
   SELECT * INTO v_depts FROM depts WHERE region = 'Asia';
-- Now dept_names is a field in a record, so we access it with dot notation
   FOR i IN v_depts.dept_names.FIRST .. v_depts.dept_names.LAST LOOP
-- Because we have all the table columns in the record, we can refer to region
     DBMS_OUTPUT.PUT_LINE(v_depts.region || ' dept_names = ' || 
                          v_depts.dept_names(i));
   END LOOP;
-- We can replace a set of department names with a new collection
-- in an UPDATE statement
   new_dnames := dnames_tab('Sales','Payroll','Shipping');
   UPDATE depts SET dept_names = new_dnames WHERE region = 'Europe';
-- Or we can modify the original collection and use it in the UPDATE.
-- We'll add a new final element and fill in a value
   v_depts.dept_names.EXTEND(1);
   v_depts.dept_names(v_depts.dept_names.COUNT) := 'Finance';
   UPDATE depts SET dept_names = v_depts.dept_names 
     WHERE region = v_depts.region;
-- We can even treat the nested table column like a real table and
-- insert, update, or delete elements. The TABLE operator makes the statement
-- apply to the nested table produced by the subquery.
   INSERT INTO TABLE(SELECT dept_names FROM depts WHERE region = 'Asia') 
     VALUES('Sales');
   DELETE FROM TABLE(SELECT dept_names FROM depts WHERE region = 'Asia') 
      WHERE column_value = 'Payroll';
   UPDATE TABLE(SELECT dept_names FROM depts WHERE region = 'Americas')
      SET column_value = 'Payroll' WHERE column_value = 'Finance';
   COMMIT;
END;
/

Example 12-16 Using INSERT, UPDATE, DELETE, and SELECT Statements With Varrays

-- By using a varray, we put an upper limit on the number of elements
-- and ensure they always come back in the same order
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
BEGIN
   INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
   INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
   INSERT INTO depts 
     VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
   COMMIT;
END;
/
DECLARE
   new_dnames dnames_var := dnames_var('Benefits', 'Advertising', 'Contracting', 
                                       'Executive', 'Marketing');
   some_dnames dnames_var;
BEGIN
   UPDATE depts SET dept_names  = new_dnames WHERE region = 'Europe';
   COMMIT;
   SELECT dept_names INTO some_dnames FROM depts WHERE region = 'Europe';
   FOR i IN some_dnames.FIRST .. some_dnames.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('dept_names = ' || some_dnames(i));
   END LOOP;
END;
/

Example 12-17 Using BULK COLLECT with Nested Tables

CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
BEGIN
   INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
   INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
   INSERT INTO depts 
     VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
   COMMIT;
END;
/
DECLARE
   TYPE dnames_tab IS TABLE OF dnames_var;
   v_depts dnames_tab;
BEGIN
    SELECT dept_names BULK COLLECT INTO v_depts FROM depts;
    DBMS_OUTPUT.PUT_LINE(v_depts.COUNT); -- prints 3
END;
/

Example 12-18 TEAMS Package Using Dynamic SQL for Object Types and Collections

CREATE TYPE person_typ AS OBJECT (name VARCHAR2(25), age NUMBER);
/
CREATE TYPE hobbies_var AS VARRAY(10) OF VARCHAR2(25);
/
CREATE OR REPLACE PACKAGE teams 
   AUTHID CURRENT_USER AS
   PROCEDURE create_table (tab_name VARCHAR2);
   PROCEDURE insert_row (tab_name VARCHAR2, p person_typ, h hobbies_var);
   PROCEDURE print_table (tab_name VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY teams AS
   PROCEDURE create_table (tab_name VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||
                        ' (pers person_typ, hobbs hobbies_var)';
   END;
   PROCEDURE insert_row (
      tab_name VARCHAR2,
      p person_typ,
      h hobbies_var) IS
   BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||
         ' VALUES (:1, :2)' USING p, h;
   END;
   PROCEDURE print_table (tab_name VARCHAR2) IS
      TYPE  refcurtyp IS REF CURSOR;
      v_cur refcurtyp;
      p     person_typ;
      h     hobbies_var;
   BEGIN
      OPEN v_cur FOR 'SELECT pers, hobbs FROM ' || tab_name;
      LOOP
         FETCH v_cur INTO p, h;
         EXIT WHEN v_cur%NOTFOUND;
         -- print attributes of 'p' and elements of 'h'
         DBMS_OUTPUT.PUT_LINE('Name: ' || p.name || ' - Age: ' || p.age);
         FOR i IN h.FIRST..h.LAST 
         LOOP
           DBMS_OUTPUT.PUT_LINE('Hobby(' || i || '): ' || h(i));
         END LOOP;
      END LOOP;
      CLOSE v_cur;
   END;
END;
/

Example 12-19 Calling Procedures from the TEAMS Package

DECLARE
   team_name VARCHAR2(15);
BEGIN
   team_name := 'Notables';
   TEAMS.create_table(team_name);
   TEAMS.insert_row(team_name, person_typ('John', 31),
      hobbies_var('skiing', 'coin collecting', 'tennis'));
   TEAMS.insert_row(team_name, person_typ('Mary', 28),
      hobbies_var('golf', 'quilting', 'rock climbing', 'fencing'));
   TEAMS.print_table(team_name);
END;
/