forall and bulk collect

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.