Manipulating Collections

Within PL/SQL, the collections add procedural power and flexibility. The biggest benefit is that your program can compute subscripts to process the specific elements. A bigger benefit is that the program can use the SQL to manipulate the in-memory collections.

Some Nested Table Examples

In the SQL Plus, assume that you define object type Course, as shown:

SQL> CREATE TYPE Course AS OBJECT (
2 course_no NUMBER(4),
3 title VARCHAR2(35),
4 credits NUMBER(1));


Later, you define the TABLE type CourseList that stores the Course objects:

SQL> CREATE TYPE CourseList AS TABLE OF Course;

At last, you create a database table department that has a column of type CourseList, as shown:

SQL> CREATE TABLE department (
2 name VARCHAR2(20),
3 director VARCHAR2(20),
4 office VARCHAR2(20),
5 courses CourseList)
6 NESTED TABLE courses STORE AS courses_tab;


Each item in the column courses is a nested table which will store the courses offered by a given department. The NESTED TABLE clause is needed as the department has a nested table column. The clause identify the nested table and names a system-generated store table, in which the Oracle stores data out-of-line (in other tablespace).

Now, you can populate the database table department. In the example below, notice that how the table constructor CourseList() gives values for column courses:

BEGIN
INSERT INTO department
VALUES(’Psychology’, ’Irene Friedman’, ’Fulton Hall 133’,
CourseList(Course(1000, ’General Psychology’, 5),
Course(2100, ’Experimental Psychology’, 4),
Course(2200, ’Psychological Tests’, 3),
Course(2250, ’Behavior Modification’, 4),
Course(3540, ’Groups and Organizations’, 3),
Course(3552, ’Human Factors in Busines’, 4),
Course(4210, ’Theories of Learning’, 4),
Course(4320, ’Cognitive Processes’, 4),
Course(4410, ’Abnormal Psychology’, 4)));
INSERT INTO department
VALUES(’History’, ’John Whalen’, ’Applegate Hall 142’,
CourseList(Course(1011, ’History of Europe I’, 4),
Course(1012, ’History of Europe II’, 4),
Course(1202, ’American History’, 5),
Course(2130, ’The Renaissance’, 3),
Course(2132, ’The Reformation’, 3),
Course(3105, ’History of Ancient Greece’, 4),
Course(3321, ’Early Japan’, 4),
Course(3601, ’Latin America Since 1825’, 4),
Course(3702, ’Medieval Islamic History’, 4)));

INSERT INTO department
VALUES(’English’, ’Lynn Saunders’, ’Breakstone Hall 205’,
CourseList(Course(1002, ’Expository Writing’, 3),
Course(2020, ’Film and Literature’, 4),
Course(2418, ’Modern Science Fiction’, 3),
Course(2810, ’Discursive Writing’, 4),
Course(3010, ’Modern English Grammar’, 3),
Course(3720, ’Introduction to Shakespeare’, 4),
Course(3760, ’Modern Drama’, 4),
Course(3822, ’The Short Story’, 4),
Course(3870, ’The American Novel’, 5)));
END;


In the example below, you modify the list of courses offered by the English Department:


DECLARE
new_courses CourseList :=
CourseList(Course(1002, ’Expository Writing’, 3),
Course(2020, ’Film and Literature’, 4),
Course(2810, ’Discursive Writing’, 4),
Course(3010, ’Modern English Grammar’, 3),
Course(3550, ’Realism and Naturalism’, 4),
Course(3720, ’Introduction to Shakespeare’, 4),
Course(3760, ’Modern Drama’, 4),
Course(3822, ’The Short Story’, 4),
Course(3870, ’The American Novel’, 4),
Course(4210, ’20th-Century Poetry’, 4),
Course(4725, ’Advanced Workshop in Poetry’, 5));
BEGIN
UPDATE department
SET courses = new_courses WHERE name = ’English’;
END;


In the next illustration, you recover all the courses offered by the Psychology Department into a local nested table:


DECLARE
psyc_courses CourseList;
BEGIN
SELECT courses INTO psyc_courses FROM department
WHERE name = ’Psychology’;
...
END;