|PL/SQL User's Guide and Reference
Release 2 (9.2)
Part Number A96624-01
PL/SQL Language Elements, 22 of 52
FORALL statement instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the
FORALL statement contains an iteration scheme, it is not a
FOR loop. For more information, see "Reducing Loop Overhead for Collections with Bulk Binds".
This is 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. So, another variable with the same name cannot be referenced inside the statement. Inside a
index_name cannot appear in expressions and cannot be assigned a value.
These are expressions that must yield number, which, if necessary, PL/SQL rounds to the nearest integer. The integers must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range. The expressions are evaluated only when the
FORALL statement is first entered.
These optional keywords cause the
FORALL loop to continue even if some DML operations fail. 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.
This must be an
DELETE statement that references collection elements.
The SQL statement can reference more than one collection. However, 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
FORALLloop, you cannot refer to the same collection in both the
SETclause and the
WHEREclause of an
UPDATEstatement. You might need to make a second copy of the collection and refer to the new name in the
FORALLstatement only in server-side programs (not in client-side programs). Otherwise, you get the error this feature is not supported in client-side programs.
DELETEstatement must reference at least one collection. For example, a
FORALLstatement that inserts a set of constant values in a loop raises an exception.
CREATE TABLE coords (x NUMBER, y NUMBER); CREATE TYPE Pair AS OBJECT (m NUMBER, n NUMBER); DECLARE TYPE PairTab IS TABLE OF Pair; pairs PairTab := PairTab(Pair(1,2), Pair(3,4), Pair(5,6)); TYPE NumTab IS TABLE OF NUMBER; nums NumTab := NumTab(1, 2, 3); BEGIN /* The following statement fails. */ FORALL i IN 1..3 UPDATE coords SET (x, y) = pairs(i) WHERE x = nums(i); END;
The workaround is to decompose the composite values manually:
DECLARE TYPE PairTab IS TABLE OF Pair; pairs PairTab := PairTab(Pair(1,2), Pair(3,4), Pair(5,6)); TYPE NumTab IS TABLE OF NUMBER; nums NumTab := NumTab(1, 2, 3); BEGIN /* The following statement succeeds. */ FORALL i in 1..3 UPDATE coords SET (x, y) = (pairs(i).m, pairs(i).n) WHERE x = nums(i); END;
%BULK_ROWCOUNTcannot be assigned to other collections. Also, it cannot be passed as a parameter to subprograms.
The following example shows that 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;
Remember, the PL/SQL engine bulk-binds only subscripted collections. So, in the following example, it does not bulk-bind the collection
sals, which is passed to the function