Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
|
|
View PDF |
The FORALL
statement issues a series of static or dynamic DML 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 with Bulk SQL".
Syntax
for all statement ::=
bounds_clause ::=
Keyword and Parameter Description
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.
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. See "Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute".
sql_statement
A static, such as UPDATE
or DELETE
, or dynamic (EXECUTE
IMMEDIATE
) DML 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.
For examples, see the following:
Related Topics