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):

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;
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);

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
Question 1 . Compare SQL and PL/SQL Question 2 . Write a database trigger to implement the following check condition                          Given the following table

Calling Constructors: The Calls to a constructor are allowed wherever the function calls are allowed. Similarly to the functions, a constructor is called as a section of an ex

Components of an Object Type: An object type encapsulates the operations and data. Therefore, you can declare the methods and attributes in an object type specification, but no

ROWNUM The ROWNUM returns a number representing the order in which a row was selected from the table. The first row selected has a ROWNUM of 1; the second row has a ROWNUM of

%ROWTYPE: This attribute gives a record type which represents a row in the database table or a row fetched from a formerly declared cursor. The Fields in the record and corresp

Project Description: I have two types of data sources. One that is a list in SharePoint and another that is an access desktop database. The access desktop database is fairly com

Extension and AND in SQL The theory book gives the following simple example of relational extension in Tutorial D: EXTEND IS_CALLED ADD ( FirstLetter ( Name ) AS Initial )

Explicit Cursors The set of rows returned by the query can include zero, one, or multiple rows, depending on how many rows meet your search criteria. Whenever a query returns

Cursor Variables Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opene

Pl/SQL Expressions The Expressions are constructed by using the operands and operators. An operand is a constant, literal, variable, or function call which contributes a value