DROP TABLE dept_projects CASCADE CONSTRAINTS PURGE;
DROP type projectlist;

-- 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); 

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

Creating a Table with a Varray Column

DROP TABLE sophomores CASCADE CONSTRAINTS PURGE;
DROP TYPE student;
DROP TYPE CourseList;
-------------------------------------------
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
/

20:40:43 SQL> DROP TABLE sophomores CASCADE CONSTRAINTS PURGE;

Table dropped.

Elapsed: 00:00:00.04
20:40:44 SQL> DROP TYPE student;

Type dropped.

Elapsed: 00:00:00.09
20:40:44 SQL> DROP TYPE CourseList;

Type dropped.

Elapsed: 00:00:00.06

20:40:44 SQL> CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type
20:40:44   2  /

Type created.

Elapsed: 00:00:00.01
20:40:44 SQL> CREATE TYPE student AS OBJECT ( -- create object
20:40:44   2  id_num INTEGER(4),
20:40:44   3  name VARCHAR2(25),
20:40:44   4  address VARCHAR2(35),
20:40:44   5  status CHAR(2),
20:40:44   6  courses CourseList); -- declare nested table as attribute
20:40:44   7  /

Type created.

Elapsed: 00:00:00.06
20:40:44 SQL> CREATE TABLE sophomores of student
20:40:44   2  NESTED TABLE courses STORE AS courses_nt
20:40:44   3  /

Table created.

Elapsed: 00:00:00.03

Declaring a Nested Table in SQL

DECLARE
 emp       EMPLOYEE_TYP;
 emp_ref   REF EMPLOYEE_TYP;
 emp_name  VARCHAR2(50);
BEGIN
 SELECT Ref(e)
 INTO   emp_ref
 FROM   employee_tab e
 WHERE  e.employee_id = 370;
 -- the following assignment raises an error, not allowed in PL/SQL
 -- emp_name := emp_ref.first_name || ' ' || emp_ref.last_name;
 -- emp := DEREF(emp_ref); not allowed, cannot use DEREF in procedural statements
 SELECT Deref(emp_ref)
 INTO   emp
 FROM   dual; -- use dummy table DUAL
 emp_name := emp.first_name
             ||' '
             ||emp.last_name;
 dbms_output.Put_line(emp_name);
END;
/  

Using DEREF in a SELECT INTO Statement

DECLARE
 emp      EMPLOYEE_TYP;
 emp_ref  REF EMPLOYEE_TYP;
BEGIN
 SELECT Ref(e)
 INTO   emp_ref
 FROM   employee_tab e
 WHERE  e.employee_id = 370;
 UPDATE employee_tab e
 SET    e.address = Address_typ('8701 College','Oakland','CA','94321')
 WHERE  Ref(e) = emp_ref;
END;
/  

Updating Rows in an Object Table With a REF Modifier

DECLARE
 emp  EMPLOYEE_TYP;
BEGIN
 INSERT INTO employee_tab
 VALUES     (Employee_typ(370,'Robert','Myers','RMYERS','555.111.2277',
                          '07-NOV-04','SA_REP',8800,.12,101,110,Address_typ('540 Fillmore','San Francisco','CA','94011')));
 UPDATE employee_tab e
 SET    e.address.street = '1040 California'
 WHERE  e.employee_id = 370;
 DELETE FROM employee_tab e
 WHERE       e.employee_id = 310;
END;
/  

Updating and Deleting Rows in an Object Table

DECLARE
 emp  EMPLOYEE_TYP;
BEGIN
 INSERT INTO employee_tab
 VALUES     (Employee_typ(310,'Evers','Boston','EBOSTON','555.111.2222',
                          '01-AUG-04','SA_REP',9000,.15,101,110,Address_typ('123 Main','San Francisco','CA','94111')));
 INSERT INTO employee_tab
 VALUES     (Employee_typ(320,'Martha','Dunn','MDUNN','555.111.3333',
                          '30-SEP-04','AC_MGR',12500,0,101,110,Address_typ('123 Broadway','Redwood City','CA','94065')));
END;
/  

Inserting Rows in an Object Table

DECLARE
 emp  EMPLOYEE_TYP;
BEGIN
 emp := Employee_typ(315,'Francis','Logan','FLOGAN','555.777.2222',
                     '01-MAY-04','SA_MAN',11000,.15,101,110,Address_typ('376 Mission','San Francisco','CA','94222'));
 dbms_output.Put_line(emp.first_name
                      ||' '
                      ||emp.last_name);
 dbms_output.Put_line(emp.address.street);
 dbms_output.Put_line(emp.address.city
                      ||', '
                      ||emp.address.state
                      ||' '
                      ||emp.address.postal_code);
