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
Actually the first thing is (even before SQL) – better don’t do this at all 😉 And you probably would be surprised how many times this can be proved true for at least few statements in each code snippet 🙂
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 AMIS Technology Blog… 😉