In this article I will explain the method I use when I need to process an indeterminate number of rows using PL/SQL.
I became aware of this method after reading Bryn Llewellyn’s white paper Doing SQL From PL/SQL: Best and Worst Practises (My example is based on the code shown on page 31 of the white paper)
The code is shown below and I will explain how it works:
DECLARE CURSOR employees_cur IS SELECT e.employee_id, e.first_name, e.last_name, e.salary FROM employees e; TYPE emp_type IS TABLE OF employees_cur%ROWTYPE INDEX BY PLS_INTEGER; laa_emps emp_type; lkn_batchsize CONSTANT NUMBER(3) := 100; BEGIN OPEN employees_cur; LOOP FETCH employees_cur BULK COLLECT INTO laa_emps LIMIT lkn_batchsize; FOR i IN 1 .. laa_emps.COUNT() LOOP -- do something interesting with each employee record null; END LOOP; EXIT WHEN laa_emps.COUNT() < lkn_batchsize; END LOOP; CLOSE employees_cur; END;
First of all I define an explicit cursor to select the records that I want to work with. (lines 3 – 8). The employee table is a copy of the standard Oracle HR.EMPLOYEE table which contains 107 records.
I then create a PL/SQL Associative Array based on the explicit cursor defined at (lines 3 – 8) and declare a variable of this type. (lines 10 – 13)
The next line declares a constant that controls the batch size or the number of records that each fetch will return. (line 15)
The cursor is opened. (line 19)
A basic loop is entered. (line 21) I will refer to this loop as the outer loop
Using the bulk collect construct, the first batch of 100 records are fetched from the cursor into the associative array variable. (line 23).
The for loop is entered (line 25) . It is within this loop that the processing on each record is performed (line 28). This loop is executed 100 times.
Once the batch of records have been processed, the next to be executed is line 33 where an evaluation is made whether to exit the outer loop. If the count of records that has just been fetched is less than the batch size then there are no further records to process. In this first iteration, the number of records retrieved was 100 and the batch size is 100 so this evaluates to false so processing returns to line 23.
This time the bulk collect fetches the remaining 7 records into the associative array variable. (line 23)
The for loop is again entered and the loop is executed 7 times. (line 28)
With the for loop complete the evaluation at (line 33) is again executed and this time evaluates to TRUE. The count of records is 7 which is less than the batch size of 100 which means all the records have been processed. The outer loop is then exited.
Finally the cursor is closed and the end of the program is reached. (line 37)