END;
/  

Francis Logan
376 Mission
San Francisco, CA 94222

PL/SQL procedure successfully completed.

Accessing Object Attributes

DECLARE
 emp  EMPLOYEE_TYP; -- emp is atomically null

BEGIN
 IF emp IS NULL THEN
   dbms_output.Put_line('emp is NULL #1');
 END IF;
 IF emp.employee_id IS NULL THEN
   dbms_output.Put_line('emp.employee_id is NULL #1');
 END IF;
 emp.employee_id := 330;
 IF emp IS NULL THEN
   dbms_output.Put_line('emp is NULL #2');
 END IF;
 IF emp.employee_id IS NULL THEN
   dbms_output.Put_line('emp.employee_id is NULL #2');
 END IF;
 emp := Employee_typ(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
                     NULL,NULL,Address_typ(NULL,NULL,NULL,NULL));
 -- emp := NULL; -- this would have made the following IF statement TRUE
 IF emp IS NULL THEN
   dbms_output.Put_line('emp is NULL #3');
 END IF;
 IF emp.employee_id IS NULL THEN
   dbms_output.Put_line('emp.employee_id is NULL #3');
 END IF;
EXCEPTION
 WHEN access_into_null THEN
   dbms_output.Put_line('Cannot assign value to NULL object');
END;
/  

emp is NULL #1
emp.employee_id is NULL #1
emp is NULL #2
emp.employee_id is NULL #3

PL/SQL procedure successfully completed.

Null Objects in a PL/SQL Block

DECLARE

 emp  EMPLOYEE_TYP; -- emp is atomically null



BEGIN

 -- call the constructor for employee_typ

 emp := Employee_typ(315,'Francis','Logan','FLOGAN','555.777.2222',

                     '01-MAY-04','SA_MAN',11000,.15,101,110,Address_typ('376 Mission','San Francisco','CA','94222'));

 dbms_output.Put_line(emp.first_name

                      ||' '

                      ||emp.last_name); -- display details

 emp.Display_address(); -- call object method to display details



END;

/

Francis Logan
Francis Logan
376 Mission
San Francisco, CA 94222

PL/SQL procedure successfully completed.

Declaring Object Types in a PL/SQL Block

DROP TYPE address_typ;

CREATE TYPE address_typ AS OBJECT
 (street VARCHAR2(30),
  city VARCHAR2(20),
  state CHAR(2),
  postal_code VARCHAR2(6)
 );
/

DROP TYPE employee_typ;

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;

Working With Object Types

DROP TABLE gradereport CASCADE CONSTRAINTS PURGE;

CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30),
weight NUMBER, grade NUMBER);

INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4);
INSERT INTO gradereport VALUES('Mark','Chemistry', 4, 3);
INSERT INTO gradereport VALUES('Mark','Maths', 3, 3);
INSERT INTO gradereport VALUES('Mark','Economics', 3, 4);

DROP PACKAGE pkg_gpa;

CREATE PACKAGE pkg_gpa IS
TYPE gpa IS TABLE OF NUMBER;
FUNCTION weighted_average(input_values SYS_REFCURSOR)
RETURN gpa PIPELINED;
END pkg_gpa;
/

CREATE PACKAGE BODY pkg_gpa IS
FUNCTION weighted_average(input_values SYS_REFCURSOR)
RETURN gpa PIPELINED IS
grade NUMBER;
total NUMBER := 0;
total_weight NUMBER := 0;
weight NUMBER := 0;
BEGIN
-- The function accepts a ref cursor and loops through all the input rows
LOOP
FETCH input_values INTO weight, grade;
EXIT WHEN input_values%NOTFOUND;
-- Accumulate the weighted average
total_weight := total_weight + weight;
total := total + grade*weight;
END LOOP;
PIPE ROW (total / total_weight);
RETURN; -- the function returns a single result
END;
END pkg_gpa;
/

