PL/SQL Language Elements, 22 of 52

FORALL Statement

The FORALL statement instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. For more information, see "Reducing Loop Overhead for Collections with Bulk Binds".

Syntax

Text description of forall_statement.gif follows

Text description of the illustration forall_statement.gif

Keyword and Parameter Description

index_name

This is an undeclared identifier that can be referenced only within the FORALL statement and only as a collection subscript.

The implicit declaration of index_name overrides any other declaration outside the loop. So, another variable with the same name cannot be referenced inside the statement. Inside a FORALL statement, index_name cannot appear in expressions and cannot be assigned a value.

lower_bound .. upper_bound

These are expressions that must yield number, which, if necessary, PL/SQL rounds to the nearest integer. The integers must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range. The expressions are evaluated only when the FORALL statement is first entered.

SAVE EXCEPTIONS

These optional keywords cause the FORALL loop to continue even if some DML operations fail. The details of the errors are available after the loop in SQL%BULK_EXCEPTIONS. The program can report or clean up all the errors after the FORALL loop, rather than handling each exception as it happens.

sql_statement

This must be an INSERT, UPDATE, or DELETE statement that references collection elements.

Usage Notes

The SQL statement can reference more than one collection. However, the performance benefits apply only to subscripted collections.

If a FORALL statement fails, database changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous iterations of the FORALL loop are not rolled back.

Restrictions

The following restrictions apply to the FORALL statement:

You cannot loop through the elements of an associative array that has a string type for the key.
Within a FORALL loop, you cannot refer to the same collection in both the SET clause and the WHERE clause of an UPDATE statement. You might need to make a second copy of the collection and refer to the new name in the WHERE clause.
You can use the FORALL statement only in server-side programs (not in client-side programs). Otherwise, you get the error this feature is not supported in client-side programs.
The INSERT, UPDATE, or DELETE statement must reference at least one collection. For example, a FORALL statement that inserts a set of constant values in a loop raises an exception.

All collection elements in the specified range must exist. If an element is missing or was deleted, you get an error. An example follows:

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10, 20, 30, 40);
BEGIN
   depts.DELETE(3);  -- delete third element
   FORALL i IN depts.FIRST..depts.LAST
      DELETE FROM emp WHERE deptno = depts(i);  -- causes an error
END;

As the following example shows, input collections of composite values cannot be decomposed and bound to database columns:

CREATE TABLE coords (x NUMBER, y NUMBER);
CREATE TYPE Pair AS OBJECT (m NUMBER, n NUMBER);

DECLARE
   TYPE PairTab IS TABLE OF Pair;
   pairs PairTab := PairTab(Pair(1,2), Pair(3,4), Pair(5,6));
   TYPE NumTab IS TABLE OF NUMBER;
   nums NumTab := NumTab(1, 2, 3);
BEGIN
   /* The following statement fails. */
   FORALL i IN 1..3
      UPDATE coords SET (x, y) = pairs(i)
         WHERE x = nums(i);
END;

The workaround is to decompose the composite values manually:

DECLARE
   TYPE PairTab IS TABLE OF Pair;
   pairs PairTab := PairTab(Pair(1,2), Pair(3,4), Pair(5,6));
   TYPE NumTab IS TABLE OF NUMBER;
   nums NumTab := NumTab(1, 2, 3);
BEGIN
   /* The following statement succeeds. */
   FORALL i in 1..3
      UPDATE coords SET (x, y) = (pairs(i).m, pairs(i).n)
         WHERE x = nums(i);
END;

Collection subscripts cannot be expressions, as the following example shows:

FORALL j IN mgrs.FIRST..mgrs.LAST
   DELETE FROM emp WHERE mgr = mgrs(j+1);  -- invalid subscript

The cursor attribute %BULK_ROWCOUNT cannot be assigned to other collections. Also, it cannot be passed as a parameter to subprograms.

Example

The following example shows that you can use the lower and upper bounds to bulk-bind arbitrary slices of a collection:

DECLARE
   TYPE NumList IS VARRAY(15) OF NUMBER;
   depts NumList := NumList();
BEGIN
   -- fill varray here
   ...
   FORALL j IN 6..10  -- bulk-bind middle third of varray
      UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(j);
END;

Remember, the PL/SQL engine bulk-binds only subscripted collections. So, in the following example, it does not bulk-bind the collection sals, which is passed to the function median:

FORALL i IN 1..20
   INSERT INTO emp2 VALUES (enums(i), names(i), median(sals), ...);