Bulk Operations in PL/SQL Oracle Headquarters Redwood Shores1 e1698667100526

Bulk Operations in PL/SQL

When I was in Chicago for the OPP2008 and APEXposed event I talked to someone who seems to have trouble understanding bulk operations in PL/SQL. I helped him out by providing a test/demo script, that shows how it could be done. First of all, one of the most important rules of thumb that I got from the seminar (almost everyone talked about this):

  • If you can do it in SQL, do it in SQL
  • If you can’t, use PL/SQL
  • If you still can’t, then resort to some other tool, like Java, VB or whatever…

If you can do your update, entirely in SQL, then that is the fastest solution. But in the case described to me, there is a lot going on between the select (from table) and the update (maybe even some other table).

To work with the demonstration files, we need to create a table that we can use for this demonstration:

   1:  DELETE EMP;
   2:  DELETE DEPT;
   3:  DROP TABLE EMP;
   4:  DROP TABLE DEPT;
   5:   
   6:  CREATE TABLE DEPT
   7:         ( DEPTNO NUMBER(2)
   8:         , DNAME  VARCHAR2(14)
   9:         , LOC    VARCHAR2(13) 
  10:         );
  11:   
  12:  INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
  13:  INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
  14:  INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
  15:  INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
  16:   
  17:  CREATE TABLE EMP
  18:         ( EMPNO    NUMBER(4) NOT NULL
  19:         , ENAME    VARCHAR2(10)
  20:         , JOB      VARCHAR2(9)
  21:         , MGR      NUMBER(4)
  22:         , HIREDATE DATE
  23:         , SAL      NUMBER(7, 2)
  24:         , COMM     NUMBER(7, 2)
  25:         , DEPTNO   NUMBER(2)
  26:         );
  27:   
  28:  INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
  29:  INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
  30:  INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
  31:  INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981' , 'DD-MON-YYYY'), 2975, NULL, 20);
  32:  INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
  33:  INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981' , 'DD-MON-YYYY'), 2850, NULL, 30);
  34:  INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981' , 'DD-MON-YYYY'), 2450, NULL, 10);
  35:  INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
  36:  INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
  37:  INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981' , 'DD-MON-YYYY'), 1500,    0, 30);
  38:  INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
  39:  INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981' , 'DD-MON-YYYY'),  950, NULL, 30);
  40:  INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981' , 'DD-MON-YYYY'), 3000, NULL, 20);
  41:  INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
  42:   
  43:  COMMIT;

