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
Description: Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that pr

Selecting Objects: Suppose that you have run the SQL*Plus script below that creates object type Person and object table persons, and that you have settled the table: CREATE

Tables within a Table - SQL Figure here is an exact copy of the one in the theory book and as before it is just an alternative way of representing some of the information con

Assignment of Variable - Updating a Variable Syntax: SET SN = SID ('S2'); This can obviously be read as "set the variable SN to be equal in value to SID ( 'S2' )".

Dynamic SQL: The Most PL/SQL programs do a predictable, specific job. For illustration, a stored procedure may accept an employee number and salary increase, and then update t

SELECT INTO Statement   The SELECT INTO statement retrieve data from one or more database tables, and then assigns the selected values to the variables or fields. Syntax:

Logical Connectives - SQL SQL's extended truth tables in which the symbol, for unknown, appears along with the usual T and F. Negation (NOT, ¬) Conjunction (

Cursors   To execute the multi-row query, the Oracle opens an unnamed work region which stores the processing information. The cursor names the work region, access the informa

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

UNION without CORRESPONDING - SQL The use of UNION without CORRESPONDING. Example is merely by omitting CORRESPONDING, but only because the operands have identical SELECT clau