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

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;

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
Object Type: The object type is a user-defined composite datatype which encapsulates a data structure along with the functions and procedures required to manipulate the data

Create the four tables and populate them with the given data. Answer the following queries in SQL. 1. Get all part-color/part-city combinations. Note: Here and subsequently, the

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

MECHANISTI S THEORY-HAECKEL (1866) - Haeckel stating that after each catalysm, some new organism suddenly forms as a chance event in one stride from inanimate matter and sub

Using LOCK TABLE You use the LOCK TABLE statement to lock the whole database tables in the specified lock mode so that you can share or deny the access to them. For illustrati

Deriving Predicates from Predicates in SQL The corresponding section in the theory book describes how predicates can be derived from predicates using (a) the logical connectiv

EXCEPTION_INIT Pragma The pragma EXCEPTION_INIT relates an exception name with an Oracle error number. Which allow you to refer to any internal exception by the name and to wri

Read-Only Operator (+) - SQL The term read-only operator to the mathematical term function. Here I just need to add that the SQL standard reserves the term function for read-

Keyword and Parameter Description: label_name: This is an undeclared identifier which optionally labels the PL/SQL block. When used, label_name should be enclosed by the do

Change Sql file into CSV for product registration on Magento Project Description: I have a set of files that are in Sql format and could like for a developer to help me with