The beginning of knowledge is the discovery of something we do not understand. [Frank Herbert]

Menu

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:

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: ENDIF;

94: -- exit when we didn't fetch our maximum rows

95: EXITWHEN 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.

Post navigation

2 thoughts on “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 🙂