Anshuman Ghosh

Building on my previous post on BULK COLLECT usage for better performance in PLSQL, this post will talk on bulk DML operations and also on combining bulk DML with bulk collect operation.

While Bulk Collect is used for a bulk fetch of data from cursors, what if the requirement is to do DML on a data collection?

The answer is - use FORALL.

Without using FORALL

The direct way to do a DML after BULK COLLECT would be something like -

Example-

PROCEDURE process_all_rows
IS
TYPE employees_collection
IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
l_employees employees_collection;
BEGIN
....
.... build l_employees collection
....

FOR indx IN 1 .. l_employees.COUNT
LOOP
INSERT INTO selected_employees_table values l_employees(indx).emp_id;
END LOOP;
END process_all_rows;

Using FORALL

Using FORALL, a bulk DML can be run in one go, instead of multiple independent DML operations (like bulk collect, saving the associated overhead time consumed in context switching between SQL fetches and PLSQL program process).

Example-

PROCEDURE process_all_rows
IS
TYPE employees_collection
IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
l_employees employees_collection;
BEGIN
....
.... build l_employees collection
....

FORALL indx IN 1 .. l_employees.COUNT
INSERT INTO selected_employees_table values l_employees(indx).emp_id;

END process_all_rows;

Using FORALL with BULK COLLECT

The following example demonstrates using FORALL with BULKCOLLECT, wherein you wish to collect specific data involved in the DML.

Example-

PROCEDURE process_all_rows
IS
TYPE employees_collection IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;

TYPE employee_names IS TABLE OF VARCHAR2(300) INDEX BY PLS_INTEGER;

l_employees employees_collection;

l_selected_emp_names employee_names;
BEGIN
....
.... build l_employees collection
....

FORALL indx IN 1 .. l_employees.COUNT
UPDATE selected_employees_table
SET selected_flag = 'Y'
WHERE emp_id = l_employees(indx).emp_id
RETURNING emp_full_name BULK COLLECT INTO l_selected_emp_names;

END process_all_rows;

Add comment