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

Pages

Friday, 23 June 2017

Oracle 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.

Bulk bind 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;
You May Like:
 
Share this article :

0 comments:

Post a Comment