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

Assignment Help:

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;


Related Discussions:- Using forall statement - bulk bind performance improvement

Usefulness of data type, Usefulness of Data Type In SQL, as in most c...

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

Using for update, Using FOR UPDATE If you declare a cursor which will ...

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

Parameter and keyword description - %type attribute, Parameter and Keyword ...

Parameter and Keyword Description: collection_name: This keyword identifies the index-by table, nested table, or varray formerly declared within the present scope. cu

Mutual recursion, Mutual Recursion The Subprograms are mutually recursi...

Mutual Recursion The Subprograms are mutually recursive if they directly or indirectly call each other. In the illustration below, the Boolean functions odd & even, that dete

Sql queries-oracle , 1- You can check attribute names from each table in D...

1- You can check attribute names from each table in DBF11 by running for example:  desc dbf11.Member;  desc dbf11.Agent;  desc dbf11.Producer; Because some attribute names in

Creating and destroying base tables, Creating and Destroying Base Tables: ...

Creating and Destroying Base Tables: Example shows an SQL command to create the base table counterpart of the ENROLMENT variable Example  Creating a base table. CREATE T

Boolean values-assignments in pl/sql, Boolean Values Only the values TRU...

Boolean Values Only the values TRUE, FALSE, & NULL can be assigned to a Boolean variable. For illustration, given the declaration DECLARE done BOOLEAN; the following statements

Information hiding in pl/sql, Information Hiding   With the informatio...

Information Hiding   With the information hiding, you see only the details that are significant at a given level of algorithm and data structure design. The Information hiding

Renaming columns - sql, Renaming Columns - SQL SQL has no direct count...

Renaming Columns - SQL SQL has no direct counterpart of RENAME. To derive the table on the right in Figure 4.4 from the table on the left, Tutorial D has IS_CALLED RENAME ( St

Data abstraction, Data Abstraction The Data abstraction extracts the im...

Data Abstraction The Data abstraction extracts the important properties of data while ignoring the not necessary details. Once you design a data structure, you can fail to reme

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd