Using bulk collect clause- bulk bind performance improvement, PL-SQL Programming

Using the BULK COLLECT Clause

The keywords BULK COLLECT specify the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the FETCH INTO, SELECT INTO, and RETURNING INTO clauses. The syntax for the above is shown below:

... BULK COLLECT INTO collection_name[, collection_name] ...

The SQL engine bulk-binds all the collections referenced in the INTO list. The parallel columns should store the scalar (not composite) values. In the illustration, the SQL engine loads the whole empno and ename database columns into the nested tables before returning the tables to the PL/SQL engine:

DECLARE

TYPE NumTab IS TABLE OF emp.empno%TYPE;

TYPE NameTab IS TABLE OF emp.ename%TYPE;

enums NumTab; -- no need to initialize

names NameTab;

BEGIN

SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;

...

END;

The SQL engine initializes and then expands the collections for you. (Though, it cannot expands the varrays beyond their maximum size.) Then, starting at index 1, it inserts the elements successively and overwrites any pre-existent elements.

The SQL engine bulk-binds the whole database columns. Therefore, if a table has 50,000 rows, then the engine loads 50,000 column values into the target collection. Though, you can use the pseudocolumn ROWNUM to limit the number of rows processed. In the illustration below, you limit the number of rows to 100:

DECLARE

TYPE NumTab IS TABLE OF emp.empno%TYPE;

sals NumTab;

BEGIN

SELECT sal BULK COLLECT INTO sals FROM emp WHERE ROWNUM <= 100;

...

END;

Posted Date: 10/4/2012 3:30:09 AM | Location : United States







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

Write discussion on Using bulk collect clause- bulk bind performance improvement
Your posts are moderated
Related Questions
Table Comparison - SQL The following definitions for relation comparisons: Let r1 and r2 be relations having the same heading. Then: r1 ⊆ r2 is true if every tuple of r1

Parameter and Keyword Description: label_name: This is an undeclared identifier which optionally labels a loop. When used, the label_name should be enclosed by double ang

Use the PLS_INTEGER Datatype When you require to declare an integer variable, use the datatype PLS_INTEGER that is the most efficient numeric type. That is as the PLS_INTEGER

Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff

Datatype Conversion At times it is necessary to convert a value from one datatype to another. For e.g. if you want to inspect a rowid, you should convert it to a character stri

a. Write an anonymous block that contains a PL/SQL function. Given an order number orderNo, the function will calculate the total number of the parts in the order. Then the anonym

What Are Subprograms? The Subprograms are named PL/SQL blocks which can take parameters and be invoked. The PL/SQL has 2 types of subprograms known as the procedure s and func

Keyword and Parameter Description: boolean_expression: This is an expression which results the Boolean value TRUE, FALSE, & NULL. It is related with a series of statement

SQL Cursor   The Oracle implicitly opens a cursor to process each SQL statement not related with an explicit cursor. The PL/SQL refers to the most current implicit cursor as t

Use Object Types and Collections The Collection types and object types increase your efficiency by allowing for the realistic data modeling. The Complex real-world entities an