Manipulating local collections - pl/sql, PL-SQL Programming

Manipulating Local Collections

Within PL/SQL, to manipulate the local collection, by using the  TABLE and CAST operators . The operands of CAST are a collection declared locally (in a PL/SQL unspecified block for illustration) and a SQL collection type. The CAST converts the local collection to the specified type. In that way, you can manipulate the collection as if it were a SQL database table. In the illustration below, you count the number of differences between a revised course list & the original (notice that the number of credits for the course 3720 changed from 4 to 3):


DECLARE
revised 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’,3),
Course(3760, ’Modern Drama’, 4),
Course(3822, ’The Short Story’, 4),
Course(3870, ’The American Novel’, 5),
Course(4210, ’20th-Century Poetry’, 4),
Course(4725, ’Advanced Workshop in Poetry’,5));
num_changed INTEGER;
BEGIN
SELECT COUNT(*) INTO num_changed
FROM TABLE(CAST(revised AS CourseList)) AS new,
TABLE(SELECT courses FROM department
WHERE name = ’English’) AS old
WHERE new.course_no = old.course_no AND
(new.title != old.title OR new.credits != old.credits);
DBMS_OUTPUT.PUT_LINE(num_changed);
END;

Posted Date: 10/3/2012 6:48:01 AM | Location : United States







Related Discussions:- Manipulating local collections - pl/sql, Assignment Help, Ask Question on Manipulating local collections - pl/sql, Get Answer, Expert's Help, Manipulating local collections - pl/sql Discussions

Write discussion on Manipulating local collections - pl/sql
Your posts are moderated
Related Questions
Architecture The PL/SQL run-time system and compilation is a technology, not an independent product. Consider this technology as an engine that compiles and executes the PL/SQL

Keys in SQL SQL support for keys in the following respects: SQL does not require at least one key for every base table. If no key is explicitly declared, then KEY {ALL B

Mixed Notation The fourth procedure call shows that you can mix the positional and named notation. In this situation, the first parameter uses the positional notation, & the s

Parameter and Keyword Description: label_name: This is an undeclared identifier which optionally labels a loop. When used, the label_name should be enclosed by double ang

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

Parameter SELF in pl/sql The MEMBER methods recognize a built-in parameter named SELF that is an instance of the object type. Whether declared explicitly or implicitly, it is

1- You can check attribute names from each table in DBF11 by running for example:  desc dbf11.Member;  desc dbf11.Agent;  desc dbf11.Producer; Because some attribute names in

Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D

Transaction Visibility As the figure shows, the changes made by an autonomous transaction become visible to another transaction whenever the autonomous transaction commits. Th

Variable Declaration - SQL SQL's support for variables is very similar to Tutorial D's, except that the syntax for creating persistent  variables-base tables-is quite differen