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
Inner Join We have learned how to retrieve data from one table by using SELECT statement. But, as we have learned, normalized relational databases mean the data is spread betw

Using FOR UPDATE If you declare a cursor which will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you should use the FOR UPDATE clause to obtain an

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illu

Special cases of projection This section describes the identity projection, r {ALL BUT}, and the projection on no attributes, r { }, which yields TABLE_DUM when r is empty, ot

Use of COUNT in SQL It describes and discusses various general methods of expressing constraints, eventually noting that support for "=" with relation operands is sufficient f

Dynamic SQL: The Most PL/SQL programs do a predictable, specific job. For illustration, a stored procedure may accept an employee number and salary increase, and then update t

Effects of NULL for Table Expression Here's an important distinction between expressions denoting tables and expressions denoting multisets of rows: a table expression cannot

Keyword and Parameter Description: label_name: This is an undeclared identifier which labels an executable statement or the PL/SQL block. You can use a GOTO statement to

Using Aggregation on Nested Tables Example is the most direct translation of its counterpart in the theory book that can be obtained in SQL but it is so over-elaborate that no

Usefulness of Data Type In SQL, as in most computer languages, a type can be used for constraining the values that are permitted to be used for some purpose. In particular, i