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
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 (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 The use of UNION without CORRESPONDING. Example is merely by omitting CORRESPONDING, but only because the operands have identical SELECT clau

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

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 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 : 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 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: This keyword signals the beginning of the executable section of a PL/SQL block, that contains executable statements. The execut

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