Using BULK COLLECT INTO construct in Oracle PLSQL

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:
Data1

I want to fetch all the Students age into a different array which I would do using BULK COLLECT INTO as shown below:

[sql]
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;
[/sql]

And the output would be:
BulkColelctOutput

Note: I have used the SQL Worksheet tool provided with JDeveloper to execute this example.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s