Bulk Collect:
Bulk Processing in PL/SQL
The bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine.
Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch.
Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values. The UPDATE statement in the increase_salary procedure fits this scenario; the only thing that changes with each new execution of the statement is the employee ID.
In this article, I will cover the two most important of these features: BULK COLLECT and FORALL.
- BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
- FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly.
PROCEDURE process_all_rows
IS[-
TYPE employees_aat
IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
l_employees employees_aat;
BEGIN
SELECT *
BULK COLLECT INTO l_employees
FROM employees;
FOR indx IN 1 .. l_employees.COUNT
LOOP
analyze_compensation
(l_employees(indx));
END LOOP;
END process_all_rows;
