Manipulating collections-nested table examples, PL-SQL Programming

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;

Posted Date: 10/3/2012 6:42:13 AM | Location : United States







Related Discussions:- Manipulating collections-nested table examples, Assignment Help, Ask Question on Manipulating collections-nested table examples, Get Answer, Expert's Help, Manipulating collections-nested table examples Discussions

Write discussion on Manipulating collections-nested table examples
Your posts are moderated
Related Questions
"Not Enforced" Table Constraints A constraint that is not enforced is not really a constraint within the meaning of the act, but SQL does have such a concept and it needs to b

Packages The package is a schema object which groups logically associated to the PL/SQL items, types, and subprograms. The Packages have 2 sections: the specification & the bod

Declaring a Cursor The Forward references are not allowed in the PL/SQL. Therefore, you must declare a cursor before referencing it in other statements. Whenever you declare a

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Example of EXCEPT Operator - SQL Example, like its counterpart in the theory book, illustrates the convenience of allowing any table expression to be the source for an INSERT

Assigning and Comparing Collections One collection can be assigned to other by an SELECT, INSERT, UPDATE, or FETCH statement, an assignment statement, or by a subprogram call. A

Table Comparison - SQL The following definitions for relation comparisons: Let r1 and r2 be relations having the same heading. Then: r1 ⊆ r2 is true if every tuple of r1

Keyword & Parameter Description: PRAGMA: These keywords signify that the statement is a pragma (i.e. compiler directive). The Pragmas are processed at the compile time, n

CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

Relational Operators The relational operators permit you to compare randomly complex expressions. The list below provides the meaning of each operator: