At times when you are programming in PLSQL you would want to fetch multiple records into an ARRAY or some collection. One approach to do is to loop through the result of the SQL query and then keep adding each record to the ARRAY or Collection. PLSQL provides another construct to perform the same operation in a much more time efficient manner by using the BULK COLLECT INTO construct. In the first approach there has to be frequent switching between the PLSQL and the SQL engines which causes overhead but while using BULK COLLECT INTO that switching overhead is reduced considerably. To know more about this difference and to see the time variations in both the cases, I would recommend you to read this.
Using BULK COLLECT INTO construct:
Consider a STUDENT table with the following data:
I want to fetch all the Students age into a different array which I would do using BULK COLLECT INTO as shown below:
SET SERVEROUTPUT ON;
DECLARE
--Define a Collection Type
TYPE NumberArray IS TABLE OF STUDENT.AGE%TYPE;
--Declare a variable of the Type defined above.
--This holds the student age fetched.
StudentIds NumberArray;
BEGIN
/*
Select age from the table and bulk collect them in to the
variable of collection type.
*/
SELECT age BULK COLLECT INTO StudentIds FROM STUDENT;
/*
Looping through the collection just to confirm that the
data has really been fetched into it.
*/
for i in 1..StudentIds.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(StudentIds(i));
END LOOP;
END;
And the output would be:
Note: I have used the SQL Worksheet tool provided with JDeveloper to execute this example.