Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: BULK COLLECT Jump to:  
Category: >> Oracle PL/SQL >> BULK COLLECT Bookmark and Share

<< lastnext >>

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;
/
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 227 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?