Total Pageviews

August 26, 2015

8/26/2015 11:18:00 AM
Bulk Bind and Collect features were introduced to reduce the SQL processing overhead by efficient use of collections in PL/SQL code.
The PL/SQL engine executes procedural statements and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.

Bulk Collects (Reading data in bulk)
The bulk collect option instructs the SQL engine to bulk bind the output collections before returning them to the PL/SQL engine. This allows us to load data dynamically into collections at one shot for further processing.
Bulk collect can be used with SELECT INTO, FETCH INTO and RETURNING INTO statements.


CREATE OR REPLACE PROCEDURE xxemp_prc (ENO NUMBER)
AS
   TYPE NAME
   IS
      TABLE OF EMP.ENAME%TYPE
         INDEX BY BINARY_INTEGER;

   TYPE SALARY
   IS
      TABLE OF EMP.SAL%TYPE
         INDEX BY BINARY_INTEGER;

   N   NAME;
   P   SALARY;
BEGIN
         DELETE FROM   my_emp
               WHERE   EMPNO = ENO
              RETURN   ename, sal BULK COLLECT INTO   n, p;

   FOR i IN 1 .. n.COUNT
   LOOP
      INSERT INTO emp_DELETE (ENAME, SAL)
        VALUES   (n (i), p (i));
   END LOOP;
END xxemp_prc;

 
Related Posts Plugin for WordPress, Blogger...