PL/SQL FORALL

The FORALL statement in PL/SQL is a powerful construct designed to improve the performance of bulk operations on collections. It is particularly useful when dealing with large sets of data. The FORALL statement allows you to perform a single DML (Data Manipulation Language) operation on an entire collection in a more efficient and streamlined manner compared to using individual DML statements in a loop.

Here’s a breakdown of the key aspects of the FORALL statement:

Syntax

FORALL index IN [LOWER BOUND]..[UPPER BOUND]
   [SAVE EXCEPTIONS]
   DML statement;

index: The loop variable used to iterate over the collection.

LOWER BOUND, UPPER BOUND: Specifies the range of elements in the collection to be processed. If not specified, the entire collection is processed.

SAVE EXCEPTIONS: An optional clause that allows the FORALL statement to continue processing even if some rows raise exceptions. The exceptions can be retrieved later using the SQL%BULK_EXCEPTIONS attribute.

Key Points

Performance Improvement: One of the primary advantages of the FORALL statement is its ability to reduce context switches between the PL/SQL and SQL engines. It processes multiple rows in a single SQL statement, resulting in improved performance.

Bulk Processing: The FORALL statement is designed for bulk processing, making it efficient for handling large volumes of data. It is commonly used with collections like arrays or nested tables.

Error Handling with SAVE EXCEPTIONS: The optional SAVE EXCEPTIONS clause allows the FORALL statement to continue processing even if some rows encounter errors. After the FORALL statement execution, you can inspect the SQL%BULK_EXCEPTIONS collection to identify the rows that caused exceptions.

Example

DECLARE
   TYPE emp_id_list IS TABLE OF employees.employee_id%TYPE;
   l_emp_ids emp_id_list := emp_id_list(101, 102, 103, 104);
BEGIN
      FORALL i IN 1..l_emp_ids.COUNT
         UPDATE employees
         SET salary = salary * 1.1
         WHERE employee_id = l_emp_ids(i);
END;

In this example, the FORALL statement is used to update the salaries of employees in bulk. It eliminates the need for a loop, resulting in more efficient processing.

In summary, the FORALL statement in PL/SQL provides a powerful mechanism for bulk processing of data, enhancing performance and reducing the overhead associated with context switching between PL/SQL and SQL engines. It is a valuable tool for improving the efficiency of operations involving large collections of data.