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
Keyword & Parameter Description: WHEN: This keyword introduces the exception handler. You can have many exceptions execute the similar sequence of the statements by follo

Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t

Second Step at defining type SID in SQL CREATE TYPE SID AS VARCHAR(5) ; Explanation: TYPE SID announces that a type named SID is being defined to the system.

Remote Operations: As the illustration shows below, the PL/SQL subprograms can execute the dynamic SQL statements which refer to the objects on a remote database: PROCEDURE

"Not Enforced" Table Constraints A constraint that is not enforced is not really a constraint within the meaning of the act, but SQL does have such a concept and it needs to b

Operators on Tables and Rows Row Extraction TUPLE FROM r, SQL has row subqueries. These are just like scalar subqueries except that they may specify more than one column.

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi

Example of Table Literal - SQL Example: A Table Literal (correct version) VALUES ('S1', 'C1', 'Anne'), ('S1', 'C2', 'Anne'), ('S2', 'C1', 'Boris'), ('S3', 'C3'

write the program for traffic control system with 10 second, 15 secod, and 20 second delay

Albeit simple method : These all the truth tables give us our first as albeit simple method for proving a theorem: where check whether it can be written in propositional logic