|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
FORALL statement issues a series of
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
WHERE clauses. For more information, see "Reducing Loop Overhead for DML Statements and Queries (FORALL, BULK COLLECT)".
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.
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.
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
The index collection must be a nested table, or an associative array indexed by
BINARY_INTEGER, whose elements are also
BINARY_INTEGER. If the index collection is empty, an exception is raised and the FORALL statement is not executed.
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
index_name cannot appear in expressions and cannot be assigned a value.
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.
DELETE statement that references collection elements in the
Although the SQL statement can reference more than one collection, the performance benefits apply only to subscripted collections.
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.
The following restrictions apply to the
You cannot loop through the elements of an associative array that has a string type for the key.
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
You can use the
FORALL statement only in server-side programs, not in client-side programs.
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
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
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
Collection subscripts must be just the index variable rather than an expression, such as
i rather than
The cursor attribute
%BULK_ROWCOUNT cannot be assigned to other collections, or be passed as a parameter to subprograms.
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), ...);