FORALL Statement

The FORALL statement issues a series of INSERT, UPDATE, or DELETE statements, usually much faster than an equivalent FOR loop. It requires some setup code, because each iteration of the loop must use values from one or more collections in its VALUES or WHERE clauses. For more information, see "Reducing Loop Overhead for DML Statements and Queries (FORALL, BULK COLLECT)".

Syntax

Description of forall_statement.gif follows

Description of the illustration forall_statement.gif

bounds_clause
Description of bounds_clause.gif follows

Description of the illustration bounds_clause.gif

Keyword and Parameter Description

INDICES OF collection_name

A clause specifying that the values of the index variable correspond to the subscripts of the elements of the specified collection. With this clause, you can use FORALL with nested tables where some elements have been deleted, or with associative arrays that have numeric subscripts.

BETWEEN lower_bound AND upper_bound

Limits the range of subscripts in the INDICES OF clause. If a subscript in the range does not exist in the collection, that subscript is skipped.

VALUES OF index_collection_name

A clause specifying that the subscripts for the FORALL index variable are taken from the values of the elements in another collection, specified by index_collection_name. This other collection acts as a set of pointers; FORALL can iterate through subscripts in arbitrary order, even using the same subscript more than once, depending on what elements you include in index_collection_name.

The index collection must be a nested table, or an associative array indexed by PLS_INTEGER or BINARY_INTEGER, whose elements are also PLS_INTEGER or BINARY_INTEGER. If the index collection is empty, an exception is raised and the FORALL statement is not executed.

index_name

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. You cannot refer to another variable with the same name inside the statement. Inside a FORALL statement, index_name cannot appear in expressions and cannot be assigned a value.

lower_bound .. upper_bound

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

SAVE EXCEPTIONS

Optional keywords that cause the FORALL loop to continue even if some DML operations fail. Instead of raising an exception immediately, the program raises a single exception after the FORALL statement finishes. 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

An INSERT, UPDATE, or DELETE statement that references collection elements in the VALUES or WHERE clauses.

Usage Notes

Although the SQL statement can reference more than one collection, 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.
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.
When you specify an explicit range, all collection elements in that range must exist. If an element is missing or was deleted, you get an error.
When you use the INDICES OF or VALUES OF clauses, all the collections referenced in the DML statement must have subscripts matching the values of the index variable. Make sure that any DELETE, EXTEND, and so on operations are applied to all the collections so that they have the same set of subscripts. If any of the collections is missing a referenced element, you get an error. If you use the SAVE EXCEPTIONS clause, this error is treated like any other error and does not stop the FORALL statement.
You cannot refer to individual record fields within DML statements called by a FORALL statement. Instead, you can specify the entire record with the SET ROW clause in an UPDATE statement, or the VALUES clause in an INSERT statement.
Collection subscripts must be just the index variable rather than an expression, such as i rather than i+1.
The cursor attribute %BULK_ROWCOUNT cannot be assigned to other collections, or be passed as a parameter to subprograms.

Example

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;

Bulk binds apply only to subscripted collections. In the following example, the collection sals, which is passed to the function median, is not bulk-bound:

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