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
Other monadic - SQL In 2VL there are just 4 (2 2 ) monadic operators, of which negation is really the only "useful" one. When a third truth value is introduced we have 27 (3 3

Using research notes and Oracle documentation plan and execute an upgrade of an installation of Oracle 10g to Oracle 11g release 1. To do this you must show in screen shots and wri

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

Row Operators The Row operators return or reference the particular rows. ALL retains the duplicate rows in the result of a query or in an aggregate expression. The DISTINCT el

Due to an increase in overhead costs, the buying price of all items needs to be increased. Management wants to see a report before deciding how much each product will go up. Add to

Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th

Positional and Named Notation You can write the actual parameters when calling a subprogram, using either positional or named notation. That is, you can point to the relationsh

LAWS / RULES - Dollo's Law                     :           Living organisms do exhibit evolutionary irreversibility or evolution is irreversible. Williston's Law

Recursion versus Iteration Dissimilar the iteration, recursion is not crucial to PL/SQL programming. Any problem which can be solved using recursion can be solving using the it

Example of Check Constraints Example: Workaround for when subqueries not permitted in CHECK constraints CREATE FUNCTION NO_MORE_THAN_20000_ENROLMENTS ( ) RETURNS BOOLEAN