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

Pages

Friday, 23 June 2017

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:
PL/SQL Nested tables
PL/SQL Varray
Share this article :

0 comments:

Post a Comment