Live ORACLE

DECLARE
wages         NUMBER;
hours_worked  NUMBER := 40;
hourly_salary  CONSTANT NUMBER := 17.50; -- constant value does not change
country       VARCHAR2(64) := 'UNKNOWN';
unknown       BOOLEAN;
TYPE comm_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
commissions   COMM_TAB;
TYPE jobs_var IS VARRAY(10) OF employees.job_id%TYPE;
jobids        JOBS_VAR;
CURSOR c1 IS
  SELECT department_id
  FROM   departments; -- cursor declaration
deptid        departments.department_id%TYPE;
emp_rec       employees%ROWTYPE; -- do not need TYPE declaration in this case

BEGIN
/* the following are examples of assignment statements */
wages := hours_worked
           * hourly_salary; -- compute wages
country := Upper('italy');
unknown := (country = 'UNKNOWN');
Commissions(5) := 20000
                    * 0.15;
Commissions(8) := 20000
                    * 0.18;
jobids := Jobs_var('ST_CLERK');
jobids.Extend(1);
Jobids(2) := 'SH_CLERK';
OPEN c1;
FETCH c1 INTO deptid;
CLOSE c1;
emp_rec.department_id := deptid;
emp_rec.job_id := Jobids(2);
END;
/  

Declaring and Assigning Values to Variables

--Cleanup ...........
DROP PACKAGE teams
/

DROP TYPE person_typ
/

DROP TYPE hobbies_var
/

-- Creating ..........
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;
/

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

TEAMS Package Using Dynamic SQL for Object Types and Collections

-- Cleanup before Creating
DROP TABLE depts CASCADE CONSTRAINTS PURGE
/

DROP TYPE dnames_var
/

-- Creating ........
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;
/ 

Using BULK COLLECT with Nested Tables

DROP TABLE depts CASCADE CONSTRAINTS PURGE
/

DROP TYPE dnames_var
/

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

Using INSERT, UPDATE, DELETE, and SELECT Statements With Varrays

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

Using INSERT, UPDATE, DELETE, and SELECT Statements With Nested Tables

DROP TABLE department CASCADE CONSTRAINTS PURGE
/

DROP TYPE CourseList
/

DROP TYPE Course
/

CREATE TYPE Course AS OBJECT
(course_no NUMBER,
title VARCHAR2(64),
credits NUMBER);
/

CREATE TYPE CourseList AS TABLE OF course;
/

CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab
/

INSERT INTO department VALUES ('English', 'June Johnson', '491C',
CourseList(
Course(1002, 'Expository Writing', 4),
Course(2020, 'Film and Literature', 4),
Course(4210, '20th-Century Poetry', 4),
Course(4725, 'Advanced Workshop in Poetry', 4)))
/

DECLARE
revised CourseList :=
CourseList(Course(1002, 'Expository Writing', 3),
Course(2020, 'Film and Literature', 4),
Course(4210, '20th-Century Poetry', 4),
Course(4725, 'Advanced Workshop in Poetry', 5));
num_changed INTEGER;
BEGIN
SELECT COUNT(*) INTO num_changed
FROM TABLE(CAST(revised AS CourseList)) new,
TABLE(SELECT courses FROM department
WHERE name = 'English') old
WHERE new.course_no = old.course_no AND
(new.title != old.title OR new.credits != old.credits);
DBMS_OUTPUT.PUT_LINE(num_changed);
END;
/

Performing Operations on PL/SQL Nested Tables With CAST