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
%ROWCOUNT When its cursor or cursor variable is opened, the %ROWCOUNT is zeroed. Before the first fetch, the %ROWCOUNT yields 0. Afterward, it yields the number of rows fetche

Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to the

Expressions   An expression is a randomly complex combination of the constants, variables, literals, operators, & function calls. The simplest expression is the single variabl

Data Types in SQL - Character CHARACTER or, synonymously, CHAR, for character strings. When this type is to be the declared type of something (e.g., a column), the permissible

EXIT Statement   You can use the EXIT statement to exit a loop. The EXIT statement has 2 forms: the conditional EXIT WHEN and the unconditional EXIT. With the either form, you

SQL Is a Database Language: The commands given to a DBMS by an application are written in the database language of the DBMS. The term data sublanguage is sometimes used instea

(a) What are decision support systems, and what role do they play in the business environment? (b) Data warehousing is defined as "a subject-oriented, integrated, non-volatile c

Majority of Differences among 9i, 10G, 11G :- These are some combine feature which has differences among others. Automatic Workload Repository (AWR) Drop database' s

CURRVAL and NEXTVAL The series is a schema object which generates the sequential numbers. Whenever you form a sequence, you can specify its primary value and an increment. T

If two relations R and S are joined, then the non matching tuples of both R and S are ignored in __________________.