| Author: Nithya 10 Jul 2008 | Member Level: Gold | Rating:  Points: 6 |
Oracle introduced two new data manipulation language (DML) statements for PL/SQL in Oracle8i: BULK COLLECT and FORALL. Both statements implement a form of array processing inside PL/SQL; BULK COLLECT facilitates high-speed retrieval of data, and FORALL greatly improves performance of INSERT, UPDATE, and DELETE operations. Oracle Database achieves significant performance gains with these statements by greatly reducing the number of context switches between the PL/SQL and SQL statement execution engines.
With BULK COLLECT, you fetch multiple rows into one or more collections, rather than individual variables or records. The following example of BULK COLLECT retrieves all books containing "PL/SQL" in their titles into an associative array of records, all in a single pass to the database.
DECLARE
TYPE books_aat
IS TABLE OF book%ROWTYPE
INDEX BY PLS_INTEGER;
books books_aat;
BEGIN
SELECT *
BULK COLLECT INTO book
FROM books
WHERE title LIKE '%PL/SQL%';
...
END;
Similarly, FORALL transfers data from a PL/SQL collection to the specified table using collections. The following code example shows a procedure that accepts a nested table of book information and inserts the full contents of that collection (the binding array) into the book table. Note that this example also takes advantage of Oracle9i's FORALL enhancement that allows the INSERT of a record directly into a table.
BULK COLLECT and FORALL are very helpful, not only in improving performance but also in simplifying the code you need to write for SQL operations in PL/SQL. The following FORALL INSERT of multiple rows demonstrates rather clearly why PL/SQL is considered to be the best programming language for the Oracle database.
CREATE TYPE books_nt
IS TABLE OF book%ROWTYPE;
/
CREATE OR REPLACE PROCEDURE add_books (
books_in IN books_nt)
IS
BEGIN
FORALL book_index
IN books_in.FIRST .. books_in.LAST
INSERT INTO book
VALUES books_in(book_index);
...
END;
|