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:
<span class="lnum"> 1: </span><span class="kwrd">DELETE</span> EMP;
<span class="lnum"> 2: </span><span class="kwrd">DELETE</span> DEPT;
<span class="lnum"> 3: </span><span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> EMP;
<span class="lnum"> 4: </span><span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> DEPT;
<span class="lnum"> 5: </span>
<span class="lnum"> 6: </span><span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> DEPT
<span class="lnum"> 7: </span> ( DEPTNO NUMBER(2)
<span class="lnum"> 8: </span> , DNAME VARCHAR2(14)
<span class="lnum"> 9: </span> , LOC VARCHAR2(13)
<span class="lnum"> 10: </span> );
<span class="lnum"> 11: </span>
<span class="lnum"> 12: </span>INSERT <span class="kwrd">INTO</span> DEPT <span class="kwrd">VALUES</span> (10, <span class="str">'ACCOUNTING'</span>, <span class="str">'NEW YORK'</span>);
<span class="lnum"> 13: </span>INSERT <span class="kwrd">INTO</span> DEPT <span class="kwrd">VALUES</span> (20, <span class="str">'RESEARCH'</span>, <span class="str">'DALLAS'</span>);
<span class="lnum"> 14: </span>INSERT <span class="kwrd">INTO</span> DEPT <span class="kwrd">VALUES</span> (30, <span class="str">'SALES'</span>, <span class="str">'CHICAGO'</span>);
<span class="lnum"> 15: </span>INSERT <span class="kwrd">INTO</span> DEPT <span class="kwrd">VALUES</span> (40, <span class="str">'OPERATIONS'</span>, <span class="str">'BOSTON'</span>);
<span class="lnum"> 16: </span>
<span class="lnum"> 17: </span><span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> EMP
<span class="lnum"> 18: </span> ( EMPNO NUMBER(4) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>
<span class="lnum"> 19: </span> , ENAME VARCHAR2(10)
<span class="lnum"> 20: </span> , JOB VARCHAR2(9)
<span class="lnum"> 21: </span> , MGR NUMBER(4)
<span class="lnum"> 22: </span> , HIREDATE <span class="kwrd">DATE</span>
<span class="lnum"> 23: </span> , SAL NUMBER(7, 2)
<span class="lnum"> 24: </span> , COMM NUMBER(7, 2)
<span class="lnum"> 25: </span> , DEPTNO NUMBER(2)
<span class="lnum"> 26: </span> );
<span class="lnum"> 27: </span>
<span class="lnum"> 28: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7369, <span class="str">'SMITH'</span>, <span class="str">'CLERK'</span>, 7902, TO_DATE(<span class="str">'17-DEC-1980'</span>, <span class="str">'DD-MON-YYYY'</span>), 800, <span class="kwrd">NULL</span>, 20);
<span class="lnum"> 29: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7499, <span class="str">'ALLEN'</span>, <span class="str">'SALESMAN'</span>, 7698, TO_DATE(<span class="str">'20-FEB-1981'</span>, <span class="str">'DD-MON-YYYY'</span>), 1600, 300, 30);
<span class="lnum"> 30: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7521, <span class="str">'WARD'</span>, <span class="str">'SALESMAN'</span>, 7698, TO_DATE(<span class="str">'22-FEB-1981'</span>, <span class="str">'DD-MON-YYYY'</span>), 1250, 500, 30);
<span class="lnum"> 31: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7566, <span class="str">'JONES'</span>, <span class="str">'MANAGER'</span>, 7839, TO_DATE(<span class="str">'2-APR-1981'</span> , <span class="str">'DD-MON-YYYY'</span>), 2975, <span class="kwrd">NULL</span>, 20);
<span class="lnum"> 32: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7654, <span class="str">'MARTIN'</span>, <span class="str">'SALESMAN'</span>, 7698, TO_DATE(<span class="str">'28-SEP-1981'</span>, <span class="str">'DD-MON-YYYY'</span>), 1250, 1400, 30);
<span class="lnum"> 33: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7698, <span class="str">'BLAKE'</span>, <span class="str">'MANAGER'</span>, 7839, TO_DATE(<span class="str">'1-MAY-1981'</span> , <span class="str">'DD-MON-YYYY'</span>), 2850, <span class="kwrd">NULL</span>, 30);
<span class="lnum"> 34: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7782, <span class="str">'CLARK'</span>, <span class="str">'MANAGER'</span>, 7839, TO_DATE(<span class="str">'9-JUN-1981'</span> , <span class="str">'DD-MON-YYYY'</span>), 2450, <span class="kwrd">NULL</span>, 10);
<span class="lnum"> 35: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7788, <span class="str">'SCOTT'</span>, <span class="str">'ANALYST'</span>, 7566, TO_DATE(<span class="str">'09-DEC-1982'</span>, <span class="str">'DD-MON-YYYY'</span>), 3000, <span class="kwrd">NULL</span>, 20);
<span class="lnum"> 36: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7839, <span class="str">'KING'</span>, <span class="str">'PRESIDENT'</span>, <span class="kwrd">NULL</span>, TO_DATE(<span class="str">'17-NOV-1981'</span>, <span class="str">'DD-MON-YYYY'</span>), 5000, <span class="kwrd">NULL</span>, 10);
<span class="lnum"> 37: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7844, <span class="str">'TURNER'</span>, <span class="str">'SALESMAN'</span>, 7698, TO_DATE(<span class="str">'8-SEP-1981'</span> , <span class="str">'DD-MON-YYYY'</span>), 1500, 0, 30);
<span class="lnum"> 38: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7876, <span class="str">'ADAMS'</span>, <span class="str">'CLERK'</span>, 7788, TO_DATE(<span class="str">'12-JAN-1983'</span>, <span class="str">'DD-MON-YYYY'</span>), 1100, <span class="kwrd">NULL</span>, 20);
<span class="lnum"> 39: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7900, <span class="str">'JAMES'</span>, <span class="str">'CLERK'</span>, 7698, TO_DATE(<span class="str">'3-DEC-1981'</span> , <span class="str">'DD-MON-YYYY'</span>), 950, <span class="kwrd">NULL</span>, 30);
<span class="lnum"> 40: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7902, <span class="str">'FORD'</span>, <span class="str">'ANALYST'</span>, 7566, TO_DATE(<span class="str">'3-DEC-1981'</span> , <span class="str">'DD-MON-YYYY'</span>), 3000, <span class="kwrd">NULL</span>, 20);
<span class="lnum"> 41: </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7934, <span class="str">'MILLER'</span>, <span class="str">'CLERK'</span>, 7782, TO_DATE(<span class="str">'23-JAN-1982'</span>, <span class="str">'DD-MON-YYYY'</span>), 1300, <span class="kwrd">NULL</span>, 10);
<span class="lnum"> 42: </span>
<span class="lnum"> 43: </span><span class="kwrd">COMMIT</span>;
Then we create the code as a stored procedure
<span class="lnum"> 1: </span><span class="kwrd">CREATE</span> <span class="kwrd">OR</span> REPLACE <span class="kwrd">PROCEDURE</span> bulk_demonstration
<span class="lnum"> 2: </span><span class="kwrd">IS</span>
<span class="lnum"> 3: </span> <span class="rem">-- The maximum rows collected in the bulk collect operation</span>
<span class="lnum"> 4: </span> c_maxrows CONSTANT PLS_INTEGER := 5;
<span class="lnum"> 5: </span>
<span class="lnum"> 6: </span> <span class="rem">-- Subtypes based on the columns in the cursor</span>
<span class="lnum"> 7: </span> SUBTYPE empno_t <span class="kwrd">IS</span> emp.empno%TYPE;
<span class="lnum"> 8: </span> SUBTYPE ename_t <span class="kwrd">IS</span> emp.ename%TYPE;
<span class="lnum"> 9: </span> SUBTYPE job_t <span class="kwrd">IS</span> emp.job%TYPE;
<span class="lnum"> 10: </span> SUBTYPE mgr_t <span class="kwrd">IS</span> emp.mgr%TYPE;
<span class="lnum"> 11: </span> SUBTYPE hiredate_t <span class="kwrd">IS</span> emp.hiredate%TYPE;
<span class="lnum"> 12: </span> SUBTYPE sal_t <span class="kwrd">IS</span> emp.sal%TYPE;
<span class="lnum"> 13: </span> SUBTYPE comm_t <span class="kwrd">IS</span> emp.comm%TYPE;
<span class="lnum"> 14: </span> SUBTYPE deptno_t <span class="kwrd">IS</span> emp.deptno%TYPE;
<span class="lnum"> 15: </span>
<span class="lnum"> 16: </span> <span class="rem">-- Column Collections for every column in the cursor</span>
<span class="lnum"> 17: </span> TYPE empno_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> empno_t <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum"> 18: </span> TYPE ename_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> ename_t <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum"> 19: </span> TYPE job_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> job_t <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum"> 20: </span> TYPE mgr_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> mgr_t <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum"> 21: </span> TYPE hiredate_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> hiredate_t <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum"> 22: </span> TYPE sal_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> sal_t <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum"> 23: </span> TYPE comm_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> comm_t <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum"> 24: </span> TYPE deptno_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> deptno_t <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum"> 25: </span>
<span class="lnum"> 26: </span> <span class="rem">-- The cursor used</span>
<span class="lnum"> 27: </span> <span class="kwrd">CURSOR</span> emp_cur
<span class="lnum"> 28: </span> <span class="kwrd">IS</span>
<span class="lnum"> 29: </span> <span class="kwrd">SELECT</span> emp.empno
<span class="lnum"> 30: </span> , emp.ename
<span class="lnum"> 31: </span> , emp.job
<span class="lnum"> 32: </span> , emp.mgr
<span class="lnum"> 33: </span> , emp.hiredate
<span class="lnum"> 34: </span> , emp.sal
<span class="lnum"> 35: </span> , emp.comm
<span class="lnum"> 36: </span> , emp.deptno
<span class="lnum"> 37: </span> <span class="kwrd">FROM</span> emp;
<span class="lnum"> 38: </span>
<span class="lnum"> 39: </span> <span class="rem">-- Local variables to 'catch' the results from the cursor</span>
<span class="lnum"> 40: </span> l_empno empno_cc;
<span class="lnum"> 41: </span> l_ename ename_cc;
<span class="lnum"> 42: </span> l_job job_cc;
<span class="lnum"> 43: </span> l_mgr mgr_cc;
<span class="lnum"> 44: </span> l_hiredate hiredate_cc;
<span class="lnum"> 45: </span> l_sal sal_cc;
<span class="lnum"> 46: </span> l_comm comm_cc;
<span class="lnum"> 47: </span> l_deptno deptno_cc;
<span class="lnum"> 48: </span> <span class="rem">-- local function to do something with the data</span>
<span class="lnum"> 49: </span> <span class="kwrd">function</span> raisesal(sal_in sal_t) <span class="kwrd">return</span> sal_t
<span class="lnum"> 50: </span> <span class="kwrd">is</span>
<span class="lnum"> 51: </span> l_returnvalue sal_t;
<span class="lnum"> 52: </span> <span class="kwrd">begin</span>
<span class="lnum"> 53: </span> l_returnvalue := sal_in * 1.1;
<span class="lnum"> 54: </span> <span class="kwrd">RETURN</span> l_returnvalue;
<span class="lnum"> 55: </span> <span class="kwrd">end</span> raisesal;
<span class="lnum"> 56: </span>
<span class="lnum"> 57: </span><span class="kwrd">BEGIN</span>
<span class="lnum"> 58: </span> <span class="rem">-- open the cursor</span>
<span class="lnum"> 59: </span> <span class="kwrd">OPEN</span> emp_cur;
<span class="lnum"> 60: </span> <span class="rem">-- start a simple loop</span>
<span class="lnum"> 61: </span> LOOP
<span class="lnum"> 62: </span> <span class="rem">-- clear out the collections</span>
<span class="lnum"> 63: </span> l_empno.<span class="kwrd">delete</span>;
<span class="lnum"> 64: </span> l_ename.<span class="kwrd">delete</span>;
<span class="lnum"> 65: </span> l_job.<span class="kwrd">delete</span>;
<span class="lnum"> 66: </span> l_mgr.<span class="kwrd">delete</span>;
<span class="lnum"> 67: </span> l_hiredate.<span class="kwrd">delete</span>;
<span class="lnum"> 68: </span> l_sal.<span class="kwrd">delete</span>;
<span class="lnum"> 69: </span> l_comm.<span class="kwrd">delete</span>;
<span class="lnum"> 70: </span> l_deptno.<span class="kwrd">delete</span>;
<span class="lnum"> 71: </span> <span class="rem">-- fetch from the cursor using bulk collect for the result, but limited to limit</span>
<span class="lnum"> 72: </span> <span class="kwrd">FETCH</span> emp_cur <span class="kwrd">BULK</span> COLLECT <span class="kwrd">INTO</span> l_empno
<span class="lnum"> 73: </span> , l_ename
<span class="lnum"> 74: </span> , l_job
<span class="lnum"> 75: </span> , l_mgr
<span class="lnum"> 76: </span> , l_hiredate
<span class="lnum"> 77: </span> , l_sal
<span class="lnum"> 78: </span> , l_comm
<span class="lnum"> 79: </span> , l_deptno
<span class="lnum"> 80: </span> <span class="kwrd">LIMIT</span> c_maxrows;
<span class="lnum"> 81: </span> <span class="rem">-- if we fetched any data</span>
<span class="lnum"> 82: </span> <span class="kwrd">IF</span> l_empno.<span class="kwrd">count</span> > 0 <span class="kwrd">THEN</span>
<span class="lnum"> 83: </span> <span class="rem">-- do some complicated stuff</span>
<span class="lnum"> 84: </span> <span class="kwrd">FOR</span> idx <span class="kwrd">IN</span> l_empno.<span class="kwrd">first</span>..l_empno.<span class="kwrd">last</span> LOOP
<span class="lnum"> 85: </span><span class="rem">-- l_sal(idx) := l_sal(idx) * 1.1;</span>
<span class="lnum"> 86: </span> l_sal(idx) := raisesal(l_sal(idx));
<span class="lnum"> 87: </span> <span class="kwrd">END</span> LOOP;
<span class="lnum"> 88: </span> <span class="rem">-- update the records in the database, using a bulk operation</span>
<span class="lnum"> 89: </span> FORALL idx <span class="kwrd">IN</span> l_empno.<span class="kwrd">first</span>..l_empno.<span class="kwrd">last</span>
<span class="lnum"> 90: </span> <span class="kwrd">UPDATE</span> emp
<span class="lnum"> 91: </span> <span class="kwrd">SET</span> sal = l_sal(idx)
<span class="lnum"> 92: </span> <span class="kwrd">WHERE</span> empno = l_empno(idx);
<span class="lnum"> 93: </span> <span class="kwrd">END</span> <span class="kwrd">IF</span>;
<span class="lnum"> 94: </span> <span class="rem">-- exit when we didn't fetch our maximum rows</span>
<span class="lnum"> 95: </span> <span class="kwrd">EXIT</span> <span class="kwrd">WHEN</span> l_empno.<span class="kwrd">count</span> < c_maxrows;
<span class="lnum"> 96: </span> <span class="kwrd">END</span> LOOP;
<span class="lnum"> 97: </span><span class="kwrd">END</span> bulk_demonstration;
Then a small script to see if it works
<span class="lnum"> 1: </span>CLEAR screen
<span class="lnum"> 2: </span><span class="kwrd">SET</span> serveroutput <span class="kwrd">on</span>
<span class="lnum"> 3: </span><span class="kwrd">SELECT</span> emp.empno
<span class="lnum"> 4: </span> , emp.sal
<span class="lnum"> 5: </span> <span class="kwrd">FROM</span> emp;
<span class="lnum"> 6: </span><span class="kwrd">EXEC</span> bulk_demonstration;
<span class="lnum"> 7: </span><span class="kwrd">SELECT</span> emp.empno
<span class="lnum"> 8: </span> , emp.sal
<span class="lnum"> 9: </span> <span class="kwrd">FROM</span> 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.
Related posts:
This entry was posted by Patrick Barel on November 27, 2008 at 12:01 pm, and is filed under Database, Oracle. Follow any responses to this post through RSS 2.0.Both comments and pings are currently closed.
-
Comments are closed
- Comment Feed for this Post
Didn't find any related posts :(
I don’t think it is necessary to create a bunch of scalar collections, even in Oracle 10g…
SQL> select *
2 from v$version
3 /
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bi
PL/SQL Release 10.2.0.3.0 – Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 – Productio
NLSRTL Version 10.2.0.3.0 – Production
SQL> set serveroutput on
SQL> create table emp
2 (empno number
3 ,ename varchar2(30)
4 );
Table created.
SQL> insert into emp
2 select rownum
3 , object_name
4 from all_objects
5 where rownum
SQL> declare
2 cursor c_emp
3 is
4 select empno
5 , ename
6 from emp
7 ;
8 type emp_tt is table of emp%rowtype
9 index by binary_integer;
10 emp_t emp_tt;
11 begin
12 open c_emp;
13 loop
14 fetch c_emp bulk collect into emp_t;
15 exit when c_emp%notfound;
16 end loop;
17 close c_emp;
18 for i in 1 .. emp_t.count
19 loop
20 dbms_output.put_line (emp_t(i).ename||emp_t(i).empno);
21 end loop;
22 end;
23 /
DUAL1
DUAL2
SYSTEM_PRIVILEGE_MAP3
SYSTEM_PRIVILEGE_MAP4
TABLE_PRIVILEGE_MAP5
TABLE_PRIVILEGE_MAP6
STMT_AUDIT_OPTION_MAP7
STMT_AUDIT_OPTION_MAP8
MAP_OBJECT9
RE$NV_LIST10
PL/SQL procedure successfully completed.
This comment is cross-posted on Bar-Solutions…