Using the FORALL Statement
The keyword FORALL instruct the PL/SQL engine to bulk-bind input collections before sending them all to the SQL engine. Though the FORALL statement contain an iteration scheme, but it is not a FOR loop. The syntax for the FORALL Statement is as shown below:
FORALL index IN lower_bound..upper_bound
sql_statement;
The index can only be referenced within the FORALL statement and only as the collection subscript. The SQL statement should be an INSERT, DELETE, or UPDATE statement which references all the collection elements. And, the bounds should state the valid range of the consecutive index numbers. The SQL statement is executed by the SQL engine once for each and every index number in the range. As the example below shows, you can use the bounds to bulk-bind random 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;
The SQL statement can reference more than one collection. Though, the PL/SQL engine bulk-binds only the subscripted collections. And hence, in the illustration below, it does not bulk-bind the collection sals, that are passed to the function median:
FORALL i IN 1..20
INSERT INTO emp2 VALUES (enums(i), names(i), median(sals), ...);
The next illustration shows that the collection subscript cannot be an expression:
FORALL j IN mgrs.FIRST..mgrs.LAST
DELETE FROM emp WHERE mgr = mgrs(j+1); -- illegal subscript
All the collection elements in the particular range must exist. If an element was deleted or is missing, you get an error, as the example below shows:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30, 40);
BEGIN
depts.DELETE(3); -- delete third element
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno = depts(i);
-- raises an "element does not exist" exception
END;