PL/SQL Bulk Collect And Bulk bind

   

    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. 

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;

/

 Bulk bind: -

    Bulk bind is a process which is used to improve the performance of the application through collections, i.e... Once we are storing the data in a collection then we are storing. The data in a collection then we are submitting this collection to the SQL all values are modified (0r) deleted (or) inserted at a time only. In bulk bind process, we are using for all statements. Bulk bind is a reverse process of the bulk collect.

Syntax: - FORALL variablename in collectionvar.FIRST...Collectionvar.LAST                        dmlstatements;

Example:-

DECLARE

   TYPE t1 IS VARRAY (10) OF NUMBER (10);

   V_t   t1

            := t1 (20,

                   30,

                   40,

                   50,

                   60,

                   70,

                   80,

                   90);

BEGIN

   FORALL i IN v_t.FIRST .. v_t.LAST

      UPDATE emp

         SET sal = sal + 100

       WHERE deptno = v_t (i);

END;

Bulk collect clauses and also modifies salary of the empno using bulk bind process.

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;

   FORALL I IN v_t.FIRST .. v_t.LAST

      UPDATE emp

         SET sal = sal + 100

       WHERE empno = v_t (i);

END;

    

    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;

   V_t.delete (3);

   FORALL I IN v_t.FIRST .. v_t.LAST

      UPDATE emp

         SET sal = sal + 100

       WHERE empno = v_t (i);

END;

    Error: Element at index(3) does not exist

    

    Note: - before we are applying bulk bind process collections doesn’t have any gaps.to overcome this problem oracle log introduced ‘indices of’ clause in collections

   Solution:-     

   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;

   V_t.delete (3);

   FORALL I IN INDICES OF v_t

      UPDATE emp

         SET sal = sal + 100

       WHERE empno = v_t (i);

END;

   Sql%bulk_rowcount: - this attribute only used in bulk bind process. This attribute automatically counts, number of records in each iteration.

       Syntax: - sql%bulk_rowcount(index variable)

Example:-

DECLARE

   TYPE t1 IS VARRAY (10) OF NUMBER (10);

   V_t   t1

            := t1 (10,

                   20,

                   30,

                   40,

                   50,

                   60,

                   70);

BEGIN

   FORALL i IN v_t.FIRST .. v_t.LAST

      UPDATE emp

         SET sal = sal + 100

       WHERE deptno = v_t (i);

   FOR I IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.

       put_line (

            'affected number of rows'

         || ' '

         || v_t (i)

         || ' '

         || 'for'

         || '  '

         || SQL%BULK_ROWCOUNT (i));

   END LOOP;

END;

In bulk process, we are using bulk inserts, bulk updates, bulk deletes.

Bulk deletes: -

DECLARE

   TYPE t1 IS VARRAY (10) OF NUMBER (10);

   V_t   t1

            := t1 (20,

                   30,

                   50,

                   60);

BEGIN

   FORALL i IN v_t.FIRST .. v_t.LAST

      DELETE FROM emp

            WHERE deptno = v_t (i);

END;