Oracle PL/SQL: BULK COLLECT - The SQL engine will bulk bind all the values present for a \column in a table before returning the c

Quick Search:

Oracle PL/SQL: BULK COLLECT

Jump to:

Category: >> Oracle PL/SQL >> BULK COLLECT

<< last

next >>

Snippet Name: BULK COLLECT

Description: The SQL engine will bulk bind all the values present for a \column in a table before returning the collection to the PL/SQL engine. If the amount of data being retrieved is immense, this fact itself, will result in degradation of performance. An alternative is to retrieve one set of records at a time for processing. This can be achieved by using an applicable WHERE condition or by using the ROWNUM pseudo-column. If using the FETCH INTO statement, then the LIMIT clause can also be used to limit the number of rows.

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 02nd, 2009

FETCH BULK COLLECT <cursor_name>  BULK COLLECT INTO <collection_name>
LIMIT <numeric_expression>;
 
--or
 
FETCH BULK COLLECT <cursor_name> BULK COLLECT INTO <array_name>
LIMIT <numeric_expression>;
SET timing ON
 
DECLARE
 CURSOR a_cur IS
 SELECT program_id
 FROM airplanes;
BEGIN
  FOR cur_rec IN a_cur LOOP
    NULL;
  END LOOP;
END;
/
 
DECLARE
 CURSOR a_cur IS
 SELECT program_id
 FROM airplanes;
 
 TYPE myarray IS TABLE OF a_cur%ROWTYPE;
 cur_array myarray;
BEGIN
  OPEN a_cur;
  LOOP
    FETCH a_cur BULK COLLECT INTO cur_array LIMIT 100;
    EXIT WHEN a_cur%NOTFOUND;
  END LOOP;
  CLOSE a_cur;
END;
/
 
DECLARE
 CURSOR a_cur IS
 SELECT program_id
 FROM airplanes;
 
 TYPE myarray IS TABLE OF a_cur%ROWTYPE;
 cur_array myarray;
BEGIN
  OPEN a_cur;
  LOOP
    FETCH a_cur BULK COLLECT INTO cur_array LIMIT 500;
    EXIT WHEN a_cur%NOTFOUND;
  END LOOP;
  CLOSE a_cur;
END;
/
 
DECLARE
 CURSOR a_cur IS
 SELECT program_id
 FROM airplanes;
 
 TYPE myarray IS TABLE OF a_cur%ROWTYPE;
 cur_array myarray;
BEGIN
  OPEN a_cur;
  LOOP
    FETCH a_cur BULK COLLECT INTO cur_array LIMIT 1000;
    EXIT WHEN a_cur%NOTFOUND;
  END LOOP;
  CLOSE a_cur;
END;
/