Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2) Part Number E10472-05 |
|
|
View PDF |
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
forall_statement ::=
bounds_clause ::=
Semantics
forall_statement
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.
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.
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.
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.
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.
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.
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
Related Topics
In this chapter:
In other chapters: