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
DECLARE : This keyword signals the beginning of the declarative section of the PL/SQL block, that contains local declarations. The Items declared locally exist only within the

Declarations in SQL Your program stores values in the variables and constants. As the program executes, the value of the variables can change, but the values constants cannot.

Role of Abstraction in pl/sql: The abstraction is a high-level description or model of a real-world entity. The Abstractions keep our daily lives convenient. They help us ca

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

Conditionals - SQL At first sight SQL does not appear to have a single operator for expressing logical implication. In this respect it would be in common with most programming

Use the NOCOPY Compiler Hint By default, the OUT and IN OUT parameters are passed by the value i.e. the value of an IN OUT actual parameter is copied into the corresponding fo

Parameter and Keyword Description: EXIT: An unconditional EXIT statement (i.e., one without a WHEN clause) exits the present loop instantly. The Execution resumes with th

Parameter and Keyword Description: dynamic_string: This is a string variable, literal, or expression which represents a SQL statement or the PL/SQL block. define_vari

Using the Collection Methods The collection methods below help to generalize the code and make collections easier to use and also make your applications easier to maintain:

Attributes: Just similar to variable, an attribute is declared with a name and datatype. The name should be exclusive within the object type. The datatype can be any Oracle ty