|PL/SQL User's Guide and Reference
Part Number A89856-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 PL/SQL engine bulk-binds only subscripted collections.
All collection elements in the specified range must exist. If an element is missing or was deleted, you get an error.
FORALL statement fails, database changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous executions are not rolled back.
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
BULK COLLECT Clause