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 or host arrays. The FORALL statement is usually much faster than an equivalent FOR LOOP statement.

Topics

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

forall_statement ::=

bounds_clause ::=

Semantics

forall_statement

index

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

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 as an index.

Every collection that dml_statement references must have indexes that match the values of index. 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 indexes. If any collection lacks a referenced element, PL/SQL raises an exception.

Restriction on dml_statement If dml_statement is a dynamic SQL statement, then values in the USING clause (bind variables 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.

SAVE EXCEPTIONS

Enables the FORALL statement to continue even if some of its DML statements fail. For more information, see "Handling FORALL Exceptions After FORALL Statement Completes".

bounds_clause

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

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, PL/SQL raises an exception.

INDICES OF collection [ BETWEEN lower_bound AND upper_bound ]

Specifies that the values of index correspond to the indexes of the elements of collection. The indexes 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 PLS_INTEGER.

VALUES OF index_collection

Specifies that the values of index are the elements of index_collection, a collection of PLS_INTEGER elements that is indexed by PLS_INTEGER. The indexes of index_collection need not be consecutive. If index_collection is empty, PL/SQL raises an exception and the FORALL statement does not run.

Examples

• Example 12-8, "DELETE Statement in FORALL Statement"

• Example 12-9, "Time Difference for INSERT Statement in FOR LOOP and FORALL Statements"

• Example 12-10, "FORALL Statement for Subset of Collection"

• Example 12-11, "FORALL Statements for Sparse Collection and Its Subsets"

• Example 12-12, "Handling FORALL Exceptions Immediately"

• Example 12-13, "Handling FORALL Exceptions After FORALL Statement Completes"

• Example 12-26, "DELETE with RETURN BULK COLLECT INTO in FORALL Statement"

• Example 12-28, "Anonymous Block Bulk-Binds Input Host Array"

Related Topics

In this chapter:

• "FOR LOOP Statement"

• "Implicit Cursor Attribute"

In other chapters:

• "FORALL Statement"

• "BULK COLLECT Clause"

• "Using FORALL Statement and BULK COLLECT Clause Together"