10-4. Creating and Accessing Record Collections
Problem
You need to load records from a database table or view into a simple data structure that would benefit from use in a loop body or to pass as a parameter to another function or procedure. You want to act upon sets of records as a single unit.
Solution
Use a TYPE to define a TABLE based on the database table structure. The following example declares a cursor and then uses it to declare the table of records. The result is a variable named recs that holds the data fetched by the cursor.
DECLARE
CURSOR driver IS
SELECT *
FROM employees;
TYPE emp_type IS TABLE OF driver%ROWTYPE INDEX BY BINARY_INTEGER;
recs emp_type;
total number := 0.0;
BEGIN
OPEN DRIVER;
FETCH DRIVER BULK COLLECT INTO recs;
CLOSE DRIVER;
DBMS_OUTPUT.PUT_LINE (recs.COUNT || ' records found'),
FOR i in 1..recs.COUNT LOOP
total := total + recs(i).salary;
END LOOP;
END;
When you execute this block of code, you will see a message such as the following:
103 records found
How It Works
The TYPE statement defines a data structure using the attributes (columns) of the employees table as elements within the structure. The TABLE OF clause defines multiple instances of the record structure. The INDEX BY clause defines the index method, in this case an integer. Think of this structure as a spreadsheet with the rows being separate records from the database and the columns being the attributes (fields) in the database. The recipe works whether your cursor selects all the fields (SELECT *) or selects just a subset of fields from the table.
The BULK COLLECT portion of the fetch statement is more efficient than looping through the data in a standard cursor loop because PL/SQL switches control to the database just once to retrieve the data as opposed to switching to the database for each record retrieved in a cursor FOR .. LOOP. In a BULK COLLECT, all records meeting the query condition are retrieved and stored in the data structure in a single operation. Once the records are retrieved, processing may occur in a standard FOR .. standard FOR .. LOOP.