FORALL Statement

The FORALL statement runs one DML statement multiple times, with different values in the VALUES and WHERE clauses. The different values come from existing, populated collections. The FORALL statement is usually much faster than an equivalent FOR loop.

Topics:

Syntax
Semantics
Usage
Examples
Related Topics

Syntax

forall_statement ::=

Description of the illustration forall_statement.gif

bounds_clause ::=

Description of the illustration bounds_clause.gif

Semantics

bounds_clause

Specifies the collection element subscripts that provide values for the variable index_name. For each value, the SQL engine runs dml_statement once.

dml_statement

A static or dynamic INSERT, UPDATE, or DELETE statement that references at least one collection in its VALUES or WHERE clause. Performance benefits apply only to collection references that use index_name as a subscript.

Every collection that dml_statement references must have subscripts that match the values of index_name. If you apply the DELETE, EXTEND, or TRIM method to one collection, apply it to the other collections also, so that all collections have the same set of subscripts. If any collection lacks a referenced element, an exception is raised.

Restrictions on dml_statement

If dml_statement is an UPDATE statement, its SET and WHERE clauses cannot reference the same collection.

The workaround is to make a copy of the collection, and reference the original collection in the SET clause and the copy in the WHERE clause.
If dml_statement is a dynamic SQL statement, then values in the USING clause (bind arguments for the dynamic SQL statement) must be simple references to the collection, not expressions. For example, collection(i) is valid, but UPPER(collection(i) is invalid.

index_name

An identifier for the implicitly declared integer variable that is local to the FORALL statement. Statements outside the FORALL statement cannot reference index_name. Statements inside the FORALL statement can reference index_name as an index variable, but cannot use it in expressions or change its value. After the FORALL statement runs, index_name is undefined.

INDICES OF collection [ BETWEEN lower_bound AND upper_bound ]

Specifies that the values of index_name correspond to the subscripts of the elements of the specified collection. The subscripts need not be consecutive.

Both lower_bound and upper_bound are numeric expressions that PL/SQL evaluates once, when the FORALL statement is entered, and rounds to the nearest integer if necessary. The resulting integers are the lower and upper bounds of a valid range of index numbers, which need not be consecutive.

Restriction on collection If collection is an associative array, it must be indexed by integer.

lower_bound .. upper_bound

Both lower_bound and upper_bound are numeric expressions that PL/SQL evaluates once, when the FORALL statement is entered, and rounds to the nearest integer if necessary. The resulting integers must be the lower and upper bounds of a valid range of consecutive index numbers. If an element in the range is missing or was deleted, an exception is raised.

SAVE EXCEPTIONS

This option enables the FORALL loop to continue even if some of its DML statements fail. Instead of raising an exception immediately, the program raises a single exception after the FORALL statement finishes. For information about the exceptions, use the implicit cursor attribute SQL%BULK_EXCEPTIONS.

VALUES OF index_collection

Specifies that the values of index_name are the elements of index_collection, a collection of PLS_INTEGER elements that is indexed by PLS_INTEGER. The subscripts of index_collection need not be consecutive. If index_collection is empty, an exception is raised and the FORALL statement does not run.

Usage

You can use the FORALL statement only in server-side programs, not in client-side programs.

Examples