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
THEORY OF BIOGENESIS - This theory explains that the existing living organisms originated from pre-existing living beings not from non living entities. This concept of or

CLOSE Statement The CLOSE statement allows the resources held by a cursor variable or open cursor to be reused. No more rows can be fetched from the cursor variable or closed

IN OUT Mode An IN OUT parameter passes initial values to the subprogram being called and return efficient values to the caller. Within the subprogram, an IN OUT parameter acts

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

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i

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

Nested Tables versus Index-by Tables The Index-by tables and nested tables are just similar. For e.g.  They have similar structure and their individual elements are accessed in

Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff

Perform the following queries on the Hospital1.DB using SQL Anywhere (START EARLY!). a. Which patients have purchased the drug "Tylenol"? List the names and addresses. Arrange the

Using a join on 2 tables, select all columns and 10 rows from the 2 tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 E JOIN STAFF S ON E.EMP_