Oracle Bulk Collect

    Whenever, we are submitting PL/SQL blocks into oracle server always SQL statements are executed. Through SQL engine and also procedural statements are executed. Through Procedural statement executor. This procedural statement executor is available in PL/SQL engine, whenever we are using large amount of loading through SQL, PL/SQL statements always oracle server executes these statements separately through these engines. This type of execution methodology always content switching execution methodology degrades performance of the application. To overcome this problem, oracle introduced “Bulk Bind” process using collections, i.e. in this method oracle server executes all SQL statements at a time.

Oracle Bulk Collect:

Whenever we are using this clause automatically. Oracle server selecting date wise and store it into collections. Bulk collect clause used in

1.     select…into…clause

2.     cursor fetch statement

3.     DML returning clauses

1.     select…into…clause: -

Syntax:- 

       Select * bulk collect into collection_variable from tablename where condition;

Example:-

DECLARE

   TYPE t1 IS TABLE OF emp%ROWTYPE

                 INDEX BY BINARY_INTEGER;

   V_t   t1;

BEGIN

   SELECT *

     BULK COLLECT INTO v_t

     FROM emp;

   FOR I IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.put_line (v_t (i).ename || ' ' || v_t (i).sal);

   END LOOP;

END;

2 . Cursor fetch statement:-  we can also use bulk collect clause in cursor fetch statement.

  Syntax:- Fetch cursorname bulk collect into collection variable(limit any number);

Example:-

DECLARE

   TYPE t1 IS TABLE OF emp.empno%TYPE

                 INDEX BY BINARY_INTEGER;

   V_t   t1;

   CURSOR c1

   IS

      SELECT empno FROM emp;

BEGIN

   OPEN c1;

   FETCH c1

   BULK COLLECT INTO v_t;

   CLOSE c1;

   FOR I IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.put_line (v_t (i));

   END LOOP;

END;

Example:-

DECLARE

   TYPE t1 IS TABLE OF emp.job%TYPE

                 INDEX BY BINARY_INTEGER;

   V_t   t1;

   CURSOR c1

   IS

      SELECT job FROM emp;

BEGIN

   OPEN c1;

   FETCH c1

   BULK COLLECT INTO v_t;

   CLOSE c1;

   FOR I IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.put_line (v_t (i));

   END LOOP;

END;

/

Note: - through the bulk fetch, data dictionary data also.

Note: - if we want to calculate elapsed time we are using set-time method from dbms-utility package.

      Syntax: - variablename: =dbms_utility.get_time();

3. DML….returning clauses: -always returning clauses are used in DML statements only. We can also use bulk collect clauses in these returning clauses, but in this case we must use collections.

    Ex: - sql>variable z varchar2(10);

             Sql>update emp set sal=sal+100 where ename=’KING’ RETURNING job into:z;

             Sql> print z;

Bulk fetch: -

       Syntax: - update tablename set columnname=new value where condition returning col1, col2….bulk collect into collection var;

-         Write a PL/SQL stored procedure, update salaries of the clerk using bulk collect returning clauses, modified values are stored into index-table, and also display content from index by table.

  

   Example:-

      

CREATE OR REPLACE PROCEDURE p1

IS

   TYPE t1 IS TABLE OF emp%ROWTYPE

                 INDEX BY BINARY_INTEGER;

   V_t   t1;

BEGIN

      UPDATE emp

         SET sal = sal + 100

       WHERE JOB = 'CLERK'

   RETURNING EMPNO,

             ENAME,

             JOB,

             MGR,

             HIREDATE,

             sal,

             comM,

             deptno

        BULK COLLECT INTO v_t;

   DBMS_OUTPUT.put_line ('number clerks affected' || ' ' || SQL%ROWCOUNT);

   FOR I IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.put_line (v_t (i).ename || ' ' || v_t (i).job);

   END LOOP;

END;

/

You May Like:

Oracle Bulk Bind

PL/SQL Ref Cursor

PLSQL Record

PL/SQL Bulk Collect And Bulk bind

PL/SQL Index by tables (or) PL/SQL tables (or) associative arrays

PL/SQL Nested tables
PL/SQL Varray