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
Package STANDARD The package named STANDARD defines the PL/SQL atmosphere. The package specification globally declares the exceptions, types, and subprograms that are available

Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e

Manipulating Individual Elements Faraway you have manipulated an entire collection. Within the SQL, to manipulate the individual elements of the collection, and then use the ope

Cursor FOR Loops In most cases that need an explicit cursor, you can simplify the coding by using a cursor FOR loop rather of the OPEN, FETCH, and CLOSE statements. A cursor FO

Parameter and Keyword Description:   table_reference: This identifies a table or view which should be available when you execute the INSERT statement, and for that you sho

Example of Using Aggregation on Nested Tables Example: How many students sat each exam WITH C_ER AS (SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM

Need for Dynamic SQL: You need dynamic SQL in the situations as follows: 1) You would like to execute a SQL data definition statement (like CREATE), a data control statemen

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

GOTO Statement   The GOTO statement branches categorically to a block label or statement label. The label should be exclusive within its scope and should precede a PL/SQL bloc

THEORY OF BIOGENESIS - This theory explains that the existing living organisms originated from pre-existing living beings not from non living entities. This concept of or