Manipulating collections-nested table examples, PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Manipulating collections-nested table examples

Iteration schemes- iterative control, Iteration Schemes The bounds of a ...

Iteration Schemes The bounds of a loop range can be variables, literals, variables, or expressions but must compute to integers. Below are some of the examples. As you can see t

Sqls counterpart of the key words, SQLs counterpart of the key words: ...

SQLs counterpart of the key words: The text from the opening parenthesis to the end of the fourth line specifies the declared type of the table, meaning that every table ever

Updating tables in sql, Updating Tables in SQL The topic of updating b...

Updating Tables in SQL The topic of updating by describing the assignment operator, ":=" in Tutorial D. SQL uses a different syntax for assignment, using the key word SET and

Maintain an audit trail of product table changes, The accuracy of product t...

The accuracy of product table data is critical and the Brwebean's. owner wants to have an audit file that contains information regarding all DML activity on the BB_PRODUCT table. T

Using bulk collect clause- bulk bind performance improvement, Using the BUL...

Using the BULK COLLECT Clause The keywords BULK COLLECT specify the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these ke

Create GUIs in PL/SQL, i NEED TO CREATE 3 guiS IN pl/sql sERVER PAGE FORM

i NEED TO CREATE 3 guiS IN pl/sql sERVER PAGE FORM

Object types and collections - performance of application, Use Object Types...

Use Object Types and Collections The Collection types and object types increase your efficiency by allowing for the realistic data modeling. The Complex real-world entities an

Join query, Using a join on 3 tables, select 5 columns and 10 rows from the...

Using a join on 3 tables, select 5 columns and 10 rows from the 3 tables without the use of a Cartesian product Query: SELECT E.LAST_NAME, E.FIRST_NAME, S.BUILDING, S.BRAN

Using %rowtype-declarations in sql, Using %ROWTYPE The %ROWTYPE attribut...

Using %ROWTYPE The %ROWTYPE attribute gives a record type which represents a row in a table (or view). The record can store the whole row of data selected from the table or fetc

Merge and truncate in sql, MERGE and TRUNCATE in SQL SQL has two more ...

MERGE and TRUNCATE in SQL SQL has two more table update operators, MERGE and TRUNCATE. MERGE, like INSERT, takes a source table s and uses it to update a target table t. Brief

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd