Oracle PL/SQL BULK COLLECT: FORALL Example

What is BULK COLLECT?

BULK COLLECT reduces context switches between SQL and PL/SQL engine and allows SQL engine to fetch the records at once.

Oracle PL/SQL provides the functionality of fetching the records in bulk rather than fetching one-by-one. This BULK COLLECT can be used in 'SELECT' statement to populate the records in bulk or in fetching the cursor in bulk. Since the BULK COLLECT fetches the record in BULK, the INTO clause should always contain a collection type variable. The main advantage of using BULK COLLECT is it increases the performance by reducing the interaction between database and PL/SQL engine.

Syntax:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

In the above syntax, BULK COLLECT is used in collect the data from 'SELECT' and 'FETCH' statement.

FORALL Clause

The FORALL allows to perform the DML operations on data in bulk. It is similar to that of FOR loop statement except in FOR loop things happen at the record-level whereas in FORALL there is no LOOP concept. Instead the entire data present in the given range is processed at the same time.

Syntax:

FORALL <loop_variable>in<lower range> .. <higher range> 

<DML operations>;

In the above syntax, the given DML operation will be executed for the entire data that is present between lower and higher range.

LIMIT Clause

The bulk collect concept loads the entire data into the target collection variable as a bulk i.e. the whole data will be populated into the collection variable in a single-go. But this is not advisable when the total record that needs to be loaded is very large, because when PL/SQL tries to load the entire data it consumes more session memory. Hence, it is always good to limit the size of this bulk collect operation.

However, this size limit can be easily achieved by introducing the ROWNUM condition in the 'SELECT' statement, whereas in the case of cursor this is not possible.

To overcome this Oracle has provided 'LIMIT' clause that defines the number of records that needs to be included in the bulk.

Syntax:

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

In the above syntax, the cursor fetch statement uses BULK COLLECT statement along with the LIMIT clause.

BULK COLLECT Attributes

Similar to cursor attributes BULK COLLECT has %BULK_ROWCOUNT(n) that returns the number of rows affected in the nth DML statement of the FORALL statement, i.e. it will give the count of records affected in the FORALL statement for every single value from the collection variable. The term 'n' indicates the sequence of value in the collection, for which the row count is needed.

Example 1: In this example, we will project all the employee name from emp table using BULK COLLECT and we are also going to increase the salary of all the employees by 5000 using FORALL.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||c_emp_name);
END LOOP:
FORALL i IN lv_emp_name.FIRST .. lv emp_name.LAST
UPDATE emp SET salaiy=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT; 
Dbms_output.put_line(‘Salary Updated‘);
CLOSE guru99_det;
END;
/

Output

Employee Fetched:BBB
Employee Fetched:XXX 
Employee Fetched:YYY
Salary Updated

Code Explanation:

• Code line 2: Declaring the cursor guru99_det for statement 'SELECT emp_name FROM emp'.
• Code line 3: Declaring lv_emp_name_tbl as table type of VARCHAR2(50)
• Code line 4: Declaring lv_emp_name as lv_emp_name_tbl type.
• Code line 6: Opening the cursor.
• Code line 7: Fetching the cursor using BULK COLLECT with the LIMIT size as 5000 intl lv_emp_name variable.
• Code line 8-11: Setting up FOR loop to print all the record in the collection lv_emp_name.
• Code line 12: Using FORALL updating the salary of all the employee by 5000.
• Code line 14: Committing the transaction.

Next