Oracle PL/SQL Programming, Third Edition

BULK COLLECT

Oracle8i introduced a very powerful new feature that improves the efficiency of queries in PL/SQL: the BULK COLLECT clause. With BULK COLLECT you can retrieve multiple rows of data through either an implicit or an explicit query with a single roundtrip to and from the database. BULK COLLECT reduces the number of context switches between the PL/SQL and SQL statement executors and thereby reduces the overhead of retrieving data.

Take a look at the following code snippet. I need to retrieve hundreds of rows of data on automobiles that have a poor environmental record. I place that data into a set of collections so that I can easily and quickly manipulate the data for both analysis and reporting.

DECLARE
   CURSOR major_polluters_cur
   IS
      SELECT name, mileage
        FROM transportation
       WHERE TYPE = 'AUTOMOBILE'
         AND mileage < 20;
   names name_varray;
   mileages number_varray;
BEGIN
   FOR bad_car IN major_polluters
   LOOP
      names.EXTEND;
      names (major_polluters%ROWCOUNT) := bad_car.NAME;
      mileages.EXTEND;
      mileages (major_polluters%ROWCOUNT) := bad_car.mileage;
   END LOOP;
   -- Now work with data in the collections
END;

This certainly gets the job done, but the job might take a long time to complete. Consider this: if the transportation table contains 2,000 vehicles, then the PL/SQL engine issues 2,000 individual fetches against the cursor in the System Global Area (SGA).

To help out in this scenario, use the BULK COLLECT clause for the INTO element of your query. By using this clause in your cursor ...