-- the query result comes back as a nested table with a single row
-- COLUMN_VALUE is a keyword that returns the contents of a nested table
SELECT w.column_value "weighted result" FROM TABLE(
pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;

weighted result
---------------
            3.5

Using a Pipelined Table Function as an Aggregate Function

DROP PACKAGE refcur_pkg;

-- Define the ref cursor types
CREATE PACKAGE refcur_pkg IS
TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;
TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30));
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)
RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE PACKAGE BODY refcur_pkg IS
FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)
RETURN outrecset PIPELINED IS
out_rec outrec_typ;
in_rec1 p1%ROWTYPE;
in_rec2 p2%ROWTYPE;
BEGIN
LOOP
FETCH p2 INTO in_rec2;
EXIT WHEN p2%NOTFOUND;
END LOOP;
CLOSE p2;
LOOP
FETCH p1 INTO in_rec1;
EXIT WHEN p1%NOTFOUND;
-- first row
out_rec.var_num := in_rec1.employee_id;
out_rec.var_char1 := in_rec1.first_name;
out_rec.var_char2 := in_rec1.last_name;
PIPE ROW(out_rec);
-- second row
out_rec.var_num := in_rec2.department_id;
out_rec.var_char1 := in_rec2.department_name;
out_rec.var_char2 := TO_CHAR(in_rec2.location_id);
PIPE ROW(out_rec);
END LOOP;
CLOSE p1;
RETURN;
END;
END refcur_pkg;
/

-- SELECT query using the g_trans table function
SELECT * FROM TABLE(refcur_pkg.g_trans(
CURSOR(SELECT * FROM employees WHERE department_id = 60),
CURSOR(SELECT * FROM departments WHERE department_id = 60)));

Using Multiple REF CURSOR Input Variables

DROP PACKAGE refcur_pkg;

-- Define the ref cursor types and function
CREATE OR REPLACE PACKAGE refcur_pkg IS
TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30));
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION f_trans(p refcur_t)
RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS
FUNCTION f_trans(p refcur_t)
RETURN outrecset PIPELINED IS
out_rec outrec_typ;
in_rec p%ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;
-- first row
out_rec.var_num := in_rec.employee_id;
out_rec.var_char1 := in_rec.first_name;
out_rec.var_char2 := in_rec.last_name;
PIPE ROW(out_rec);
-- second row
out_rec.var_char1 := in_rec.email;
out_rec.var_char2 := in_rec.phone_number;
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN;
END;
END refcur_pkg;
/

-- SELECT query using the f_transc table function
SELECT * FROM TABLE(
refcur_pkg.f_trans(CURSOR(SELECT * FROM employees WHERE department_id = 60)));

OUTPUT:-

Using a Pipelined Table Function For a Transformation

CREATE PACKAGE pkg1 AS
TYPE numset_t IS TABLE OF NUMBER;
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/

CREATE PACKAGE BODY pkg1 AS
-- FUNCTION f1 returns a collection of elements (1,2,3,... x)
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
BEGIN
FOR i IN 1..x LOOP
PIPE ROW(i);
END LOOP;
RETURN;
END;
END pkg1;
/

-- pipelined function is used in FROM clause of SELECT statement
SELECT * FROM TABLE(pkg1.f1(5));

Assigning the Result of a Table Function

SELECT   TYPE,
       plsql_code_type,
       Count(* )
FROM     dba_plsql_object_settings
WHERE    plsql_code_type IS NOT NULL
GROUP BY TYPE,
       plsql_code_type
ORDER BY TYPE,
       plsql_code_type; 

Checking for PLSQL Compilation Type

DECLARE
 TYPE emptabtyp IS TABLE OF employees%ROWTYPE;
 emp_tab  EMPTABTYP := Emptabtyp(NULL); -- initialize
 t1       NUMBER;
 t2       NUMBER;
 t3       NUMBER;
 PROCEDURE Get_time
      (t  OUT NUMBER)
 IS
 BEGIN
   t := dbms_utility.get_time;
 END;
 PROCEDURE Do_nothing1
      (tab  IN OUT EMPTABTYP)
 IS
 BEGIN
   NULL;
 END;
 PROCEDURE Do_nothing2
      (tab  IN OUT NOCOPY EMPTABTYP)
 IS
 BEGIN
   NULL;
 END;
BEGIN
 SELECT *
 INTO   Emp_tab(1)
 FROM   employees
 WHERE  employee_id = 100;
 emp_tab.Extend(49999,1); -- copy element 1 into 2..50000
 Get_time(t1);
 Do_nothing1(emp_tab); -- pass IN OUT parameter
 Get_time(t2);
 Do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter
 Get_time(t3);
 dbms_output.Put_line('Call Duration (secs)');
 dbms_output.Put_line('--------------------');
 dbms_output.Put_line('Just IN OUT: '
                      ||To_char((t2
                                   - t1)
                                  / 100.0));
 dbms_output.Put_line('With NOCOPY: '
                      ||To_char((t3
                                   - t2))
                          / 100.0);
END;
/  

Call Duration (secs)
--------------------
Just IN OUT: .16
With NOCOPY: 0

PL/SQL procedure successfully completed.

Using NOCOPY With Parameters