Then we create the code as a stored procedure

   1:  CREATE OR REPLACE PROCEDURE bulk_demonstration
   2:  IS
   3:    -- The maximum rows collected in the bulk collect operation
   4:    c_maxrows                       CONSTANT PLS_INTEGER := 5;
   5:   
   6:    -- Subtypes based on the columns in the cursor
   7:    SUBTYPE empno_t IS emp.empno%TYPE;
   8:    SUBTYPE ename_t IS emp.ename%TYPE;
   9:    SUBTYPE job_t IS emp.job%TYPE;
  10:    SUBTYPE mgr_t IS emp.mgr%TYPE;
  11:    SUBTYPE hiredate_t IS emp.hiredate%TYPE;
  12:    SUBTYPE sal_t IS emp.sal%TYPE;
  13:    SUBTYPE comm_t IS emp.comm%TYPE;
  14:    SUBTYPE deptno_t IS emp.deptno%TYPE;
  15:   
  16:    -- Column Collections for every column in the cursor
  17:    TYPE empno_cc IS TABLE OF empno_t       INDEX BY BINARY_INTEGER;
  18:    TYPE ename_cc IS TABLE OF ename_t       INDEX BY BINARY_INTEGER;
  19:    TYPE job_cc IS TABLE OF job_t           INDEX BY BINARY_INTEGER;
  20:    TYPE mgr_cc IS TABLE OF mgr_t           INDEX BY BINARY_INTEGER;
  21:    TYPE hiredate_cc IS TABLE OF hiredate_t INDEX BY BINARY_INTEGER;
  22:    TYPE sal_cc IS TABLE OF sal_t           INDEX BY BINARY_INTEGER;
  23:    TYPE comm_cc IS TABLE OF comm_t         INDEX BY BINARY_INTEGER;
  24:    TYPE deptno_cc IS TABLE OF deptno_t     INDEX BY BINARY_INTEGER;
  25:   
  26:    -- The cursor used
  27:    CURSOR emp_cur
  28:    IS
  29:      SELECT emp.empno
  30:           , emp.ename
  31:           , emp.job
  32:           , emp.mgr
  33:           , emp.hiredate
  34:           , emp.sal
  35:           , emp.comm
  36:           , emp.deptno
  37:        FROM emp;
  38:   
  39:    -- Local variables to 'catch' the results from the cursor
  40:    l_empno                                  empno_cc;
  41:    l_ename                                  ename_cc;
  42:    l_job                                    job_cc;
  43:    l_mgr                                    mgr_cc;
  44:    l_hiredate                               hiredate_cc;
  45:    l_sal                                    sal_cc;
  46:    l_comm                                   comm_cc;
  47:    l_deptno                                 deptno_cc;
  48:    -- local function to do something with the data
  49:    function raisesal(sal_in sal_t) return sal_t
  50:    is
  51:      l_returnvalue sal_t;
  52:    begin
  53:      l_returnvalue := sal_in * 1.1;
  54:      RETURN l_returnvalue;
  55:    end raisesal;
  56:      
  57:  BEGIN
  58:    -- open the cursor
  59:    OPEN emp_cur;
  60:    -- start a simple loop
  61:    LOOP
  62:      -- clear out the collections
  63:      l_empno.delete;
  64:      l_ename.delete;
  65:      l_job.delete;
  66:      l_mgr.delete;
  67:      l_hiredate.delete;
  68:      l_sal.delete;
  69:      l_comm.delete;
  70:      l_deptno.delete;
  71:      -- fetch from the cursor using bulk collect for the result, but limited to limit
  72:      FETCH emp_cur BULK COLLECT INTO l_empno
  73:                                    , l_ename
  74:                                    , l_job
  75:                                    , l_mgr
  76:                                    , l_hiredate
  77:                                    , l_sal
  78:                                    , l_comm
  79:                                    , l_deptno
  80:      LIMIT c_maxrows;
  81:      -- if we fetched any data
  82:      IF l_empno.count > 0 THEN
  83:        -- do some complicated stuff
  84:        FOR idx IN l_empno.first..l_empno.last LOOP
  85:  --        l_sal(idx) := l_sal(idx) * 1.1;
  86:          l_sal(idx) := raisesal(l_sal(idx));
  87:        END LOOP;
  88:        -- update the records in the database, using a bulk operation
  89:        FORALL idx IN l_empno.first..l_empno.last
  90:          UPDATE emp
  91:             SET sal = l_sal(idx)
  92:           WHERE empno = l_empno(idx);
  93:      END IF;
  94:      -- exit when we didn't fetch our maximum rows
  95:      EXIT WHEN l_empno.count < c_maxrows;
  96:    END LOOP;
  97:  END bulk_demonstration;

Then a small script to see if it works

   1:  CLEAR screen
   2:  SET serveroutput on
   3:  SELECT emp.empno
   4:       , emp.sal
   5:    FROM emp;
   6:  EXEC bulk_demonstration;
   7:  SELECT emp.empno
   8:       , emp.sal
   9:    FROM emp;

Let’s take the code from the stored procedure step by step:

Line 5: Define a constant to hold the maximum rows collected in a bulk collect operation

Line Description
4 Define a constant to hold the maximum rows collected in a bulk collect operation
This should of course be bigger than 5, but for demonstration purposes this is a nice value
7-14 Subtypes defined on the columns in the table
This is done so these types can be used in collections, parameters and variables
17-24 Collection types based on the column types
27-37 The cursor used to retrieve the data from the table
40-47 Variables based on the collection types, to catch the results from the cursor
In Oracle 11G you can catch the results into records instead of lots of scalar collections
49-55 A simple inline function to demonstrate the usage of the data
59 Open the cursor
Like you would always open it
61 Start of a simple loop
63-70 Clear out the collections
To make sure they only hold the retrieved data and no old data
72-80 Fetch the records from the cursor with a max of whatever the constant is set to
82 Check if any data was retrieved
84-87 A for loop to process the data just retrieved
89-92 Write all data back to the table in one pass
95 Exit the loop if we didn’t retrieve the maximum number of rows

 

The really interesting parts of this code are of course lines 72-80 where we retrieve the data from the cursor in a single pass. The BULK COLLECT keyword tells the engines to do a single switch and retrieve all data in one pass. The LIMIT keyword is included to preserve memory. Since all data will be retrieved this could lead to memory problems. Especially in a multi-user environment.

Then there is the FORALL statement (lines 89-92) that writes all the data in the collections back to the table in one pass.

Normally we would process the contents of the cursor record by record, but by adding these keywords we use the bulk operations provided by Oracle to speed up things.

Hope this helps your understanding of the Bulk Operations in PL/SQL.

This entry is crossposted at http://blog.bar-solutions.com.

One Response

  1. Alex Nuijten November 28, 2008