DECLARE
TYPE DeptList IS VARRAY(20) OF NUMBER;
depts DeptList := DeptList(10, 30, 70); -- department numbers
BEGIN FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno = depts(i);
END;
The above DELETE statement is sent to the SQL engine just once, even though it performs three DELETE operations:
DECLARE TYPE DeptList
IS VARRAY(10) OF NUMBER;
depts DeptList := DeptList(10,20,30,40,50);
BEGIN
FORALL j IN 3..5 -- bulk-bind only part of varray
UPDATE emp SET sal = sal * 1.10
WHERE deptno = depts(j);
END;
As the following example shows, the bounds of the FORALL loop can apply to part of a collection, not necessarily all the elements:
DECLARE
TYPE enoTab IS TABLE OF emp.empno%TYPE;
TYPE enameTab IS TABLE OF emp.ename%TYPE;
enum enoTab; -- no need to initialize
Name enameTab;
BEGIN SELECT empno, ename
BULK COLLECT INTO enum, name FROM emp;
END;
The target is to reduce the context switch between SQL engine and PL/SQL engine.