PL/SQL User's Guide and Reference Release 9.0.1 Part Number A89856-01 |
|
PL/SQL Language Elements, 22 of 52
The 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 FORALL
statement, 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 INSERT
, UPDATE
, or 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.
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 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 median
:
FORALL i IN 1..20 INSERT INTO emp2 VALUES (enums(i), names(i), median(sals), ...);
BULK COLLECT Clause
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|