Using forall statement - bulk bind performance improvement, PL-SQL Programming

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


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:



depts NumList := NumList();


-- 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);


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:


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:



depts NumList := NumList(10, 20, 30, 40);


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


Posted Date: 10/4/2012 3:28:25 AM | Location : United States

Related Discussions:- Using forall statement - bulk bind performance improvement, Assignment Help, Ask Question on Using forall statement - bulk bind performance improvement, Get Answer, Expert's Help, Using forall statement - bulk bind performance improvement Discussions

Write discussion on Using forall statement - bulk bind performance improvement
Your posts are moderated
Related Questions
Substitution and Instantiation - SQL It shows how NULL might appear in substitution for a parameter of a predicate and how it might thus participate in instantiation of that p

Cursor FOR Loops In most cases that need an explicit cursor, you can simplify the coding by using a cursor FOR loop rather of the OPEN, FETCH, and CLOSE statements. A cursor FO

Declaring and Initializing Objects: An object type is once defined and installed in the schema; you can use it to declare the objects in any PL/SQL, subprogram, block or packa

Procedural Constraint Enforcement (Triggers) SQL has an alternative method of addressing database integrity, involving event-driven procedural code. The special procedures th

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Updating by insertion Syntax : INSERT INTO ENROLMENT VALUES (SID ('S4'), 'Devinder', CID ('C1'));

Create a procedure named STATUS_SHIP_SP that allows a company to employee in the Shipping Department to update the status of an order to add shipping information. The BB_BASKETSTAT

TYPES OF EVOLUTION - Sequential evolution                  :                    Minor changes in the gene pool of a population from one generation to the next, with the resul

Introduction Oracle 9i - it was made public in the year 2001 with over 400 features, and graphics, it has merged the traditional business with modern internet application

Create a view named CustomerAddresses that shows the shipping and billing addresses for each customer in the MyGuitarShop database. This view should return these columns from the