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

Ask an Expert

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

Write your message here..

Related Questions

Triggers, At times, customers make mistakes in submitting their orders and ... 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

Disjunction - sql, Disjunction (OR, ∨) Again we have nine rows instead... Disjunction (OR, ∨) Again we have nine rows instead of just four and again, when unknown is not involved, the rows are as for 2VL. Also, when anything is paired with true, t

Union without corresponding - sql, UNION without CORRESPONDING - SQL T... 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

produce vertical output format- oracle, Create a Oracle procedure to produ... Create a Oracle procedure to produce vertical output format when selecting rows from a database table.

Exception handling, set serveroutput on declare a number(5); b n... set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_d

Keyword and parameter description - cursors, Keyword and Parameter Descript... Keyword and Parameter Description select_statement: This is a query which returns a result set of the rows. Its syntax is such that of select_ into_statement without the IN

Declare keyword description in pl sql, DECLARE : This keyword signals t... 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

Closest approximation to relational union - sql, Closest Approximation to R... Closest Approximation to Relational Union - SQL Actually, just as SQL has several varieties of JOIN, it also has several varieties of UNION, none of which is equivalent to th

Begin parameter description in pl sql, BEGIN Parameter Description in pl sq... BEGIN Parameter Description in pl sql: BEGIN: This keyword signals the beginning of the executable section of a PL/SQL block, that contains executable statements. The execut

Cursor variables as parameters, Cursor Variables As Parameters You can... Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR