Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 19 February 2017

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;
Share this article :

1 comments: