Multirow Processing
In the last chapter, you left off trying to retrieve the first names of the Doe family members from table WORKERS using a singleton SELECT. As you may have already guessed, Oracle has a better way to handle this multi-row processing problem.
A cursor in this context is a named SQL SELECT statement that you can use in your PL/SQL program to access multiple rows from a table, yet retrieve them one row at a time.
Cursor Declarations
You declare cursors in the declaration section of a PL/SQL block just as you declare functions, procedures, and variables. And you should declare them with parameters, if required, just as you do with functions and procedures.
Listing 5-1 is a better solution to the last exercise in Chapter 4, where you were assigned the task of listing the Doe family’s first names.
Listing 5-1. An Example of Using a Cursor to Select Multiple Rows from a Table, cursor_the_doe_family.sql
01 rem cursor_the_doe_family.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem An anonymous PL/SQL procedure to select
04 rem the first names for the Doe family from
05 rem the Worker table.
06
07 set serveroutput on size 1000000;
08
09 declare
10
11 cursor c_workers(
12 aiv_last_name in WORKERS.last_name%TYPE) is
13 select first_name
14 from WORKERS
15 where last_name like aiv_last_name||'%'
16 order by id;
17
18 v_first_name WORKERS.first_name%TYPE;
19
20 begin
21 open c_workers('DOE'),
22 loop
23 fetch c_workers into v_first_name;
24
25 if c_workers%notfound then
26 close c_workers;
27 exit;
28 end if;
29
30 pl(v_first_name);
31 end loop;
32 end;
33 /
There’s a lot of new PL/SQL in this example. Let’s start with syntax. The syntax used in Listing 5-1 to declare CURSOR c_worker is as follows:
CURSOR <cursor_name> [(
<parameter_name_1> [IN] <parameter_data_type_1>,
<parameter_name_2> [IN] <parameter_data_type_2>,...
<parameter_name_N> [IN] <parameter_data_type_N> )] IS
<select_statement>;
where <cursor_name> is the name of the CURSOR, <parameter_name> is the name of a parameter being passed IN, <parameter_data_type> is the PL/SQL data type of the corresponding parameter, and <select_statement> is a SQL SELECT statement. The brackets ([]) around the parameters denote that they are optional.
Of course, there’s more to using cursors than just declaring them. Read on, as the next few subsections tell you all about what you can accomplish using cursors.
Fetching Rows from a Cursor Manually
The procedural PL/SQL, or control structure, syntax used to retrieve rows using the declared CURSOR in Listing 5-1 is as follows:
OPEN <cursor_name> [(
<parameter_value_1,
<parameter_value_2>,...
<parameter_value_N> )];
LOOP
-- loop until you manually EXIT;
FETCH <cursor_name> INTO
<variable_name_1>,
<variable_name_2>,...
<variable_name_N>;
END LOOP;
CLOSE <cursor_name>;
EXIT;
where <cursor_name> is the name of a declared CURSOR, <parameter_value> is a value to pass in to the CURSOR that will be utilized somewhere in its SQL SELECT statement, and <variable_name> is a PL/SQL variable to receive one of the SQL SELECT statement’s column values from the CURSOR.
The keyword OPEN is used to pass parameters to, and then execute, the cursor’s SQL SELECT statement. FETCH retrieves one row of column values from the cursor’s SELECT statement into a comma-separated list of PL/SQL variables. CLOSE does just that—it closes a CURSOR, releasing the cursor’s resources back to PL/SQL and the database.
The keyword LOOP is used to start an unconstrained loop. In this context, any PL/SQL code between the keywords LOOP and END LOOP will continue to be executed over and over again until you manually exit the loop with the keyword EXIT. It’s what I call a manual loop.
Here’s the output from the cursor_the_doe_family.sql script (Listing 5-1):
SQL> @cursor_the_doe_family
JOHN
JANE
JOHNNY
JANIE
PL/SQL procedure successfully completed.
Let’s take a moment to look at Listing 5-1, line by line:
- Lines 11 through 16 declare CURSOR c_workers, which will select the first name column from the table WORKERS, where the last name is like the one passed in.
- Line 18 declares a variable to hold the column value from the cursor.
- On line 21, I open the cursor, passing it the last name 'DOE'. This should give me all the first names for anyone with a name like DOE.
- Line 22 starts a manual loop. Lines 23 through 30 will be repeated endlessly until I exit manually, as I do on line 27.
- On line 23, I fetch the value of the column first_name into variable v_first_name.
- On line 25, I test the cursor c_workers for %notfound. If the SELECT statement did not find a row in the database for the current loop, the cursor c_worker will report no data found through the cursor variable %notfound.
- On line 26, if there is no data found, I CLOSE the cursor, and then EXIT the loop on line 27.
- On line 30, if a row is found, I display the first name on the screen using pl().
- On line 31, the keywords END LOOP signify the end of the LOOP.
Using a cursor for this type of problem—that is, retrieving multiple rows one row at a time—is definitely a better solution, if for no other reason than it was easier to maintain control over the program. But PL/SQL’s solutions to this problem are going to get even better.
On line 18 in Listing 5-1, I declared the variable v_first_name to hold the value from the database during each iteration of the LOOP. You can also use the keyword %ROWTYPE to declare a record for a cursor. So with one declaration, you can declare a record that has as many columns as the cursor it is defined for, with the same field names as the column names in the SELECT statement of the cursor. For example, I could have declared a record for CURSOR c_workers on line 18 as follows:
r_worker c_workers%ROWTYPE;
Pretty neat, huh? You’ll see an example of this later in the chapter in Listing 5-7. Now let’s take a look at using a cursor as a singleton SELECT.
By using a cursor, you can eliminate having to give up control of your programs when a NO_DATA_FOUND exception is raised because one is never raised. Instead, you can check the cursor variable for the status %notfound. Listing 5-2 is an example of using a cursor for a singleton SELECT. As you will see as you review the listing, sometimes using a cursor to retrieve one row can cost more in code than it’s worth.
Listing 5-2. An Example of Using a Cursor for a Singleton SELECT, insert_with_plsql_cursor_detection_for_update.sql
001 rem insert_with_plsql_cursor_detection_for_update.sql
002 rem by Donald J. Bales on 2014-10-20
003 rem An anonymous PL/SQL procedure to insert
004 rem values using PL/SQL literals and variables
005
006 set serveroutput on size 1000000;
007
008 declare
009
010 cursor c_worker_types(
011 aiv_code in WORKER_TYPES.code%TYPE) is
012 select id
013 from WORKER_TYPES
014 where code = aiv_code;
015
016 cursor c_gender_types(
017 aiv_code in GENDER_TYPES.code%TYPE) is
018 select id
019 from GENDER_TYPES
020 where code = aiv_code;
021
022 cursor c_workers(
023 aiv_name in WORKERS.name%TYPE,
024 aid_birth_date in WORKERS.birth_date%TYPE,
025 ain_gender_type_id in WORKERS.gender_type_id%TYPE) is
026 select id
027 from WORKERS
028 where name = aiv_name
029 and birth_date = aid_birth_date
030 and gender_type_id = ain_gender_type_id;
031
032 cursor c_worker_ids is
033 select WORKERS_ID.nextval worker_id
034 from SYS.DUAL;
035
036 cursor c_external_ids is
037 select lpad(to_char(EXTERNAL_ID_SEQ.nextval), 9, '0') external_id
038 from SYS.DUAL;
039
040 -- I declared these variables so I can get
041 -- the required ID values before I insert.
042 n_id WORKERS.id%TYPE;
043 n_worker_type_id WORKERS.worker_type_id%TYPE;
044 v_external_id WORKERS.external_id%TYPE;
045 v_first_name WORKERS.first_name%TYPE;
046 v_middle_name WORKERS.middle_name%TYPE;
047 v_last_name WORKERS.last_name%TYPE;
048 v_name WORKERS.name%TYPE;
049 d_birth_date WORKERS.birth_date%TYPE;
050 n_gender_type_id WORKERS.gender_type_id%TYPE;
051
052 -- I'll use these variables to hold the result
053 -- of the SQL insert and update statements.
054 n_inserted number := 0;
055 n_updated number := 0;
056
057 begin
058 v_first_name := 'JOHN';
059 v_middle_name := 'J.';
060 v_last_name := 'DOE';
061 v_name :=
062 rtrim(v_last_name||', '||v_first_name||' '||v_middle_name);
063 d_birth_date :=
064 to_date('19800101', 'YYYYMMDD'), -- I'm guessing
065
066 -- First, let's get the worker_type_id for a contractor
067 begin
068 open c_worker_types('C'),
069 fetch c_worker_types
070 into n_worker_type_id;
071 if c_worker_types%notfound then
072 raise_application_error(-20001,
073 'Can''t find the worker types ID for Contractor.'||
074 ' on select WORKER_TYPES'||
075 ' in filename insert_with_plsql_cursor_detection_for_update.sql'),
076 end if;
077 close c_worker_types;
078 exception
079 when OTHERS then
080 raise_application_error(-20002, SQLERRM||
081 ' on select WORKER_TYPES'||
082 ' in filename insert_with_plsql_cursor_detection_for_update.sql'),
083 end;
084
085 -- Next, let's get the gender_id for a male
086 begin
087 open c_gender_types('M'),
088 fetch c_gender_types
089 into n_gender_type_id;
090 if c_gender_types%notfound then
091 raise_application_error(-20003,
092 'Can''t find the gender ID for Male.'||
093 ' on select GENDER_TYPES'||
094 ' in filename insert_with_plsql_cursor_detection_for_update.sql'),
095 end if;
096 close c_gender_types;
097 exception
098 when OTHERS then
099 raise_application_error(-20004, SQLERRM||
100 ' on select GENDER_TYPES'||
101 ' in filename insert_with_plsql_cursor_detection_for_update.sql'),
102 end;
103
104 -- Detect any existing entries with the unique
105 -- combination of columns as in this constraint:
106 -- constraint WORKERS_UK2
107 -- unique (
108 -- name,
109 -- birth_date,
110 -- gender_id )
111 begin
112 open c_workers(v_name, d_birth_date, n_gender_type_id);
113 fetch c_workers
114 into n_id;
115 if c_workers%notfound then
116 n_id := NULL;
117 end if;
118 close c_workers;
119 exception
120 when OTHERS then
121 raise_application_error(-20005, SQLERRM||
122 ' on select WORKERS'||
123 ' in filename insert_with_plsql_cursor_detection_for_update.sql'),
124 end;
125
126 -- Conditionally insert the row
127 if n_id is NULL then
128 -- Now, let's get the next worker_id sequence
129 begin
130 open c_worker_ids;
131 fetch c_worker_ids
132 into n_id;
133 close c_worker_ids;
134 exception
135 when OTHERS then
136 raise_application_error(-20006, SQLERRM||
137 ' on select WORKER_IDS.nextval'||
138 ' in filename insert_with_plsql_cursor_detection_for_update.sql'),
139 end;
140
141 -- And then, let's get the next external_id sequence
142 begin
143 open c_external_ids;
144 fetch c_external_ids
145 into v_external_id;
146 if c_external_ids%notfound then
147 v_external_id := NULL;
148 end if;
149 close c_external_ids;
150 exception
151 when OTHERS then
152 raise_application_error(-20006, SQLERRM||
153 ' on select EXTERNAL_ID_SEQ.nextval'||
154 ' in filename insert_with_plsql_cursor_detection_for_update.sql'),
155 end;
156
157 -- Now that we have all the necessary ID values
158 -- we can finally insert a row!
159 begin
160 insert into WORKERS (
161 id,
162 worker_type_id,
163 external_id,
164 first_name,
165 middle_name,
166 last_name,
167 name,
168 birth_date,
169 gender_type_id )
170 values (
171 n_id,
172 n_worker_type_id,
173 v_external_id,
174 v_first_name,
175 v_middle_name,
176 v_last_name,
177 v_name,
178 d_birth_date,
179 n_gender_type_id );
180
181 n_inserted := sql%rowcount;
182 exception
183 when OTHERS then
184 raise_application_error(-20007, SQLERRM||
185 ' on insert WORKERS'||
186 ' in filename insert_with_plsql_cursor_detection_for_update.sql'),
187 end;
188 else
189 begin
190 update WORKERS
191 set worker_type_id = n_worker_type_id
192 where id = n_id;
193
194 n_updated := sql%rowcount;
195 exception
196 when OTHERS then
197 raise_application_error(-20008, SQLERRM||
198 ' on update WORKERS'||
199 ' in filename insert_with_plsql_cursor_detection_for_update.sql'),
200 end;
201 end if;
202
203 pl(to_char(n_inserted)||' row(s) inserted.'),
204 pl(to_char(n_updated)||' row(s) updated.'),
205 end;
206 /
207
208 commit;
Listing 5-2 doesn’t have any new syntax, but it does show a different utilization of the keywords OPEN, FETCH, and CLOSE. You may recall seeing a form of this source code originally in Listing 4-4. Now it has been modified to use cursors for its singleton SELECT statements, so I can argue when it’s a good idea to use cursors for singletons and when it’s not.
Let’s break down the listing:
- Lines 10 through 14 declare a cursor for table WORKER_TYPES.
- Lines 16 through 20 declare a cursor for table GENDER_TYPES.
- Lines 22 through 30 declare a cursor for table WORKERS. Later in the executable section, I’ll pass parameters for the worker’s name, birth date, and gender in order to try to find an existing row in the database.
- Lines 32 through 34 declare a cursor for allocating the next worker ID sequence value.
- Lines 36 through 38 declare a cursor for allocating the external ID sequence value.
- On line 68, I open the cursor for the worker type, passing the code value C as a parameter.
- On line 69 and 70, I try to fetch the corresponding worker_type_id value into variable n_worker_type_id.
- On line 71, I test the cursor variable c_worker_types%notfound to see if a corresponding ID value was found. If not, I raise an application error, which stops the execution of the program.
- Line 77 closes the cursor (that’s a mandatory programming task).
- On lines 68 through 83 collectively, I’ve blocked OPEN, FETCH, and CLOSE in order to capture any unusual errors.
- On lines 86 through 102 collectively, I do the same for table GENDER_TYPES as I have done for table WORKER_TYPES. My assessment: using a cursor for getting the code IDs gains me nothing. I don’t expect there to be an exception, so I really don’t gain any more control over the program by using a cursor for singleton SELECT as in this situation.
- On lines 111 through 124 collectively, I use a cursor to select a matching id from WORKERS. This time, there is an advantage in using a cursor for a singleton SELECT. Since I expect that the SELECT may not find any data, I can query the cursor variable c_workers%notfound to determine this. If the SELECT statement did not find a matching entry in the table, I set variable n_id to NULL to flag that no matching entry exists. In this instance, I no longer needed to code a WHEN NO_DATA_FOUND exception.
- On lines 129 through 139, and 142 through 155 collectively, I’ve used cursors to select the next sequence values from the database. Once again, since I don’t expect any possible errors, using cursors for singleton SELECT statements adds a lot of code but little value.
The moral of the story is that you may want to use a cursor for a singleton SELECT if you expect a NO_DATA_FOUND exception may be raised; otherwise, you may as well stick to a simple SELECT statement. In practice, I personally have no problem with utilizing the exception NO_DATA_FOUND, syntactic sugar that it is, but the choice is now yours.
It’s Your Turn to Fetch Manually
In this section, your assignment is to write a program using cursors. But first, I’ll present a point of view on why good use of cursors is so important.
Do you ever think about how many times you’ve written a given SQL statement? Do you ever find yourself writing the same statement more than once in your program? Just how many times should you write the same SQL statement? In my opinion, the answer is just once! There are a lot of reasons to avoid writing the same statement more than once. Let’s start with these:
- One SQL statement to accomplish one goal means fewer cursors in use on your database, and that means better performance.
- One SQL statement to accomplish one task means consistent behavior across your application’s presentation layers.
- One SQL statement to accomplish one requirement means it will be easier to maintain and modify your application’s code.
- One SQL statement to attain the goals just mentioned means saving money, and saved money is profit.
To attain better performance, consistent behavior, more maintainable code, and profit, you’re going to have to start thinking like an object-oriented programmer.
What’s your assignment? Write three packages for three tables. I’ll show you two out of the three so you have some models for your coding, but I expect you to stretch during the third exercise and it all on your own. Let’s start by modularizing a code table.
In the listings in Chapter 4 and now Chapter 5, I’ve repeatedly coded the same SQL SELECT statement in order to get the corresponding ID value for a given code. So rather than keep writing the same code over and over, I’ve created a package called WORKER_TYPE for table WORKER_TYPES. This is what I call a pseudo-object-oriented approach to programming in PL/SQL. Listing 5-3 is the worker type codes package spec, and Listing 5-4 is its implementation, or package body.
Listing 5-3. The WORKER_TYPE Package Spec, worker_type.pks
01 create or replace PACKAGE WORKER_TYPE as
02 /*
03 worker_type.pks
04 by Don Bales on 2014-10-20
05 Code Table WORKER_TYPES' methods.
06 */
07
08
09 -- Returns the id for the specified code value.
10
11 FUNCTION get_id(
12 aiv_code in WORKER_TYPES.code%TYPE )
13 return WORKER_TYPES.id%TYPE;
14
15
16 end WORKER_TYPE;
17 /
18 @se.sql WORKER_TYPE
In Listing 5-3, I’ve declared one function, get_id(aiv_code) return id. Now if programmers—whether they are coding in a PL/SQL program, a JDBC program, C, C++, Perl, PowerScript, and so on—want to get the id value for a corresponding code value, all they need to do is call the PL/SQL function WORKER_TYPE.get_id(), passing it an existing code value.
Listing 5-4. The WORKER_TYPE Package Body, worker_type.pkb
01 create or replace PACKAGE BODY WORKER_TYPE as
02 /*
03 worker_type.pkb
04 by Don Bales on 2014-10-20
05 Table WORKER_TYPES' methods
06 */
07
08
09 -- FUNCTIONS
10
11 FUNCTION get_id(
12 aiv_code in WORKER_TYPES.code%TYPE )
13 return WORKER_TYPES.id%TYPE is
14
15 n_id WORKER_TYPES.id%TYPE;
16
17 begin
18 select id
19 into n_id
20 from WORKER_TYPES
21 where code = aiv_code;
22
23 return n_id;
24 end get_id;
25
26
27 end WORKER_TYPE;
28 /
29 @be.sql WORKER_TYPE
Let’s look at the implementation, Listing 5-4:
- Line 11 declares the implementation for function get_id().
- Line 12 specifies that a calling program must pass in a code value.
- Line 13 specifies that the function will return a worker type ID value.
- Line 15 declares a local variable called n_id to hold the ID value retrieved from the database.
- Lines 18 through 21 contain a SQL SELECT statement to retrieve a corresponding ID value for the given code value in parameter aiv_code.
- On line 23, I return the retrieved ID value to the calling program unit.
- Lines 11 through 24 collectively implement a SQL SELECT statement to retrieve an ID value for a corresponding code value. I have not blocked this SQL statement, so it will raise a NO_DATA_FOUND exception should one occur. This means the calling program may want to block the call to WORKER_TYPE.get_id() in order to be able to report the exact error and the location where it occurred in the program.
Now, anywhere I would have coded another SQL statement to select an ID from table WORKER_TYPES for a given code, I can simply code
n_worker_type_id := WORKER_TYPE.get_id('C'),
or better yet
begin
n_worker_type_id := WORKER_TYPE.get_id('C'),
exception
when OTHERS then
raise_application_error(-20???, SQLERRM||
' on call WORKERT_TYPE.get_id()'||
' in <my_program_unit>'),
end;
The latter example, although more code, will make it easier to troubleshoot your program when an error does occur.
So what’s part one of your assignment? Create a code package for table GENDER_TYPES, and that includes compiling and testing it. When you’re finished, continue reading for part two.
In a similar fashion to the code tables, I’ve written SQL SELECT statements numerous times in order to allocate database-generated sequence values. So I’ve created package WORKER to hold functions and procedures for table WORKERS. The first function I’ve added is get_id(). However, this time, get_id() is called without parameters and returns the next available sequence number for the WORKERS id column.
Take a look at package WORKER. Listing 5-5 is its package spec, and Listing 5-6 is its implementation.
Listing 5-5. The WORKER Package Spec, worker.pks
01 create or replace PACKAGE WORKER as
02 /*
03 worker.pks
04 by Donald J. Bales on 2014-10-20
05 Table WORKERS' methods.
06 */
07
08
09 -- Return the next ID sequence value
10
11 FUNCTION get_id
12 return WORKERS.id%TYPE;
13
14
15 end WORKER;
16 /
17 @se.sql WORKER
In Listing 5-5, I’ve declared one function: get_id(). It will return the next WORKERS_ID sequence value.
Listing 5-6. The WORKER Package Body, worker.pkb
01 create or replace PACKAGE BODY WORKER as
02 /*
03 worker.pkb
04 by Donald J. Bales on 2014-10-20
05 Table WORKERS' methods
06 */
07
08
09 -- FUNCTIONS
10
11 FUNCTION get_id
12 return WORKERS.id%TYPE is
13
14 n_id WORKERS.id%TYPE;
15
16 begin
17 select WORKERS_ID.nextval
18 into n_id
19 from SYS.DUAL;
20
21 return n_id;
22 end get_id;
23
24
25 end WORKER;
26 /
27 @be.sql WORKER
In Listing 5-6, I’ve coded a function that queries and returns the sequence value from WORKERS_ID.nextval. Remember that the SYS.DUAL table has one row, so selecting any value against it will return one value.
Your assignment for part two is to add three functions to package WORKER:
- A get_external_id() function, which will return a value from sequence EXTERNAL_ID_SEQ as a properly zero-left-padded varchar2.
- A get_unformatted_name() function, which will return a concatenated value for three parameters: aiv_first_name, aiv_middle_name, and aiv_last_name. Use the concatenation operator—two vertical bars (||)—to concatenate the varchar2 values together. (Hint: I’ve already coded this concatenation time and time again in the previous listings.)
- An is_duplicate() function, which you will pass three parameters: aiv_name, aid_birth_date, and ain_gender_type_id. It should return a Boolean value of TRUE if a duplicate exists; otherwise, it should return FALSE.
Remember to test all three functions. Yes, write a test unit for each one!
Let’s move on to part three of your assignment.
Write a Modularized Version of Insert with PL/SQL Detection
Do you remember Listing 4-3? Now that you have functions for five out of the five blocked singleton SELECT statements, rewrite Listing 4-3 (insert_with_plsql_detection.sql), but this time replace all the singletons and the creation of the value for v_name with function calls to the appropriate packages. Save your script as insert_with_modularity.sql. Then execute it until it works.
My Solution
Listing 5-7 is my solution to this third part of the exercise.
Listing 5-7. A Modular Approach to Inserting, insert_with_modularity.sql
01 rem insert_with_modularity.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem An anonymous PL/SQL procedure to insert
04 rem values using PL/SQL functions
05
06 set serveroutput on size 1000000;
07
08 declare
09
10 -- I declared this record, so I can get
11 -- the required ID values before I insert.
12 r_worker WORKERS%ROWTYPE;
13
14 -- I'll use this variable to hold the result
15 -- of the SQL insert statement.
16 n_count number := 0;
17
18 begin
19 r_worker.first_name := 'JOHN';
20 r_worker.middle_name := 'J.';
21 r_worker.last_name := 'DOE';
22 -- Using the same function to get this derived value
23 -- from all programs will ensure its value is consistent
24 r_worker.name := WORKER.get_formatted_name(
25 r_worker.first_name, r_worker.middle_name, r_worker.last_name);
26 r_worker.birth_date :=
27 to_date('19800101', 'YYYYMMDD'), -- I'm guessing
28
29 -- First, let's get the worker_type_id for a contractor
30 begin
31 r_worker.worker_type_id := WORKER_TYPE.get_id('C'),
32 exception
33 when OTHERS then
34 raise_application_error(-20001, SQLERRM||
35 ' on call WORKER_TYPE.get_id(''C'')'||
36 ' in filename insert_with_modularity.sql'),
37 end;
38
39 -- Next, let's get the gender_id for a male
40 begin
41 r_worker.gender_type_id := GENDER_TYPE.get_id('M'),
42 exception
43 when OTHERS then
44 raise_application_error(-20002, SQLERRM||
45 ' on call GENDER_TYPE.get_id(''M'')'||
46 ' in filename insert_with_modularity.sql'),
47 end;
48
49 -- Detect any existing entries, and
50 -- then conditionally insert the row
51 if not WORKER.is_duplicate(
52 r_worker.name, r_worker.birth_date, r_worker.gender_type_id) then
53 -- I'm not going to block the next two calls,
54 -- because it's highly unlikely that I could
55 -- ever get an error allocating a sequnce.
56
57 -- Now, let's get the next id sequence.
58 -- no parameters, so no parentheses needed
59 r_worker.id := WORKER.get_id;
60
61 -- And then, let's get the next external_id sequence
62 -- no parameters, so no parentheses needed
63 r_worker.external_id := WORKER.get_external_id;
64
65 -- Now that we have all the necessary ID values
66 -- we can finally insert a row!
67 begin
68 -- Since I declared r_worker based on WORKERS, I
69 -- can skip the column list and just use the record.
70 insert into WORKERS values r_worker;
71
72 n_count := sql%rowcount;
73 exception
74 when OTHERS then
75 raise_application_error(-20003, SQLERRM||
76 ' on insert WORKERS'||
77 ' in filename insert_with_modularity.sql'),
78 end;
79 end if;
80
81 pl(to_char(n_count)||' row(s) inserted.'),
82 end;
83 /
84
85 commit;
I’m not going to explain Listing 5-7 in detail, because it’s well commented and you should be able to understand it by now. But I will say that, unless there’s more you have to do between the selection of codes and sequences and before the insert, you’re probably still better off using SQL to do all the work!
Now let me introduce you to the star of the PL/SQL show: CURSOR FOR LOOP.
Fetching Rows from a Cursor Automatically
CURSOR FOR LOOP is the heart and soul of PL/SQL’s abilities. The CURSOR FOR LOOP allows you to work with a cursor that returns multiple rows, one row at a time, using a very nice and neat structure that does everything for you automatically.
Listing 5-8 is a revision of Listing 5-1. The difference is that Listing 5-8 utilizes the “automatic” CURSOR FOR LOOP, instead of the manually coded loop involving OPEN, FETCH, and CLOSE.
Listing 5-8. An Example of Using a CURSOR FOR LOOP, cursor_for_loop_the_doe_family.sql
01 rem cursor_for_loop_the_doe_family.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem An anonymous PL/SQL procedure to select
04 rem the first names for the Doe family from
05 rem the Workers table.
06
07 set serveroutput on size 1000000;
08
09 declare
10
11 cursor c_workers(
12 aiv_last_name in WORKERS.last_name%TYPE) is
13 select first_name
14 from WORKERS
15 where last_name like aiv_last_name||'%'
16 order by id;
17
18 begin
19 for r_worker in c_workers('DOE') loop
20 pl(r_worker.first_name);
21 end loop;
22 end;
23 /
The CURSOR FOR LOOP syntax used in Listing 5-8 is as follows:
FOR <record_name> IN <cursor_name> [(<cursor_parameters>)] LOOP
-- Put your PL/SQL to be executed for each row here
END LOOP;
where <record_name> is the name of the record that will contain fields that correspond to columns in the associated cursor’s SELECT statement, <cursor_name> is the name of the associated CURSOR, and <cursor_parameters> is a list of zero or more parameters to be passed to the CURSOR. The brackets around the parameters denote that they are optional; they are needed only if the associated CURSOR was declared with parameters.
Listing 5-8 is short but powerful. In lines 11 through 16, I declare a CURSOR c_workers. Then, on line 19, I use the CURSOR FOR LOOP syntax to automatically OPEN the CURSOR and LOOP until there’s no data found, placing any column values in an automatically declared record r_worker that is in scope only inside the FOR LOOP. If at least one row was retrieved from the database, the CURSOR FOR LOOP automatically CLOSEs the OPEN CURSOR when the LOOP ENDs. As I documented earlier in Table 3-1, I use the prefix c_ for cursors and the prefix r_ for records. This naming convention makes it easy to see exactly what is going on in PL/SQL.
I told you PL/SQL’s solution to the last exercise in Chapter 4 was going to get even better. As you can see, the CURSOR FOR LOOP represents some powerfully compact syntax!
You can use a CURSOR FOR LOOP as long as you don’t need the values of the fields in the automatically declared record outside the loop. And it will do you no good to declare the record outside the loop because the CURSOR FOR LOOP will still declare its own. If you do need the cursor values beyond the fetch, you’ll need to assign the record values to variables declared outside the loop or use a manual loop, as I did earlier in Listing 5-1.
Enough talk—it’s time you put CURSOR FOR LOOP to work for you.
It’s Your Turn to Fetch Automatically
In the development process, you’ll eventually need to test what you’ve built with some realistic data; otherwise, you won’t know how your code will perform in production. So your assignment here is to create a PL/SQL procedure that will populate table WORKERS with test data.
If you combine 100 last names, 100 first names, and 26 different middle initials, you get 260,000 entries for table WORKERS. A quarter of a million entries in a table is a fair amount of data for testing.
To help you out with this assignment, I’ve created three scripts to create the TOP_100_FIRST_NAMES, TOP_100_LAST_NAMES, and A_THRU_Z tables for you. I’ve placed these table scripts in the downloaded source code directory for Chapter 5, as top_100_first_names.tab, top_100_last_names.tab, and a_thru_z.tab, respectively. You can create those tables by running these scripts.
In order to populate table WORKERS, I suggest you create a PL/SQL procedure that uses nested CURSOR FOR LOOPs to iterate through each table’s entries, inserting an entry into the table for each loop, in the most nested FOR LOOP. Your executable code will look something like this:
begin
for r_last in c_last loop
for r_first in c_first loop
for r_middle in c_middle loop
-- initialize the variables to be used in the insert statement here.
insert into WORKERS ...
end loop;
end loop;
end loop;
end;
Of course, you’ll need to supply values for other columns. For those, I suggest you do the following:
- For id, call function WORKER.get_id().
- For worker_type_id, use an IF statement to flip back and forth between the ID values for an employee vs. contractor on every other entry.
- For external_id, call function WORKER.get_external_id().
- For birth_date, call function DATES.random(1940, 1990); this will give you a randomly generated date between the years 1940 and 1990.
- For gender_type_id, use an IF statement to specify the correct ID value for the corresponding code found in the TOP_100_FIRST_NAME table.
Add a variable to keep track of how many inserts you’ve made, and then display that number on the screen when you’re finished inserting. Save the script as worker cursor_for_loop.ins, and then execute it. I know this is a hard assignment, but you have everything you need to get it done.
Listing 5-9 is my solution to this exercise. When I ran this script on my computer, it inserted 260,000 rows in an average time of 72 seconds—that’s about 3,611 rows per second.
Listing 5-9. Using CURSOR FOR LOOPs to Insert Test Data, workers _cursor_for_loop.ins
001 rem workers_cursor_for_loop.ins
002 rem by Donald J. Bales on 2014-10-20
003 rem Seed the Workers table with the top 100 names
004 rem 100 last x 100 first x 26 middle = 260,000 entries
005
006 set serveroutput on size 1000000;
007
008 declare
009
010 -- This is the cursor for the last names.
011 cursor c_last is
012 select last_name
013 from TOP_100_LAST_NAMES;
014
015 -- This is the cursor for the first names.
016 cursor c_first is
017 select first_name,
018 gender_code
019 from TOP_100_FIRST_NAMES;
020
021 -- This is the cursor for the middle initials.
022 cursor c_middle is
023 select letter
024 from A_THRU_Z;
025
026 -- This is the number of seconds since midnight
027 -- I'll use it to profile my code's performance.
028 n_start number :=
029 to_number(to_char(SYSDATE, 'SSSSS'));
030
031 -- Here, I declare four psuedo-constants to hold the
032 -- ID values from the code tables, rather than look
033 -- them up repeatedly during the insert process.
034 n_G_FEMALE GENDER_TYPES.id%TYPE;
035 n_G_MALE GENDER_TYPES.id%TYPE;
036 n_WT_CONTRACTOR WORKER_TYPES.id%TYPE;
037 n_WT_EMPLOYEE WORKER_TYPES.id%TYPE;
038
039 -- I'll use this to keep track of the number of
040 -- rows inserted.
041 n_inserted number := 0;
042
043 -- Here, I declare a record anchored to the table so
044 -- I can set the column values and then insert using
045 -- the record.
046 r_worker WORKERS%ROWTYPE;
047
048 begin
049 -- Get the ID values for the codes
050 n_G_FEMALE := GENDER_TYPE.get_id('F'),
051 n_G_MALE := GENDER_TYPE.get_id('M'),
052 n_WT_CONTRACTOR := WORKER_TYPE.get_id('C'),
053 n_WT_EMPLOYEE := WORKER_TYPE.get_id('E'),
054
055 -- Loop through the last names
056 for r_last in c_last loop
057
058 -- While looping through the last names,
059 -- loop through the first names
060 for r_first in c_first loop
061
062 -- While looping through the last and first names
063 -- loop through the 26 letters in the English
064 -- Alphabet in order to get middle initials
065 -- As an alternative, I could have used a FOR LOOP:
066 -- for i in ascii('A')..ascii('Z') loop
067 for r_middle in c_middle loop
068
069 -- Initialize the record
070
071 -- Get the PK using the table's package
072 r_worker.id := WORKER.get_id();
073
074 -- Flip flop from contractor to employee and back again
075 if r_worker.worker_type_id = n_WT_CONTRACTOR then
076 r_worker.worker_type_id := n_WT_EMPLOYEE;
077 else
078 r_worker.worker_type_id := n_WT_CONTRACTOR;
079 end if;
080
081 -- Get the External ID using the table's package
082 r_worker.external_id := WORKER.get_external_id();
083
084 -- The first, middle, and last names come from the cursors
085 r_worker.first_name := r_first.first_name;
086 -- r_worker.middle_name := chr(i)||'.';
087 r_worker.middle_name := r_middle.letter||'.';
088 r_worker.last_name := r_last.last_name;
089
090 -- get the name using the table's package
091 r_worker.name := WORKER.get_formatted_name(
092 r_worker.first_name, r_worker.middle_name, r_worker.last_name);
093
094 -- get a random date for a birth date
095 r_worker.birth_date := DATES.random(
096 to_number(to_char(SYSDATE, 'YYYY')) - 65,
097 to_number(to_char(SYSDATE, 'YYYY')) - 18);
098
099 -- selecrt the corresponding ID value
100 if r_first.gender_code = 'F' then
101 r_worker.gender_type_id := n_G_FEMALE;
102 else
103 r_worker.gender_type_id := n_G_MALE;
104 end if;
105
106 -- Insert the row into the database
107 insert into WORKERS values r_worker;
108
109 -- keep track of the number of inserts
110 n_inserted := n_inserted + sql%rowcount;
111 end loop; -- c_middle
112 -- end loop; -- for i
113 commit; -- commit every 26 rows
114
115 end loop; -- c_first
116
117 end loop; -- c_last
118 -- Display the results
119 pl(to_char(n_inserted)||' rows inserted in '||
120 (to_number(to_char(SYSDATE, 'SSSSS')) - n_start)||
121 ' seconds.'),
122 end;
123 /
I won’t elaborate on my solution here, because I’ve commented the code heavily. Notice that I’ve added a variable to hold the start time in seconds, so I can test the performance of this solution against others. You’ll see me profile code this way as we move forward. It’s part of the testing process. The question now is, “Can we make this PL/SQL procedure faster?”
In Oracle8, some additional functionality was added to PL/SQL to improve performance. Let’s look at one of those additions next.
Bulk Collect
In this context, BULK COLLECT is about reducing the number of transitions between the PL/SQL engine and SQL engine in order to improve efficiency and speed of your PL/SQL program.
The idea is simple. Every time you execute SQL from your PL/SQL program, PL/SQL must hand off the SQL statement to the Oracle database’s SQL engine. When the SQL engine is finished, it returns its result to the PL/SQL engine. Flip-flopping back and forth from PL/SQL to SQL and back again takes time.
Since version 8 of Oracle, you can reduce the number of transitions between PL/SQL and SQL by using the BULK COLLECT command. Rather than fetch one row at a time from the SQL engine, you can fetch perhaps 100 at a time into a PL/SQL collection, which can be a PL/SQL table (or array).
Although three types of PL/SQL collections exist, I’m going to cover only the use of associative arrays, or as they were once called, PL/SQL tables. Once you’re comfortable programming in PL/SQL, I recommend you go back and learn about the other two in a good reference (such as PL/SQL Programming by Steven Feuerstein and Bill Pribyl).
Bulk Collect with a Cursor
Since we’ve been working with cursors, let’s continue to talk about them. Listing 5-10 is yet another incarnation of Listing 5-1, the “select the Doe family” assignment. This time, however, it has been coded to use BULK COLLECT.
Listing 5-10. An Example of Using BULK COLLECT with a CURSOR, cursor_bulk_collect_the_doe_family.sql.
01 rem cursor_bulk_collect_the_doe_family.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem An anonymous PL/SQL procedure to select
04 rem the first names for the Doe family from
05 rem the Workers table.
06
07 set serveroutput on size 1000000;
08
09 declare
10
11 cursor c_workers(
12 aiv_last_name in WORKERS.last_name%TYPE) is
13 select first_name
14 from WORKERS
15 where last_name like aiv_last_name||'%'
16 order by id;
17
18 TYPE c_worker_table is table of c_workers%ROWTYPE
19 index by binary_integer;
20
21 t_workers c_worker_table;
22
23 begin
24 open c_workers('DOE'),
25 loop
26 fetch c_workers bulk collect into t_workers limit 2;
27
28 exit when t_workers.count = 0;
29
30 for i in t_workers.first..t_workers.last loop
31 pl(t_workers(i).first_name);
32 end loop;
33 end loop;
34 end;
35 /
The FETCH syntax used in the manual loop in Listing 5-10 is as follows:
FETCH <cursor_name> BULK COLLECT INTO <plsql_table_name> LIMIT <limit>;
where <cursor_name> is the name of an open cursor from which to fetch, and <plsql_table_name> is the name of the associative array in which to fetch the <limit> number of rows.
And the FOR LOOP syntax used in the listing is as follows:
FOR <index> IN <from_index>..<through_index> LOOP
-- Put your PL/SQL code to execute during the loop here.
END LOOP
where <index> is an index value to use in the loop to address PL/SQL table elements, <from_index> is a valid index value to start with, and <through_index> is a valid index value to end with.
There’s nothing like dissecting a program line by line to help explain it, so here it is:
- Lines 11 through 16 declare a cursor to select the first names of a specified family. There’s nothing new here. You’ve seen this time and again.
- Lines 18 and 19 declare an associative array, or PL/SQL table, based on the row type of CURSOR c_workers.
- Line 21 declares a variable of TYPE c_worker_table that will be the target of the FETCH ... BULK COLLECT command.
- Line 24 opens the cursor c_workers.
- Line 25 starts a manual LOOP.
- On line 26, I fetch rows from the database, two rows at a time, into table t_workers. Because I’ve reduce the number of transitions, or context switches, by two, this PL/SQL program will run roughly twice as fast. I specified the number of rows to retrieve with the LIMIT clause at the end of the FETCH ... BULK COLLECT statement. I could have set the limit to 10, 100, 1000, 9999, or whatever. But keep in mind that the limit also determines how much memory will be consumed by holding the rows in memory between each call, so you need to use a reasonable value or bring your database to a screeching halt—it’s your choice.
- On line 28, instead of checking the cursor variable c_workers%notfound, I need to check the PL/SQL table t_workers’ %count variable to see how many rows were inserted into the table from the last FETCH. If t_workers%count is zero, there are no more rows to retrieve from the cursor, so I EXIT the LOOP.
- On line 30, now that the fetched data resides in PL/SQL table t_workers, I use a FOR LOOP to iterate through the entries, from the first, t_worker.first through (..) the last, t_worker.last. In this context, first and last are PL/SQL table variables that tell you the first and last rows in the table, respectively.
- Line 31, inside the FOR LOOP, displays the first names on the screen. However, this time, since the PL/SQL table was based on a cursor’s row type, I need to use the form table name, index, dot, field name—t_workers(i).first_name—in order to address the field.
Using BULK COLLECT with a FETCH statement for a cursor can lead to a significant performance improvement if you set a moderately sized LIMIT. If you specify too large of a LIMIT, you’ll use up memory needed for other database sessions.
Another thing to consider is that using BULK COLLECT requires additional programming. So is the extra memory consumption and additional programming worth the investment? Or will a simple CURSOR FOR LOOP do the job?
In practice, I always start out with a CURSOR FOR LOOP. If I find that a particular PL/SQL program is a performance bottleneck, or needs to run faster for business purposes, I take the extra time to transform the PL/SQL module to use BULK COLLECT. But then I test its performance gains against every other PL/SQL program’s performance loss due to the additional consumption of memory. Yes, I test, test, and test again.
Note Oracle Database 10g introduced an “auto bulk collect” feature. I suggest you investigate this later by reading Oracle’s PL/SQL User’s Guide and Reference for a version of Oracle Database 10g or later.
Bulk Collect with a Select Statement
If you know that the result of your SQL SELECT statement will always be a small number of rows, you can simplify your PL/SQL programming by using BULK COLLECT with a SELECT statement. The result is a SELECT statement that looks a lot like a singleton SELECT, with the difference being that rather than returning only one row at a time, your use of BULK COLLECT allows the statement to return a number of rows at a time.
Listing 5-11 demonstrates the use of BULK COLLECT with a SELECT statement. The difference with this listing compared to Listing 5-10 is that I no longer use an explicit CURSOR.
Listing 5-11. An Example of Using BULK COLLECT with a SELECT statement, bulk_collect_the_doe_family.sql
01 rem bulk_collect_the_doe_family.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem An anonymous PL/SQL procedure to select
04 rem the first names for the Doe family from
05 rem the Workers table.
06
07 set serveroutput on size 1000000;
08
09 declare
10
11 TYPE worker_table is table of WORKERS.first_name%TYPE
12 index by binary_integer;
13
14 t_workers worker_table;
15
16 begin
17 select first_name
18 BULK COLLECT
19 into t_workers
20 from WORKERS
21 where last_name like 'DOE%'
22 order by id;
23
24 for i in t_workers.first..t_workers.last loop
25 pl(t_workers(i));
26 end loop;
27 end;
28 /
The BULK COLLECT syntax used in Listing 5-11 is as follows:
SELECT <column_list>
BULK COLLECT
INTO <plsql_table_name>
FROM <table_name>
WHERE <where_clause>
ORDER BY <order_by_colunmn_list>;
where <column_list> is a single column name from the table or an asterisk (*) to denote all columns from the table (or row), <plsql_table_name> is the name of one or more associative array(s) declared to match the column list’s data types, <table_name> is the name of the table to query, <where_clause> is an appropriate WHERE clause for the query, and <order_by_column_list> is a list of column(s) by which to order the SELECT statement’s results.
Let’s break down Listing 5-11:
- Lines 11 and 12 declare a TYPE based on the first_name column of the WORKERS table.
- Line 14 declares a table for the TYPE worker_table specified on lines 11 and 12.
- On lines 17 through 22, the magic occurs. This is where I’ve used the SELECT BULK COLLECT INTO syntax to load all the resulting rows from the query into the PL/SQL table t_workers in just one context switch!
- Lines 24 through 26 contain a FOR LOOP to iterate through the rows in PL/SQL table t_workers, displaying the results on the screen.
Sweet and simple, from disk to memory in one context switch, this is indeed a powerful syntax. However, it has all the same baggage as its CURSOR-based sibling in the previous section. So use it carefully, if at all.
In practice, I never use this syntax because I can’t guarantee that the tables I’m coding against won’t grow to require an unreasonable amount of memory for the BULK COLLECT. Instead, I play it safe and use the CURSOR BULK COLLECT syntax. But now it’s your choice.
It’s Your Turn to Bulk Collect
Now that you know how to make your code more efficient by using BULK COLLECT, put it to the test by taking your solution to the last exercise and modifying it to use BULK COLLECT. This means you’ll get rid of the three cursors you declared in that solution and replace them with three TYPE and three PL/SQL table declarations. Next, you’ll change your CURSOR FOR LOOPs to FOR LOOPs, as in the following example:
begin
for l in t_last.first..t_last.last loop
for f in t_first.first..t_first.last loop
for m in t_middle.first..t_middle.last loop
-- initialize the variables to be used in the insert statement here.
insert into WORKERS ...
end loop;
end loop;
end loop;
end;
Listing 5-12 is my solution to this exercise. When I ran this script on my computer, it inserted 260,000 rows in an average time of 65 seconds—that’s about 4000 rows per second, for an 11% improvement. The small amount of improvement is understandable here, because while there were 223 context switches saved by using BULK COLLECT, there were still 260,000 context switches due to inserts.
Listing 5-12. Using BULK COLLECT to Improve the Insertion of Test Data, workers_bulk_collect.ins
001 rem workers_bulk_collect.ins
002 rem by Donald J. Bales on 2014-10-20
003 rem Seed the Workers table with the top 100 names
004 rem 100 last x 100 first x 26 middle = 260,000 entries
005
006 set serveroutput on size 1000000;
007
008 declare
009
010 -- Declare a type for a PL/SQL table of last names
011 TYPE last_name_table is table of TOP_100_LAST_NAMES%ROWTYPE
012 index by binary_integer;
013
014 -- Declare a type for a PL/SQL table of first names
015 TYPE first_name_table is table of TOP_100_FIRST_NAMES%ROWTYPE
016 index by binary_integer;
017
018 -- Declare a type for a PL/SQL table of middle initials
019 TYPE middle_name_table is table of A_THRU_Z%ROWTYPE
020 index by binary_integer;
021
022 -- This is the number of seconds since midnight
023 -- I'll use it to profile my code's performance.
024 n_start number :=
025 to_number(to_char(SYSDATE, 'SSSSS'));
026
027 -- Here, I declare four psuedo-constants to hold the
028 -- ID values from the code tables, rather than look
029 -- them up repeatedly during the insert process.
030 n_G_FEMALE GENDER_TYPES.id%TYPE;
031 n_G_MALE GENDER_TYPES.id%TYPE;
032 n_WT_CONTRACTOR WORKER_TYPES.id%TYPE;
033 n_WT_EMPLOYEE WORKER_TYPES.id%TYPE;
034
035 -- I'll use this to keep track of the number of
036 -- rows inserted.
037 n_inserted number := 0;
038
039 -- Here, I declare a record anchored to the table so
040 -- I can set the column values and then insert using
041 -- the record.
042 r_worker WORKERS%ROWTYPE;
043
044 -- Declare the three PL/SQL tables that replace cursors
045 t_first first_name_table;
046 t_middle middle_name_table;
047 t_last last_name_table;
048
049 begin
050 -- Get the ID values for the codes
051 n_G_FEMALE := GENDER_TYPE.get_id('F'),
052 n_G_MALE := GENDER_TYPE.get_id('M'),
053 n_WT_CONTRACTOR := WORKER_TYPE.get_id('C'),
054 n_WT_EMPLOYEE := WORKER_TYPE.get_id('E'),
055
056 -- Bulk collect the tables into the PL/SQL tables
057 select * bulk collect into t_last from TOP_100_LAST_NAMES;
058 select * bulk collect into t_first from TOP_100_FIRST_NAMES;
059 select * bulk collect into t_middle from A_THRU_Z;
060
061 -- Loop through the last names
062 for l in t_last.first..t_last.last loop
063
064 -- While looping through the last names,
065 -- loop through the first names
066 for f in t_first.first..t_first.last loop
067
068 -- While looping through the last and first names
069 -- loop through the 26 letters in the English
070 -- Alphabet in order to get middle initials
071 for m in t_middle.first..t_middle.last loop
072
073 -- Initialize the record
074
075 -- Get the PK using the table's package
076 r_worker.id := WORKER.get_id();
077
078 -- Flip flop from contractor to employee and back again
079 if r_worker.worker_type_id = n_WT_CONTRACTOR then
080 r_worker.worker_type_id := n_WT_EMPLOYEE;
081 else
082 r_worker.worker_type_id := n_WT_CONTRACTOR;
083 end if;
084
085 -- Get the External ID using the table's package
086 r_worker.external_id := WORKER.get_external_id();
087
088 -- The first, middle, and last names come from the cursors
089 r_worker.first_name := t_first(f).first_name;
090 r_worker.middle_name := t_middle(m).letter||'.';
091 r_worker.last_name := t_last(l).last_name;
092
093 -- get the name using the table's package
094 r_worker.name := WORKER.get_formatted_name(
095 r_worker.first_name, r_worker.middle_name, r_worker.last_name);
096
097 -- get a random date for a birth date
098 r_worker.birth_date := DATES.random(
099 to_number(to_char(SYSDATE, 'YYYY')) - 65,
100 to_number(to_char(SYSDATE, 'YYYY')) - 18);
101
102 -- selecrt the corresponding ID value
103 if t_first(f).gender_code = 'F' then
104 r_worker.gender_type_id := n_G_FEMALE;
105 else
106 r_worker.gender_type_id := n_G_MALE;
107 end if;
108
109 -- Insert the row into the database
110 insert into WORKERS values r_worker;
111
112 -- keep track of the number of inserts
113 n_inserted := n_inserted + sql%rowcount;
114 end loop; -- t_middle
115 commit; -- commit every 26 rows
116
117 end loop; -- t_first
118
119 end loop; -- t_last
120 -- Display the results
121 pl(to_char(n_inserted)||' rows inserted in '||
122 (to_number(to_char(SYSDATE, 'SSSSS')) - n_start)||
123 ' seconds.'),
124 end;
125 /
Once again, I won’t elaborate on my solution here because I’ve commented the code heavily. The question remains, “Can we make this PL/SQL procedure faster?”
Well, we’ve already used BULK COLLECT. What else is in PL/SQL’s bag of tricks?
For the sake of completeness, I’m going to mention the FORALL statement here. It’s kind of like the inverse of the BULK COLLECT statement. Given that you have a populated collection, like an associative array, you can bulk execute the same SQL statement for every entry or for selected entries in your collection(s).
For example, if you have 26 entries in your PL/SQL table, you can write a FORALL statement that will execute the same SQL statement 26 times, once for each row in your PL/SQL table. The assumption here is that you’ll use the values from your PL/SQL table in each SQL statement.
The problem I have with FORALL is that the data for a collection usually comes from a table in the database in the first place. If that’s the case, then a complex SQL statement can do everything a FORALL statement can do, with one context switch, just like FORALL, but using less memory. So why does FORALL exist? Frankly, I don’t know. Perhaps it’s syntactic sugar for PL/SQL programmers that have weak SQL skills. Or maybe I’ve just never run across a need for it. That’s always possible. However, in practice, I never use it. I always seem to find a better solution using a complex SQL statement.
Let me show you what I mean. Listing 5-13 is a rewrite of the “populate the Worker table” assignment, where I use FORALL to bind 26 SQL statements per context switch. I could have rearranged my code some more so I could do 100 at a time, but the improvement from doing so is not significant.
Listing 5-13. Using FORALL to Improve the Insertion of Test Data, workers_forall.ins
001 rem workers_forall.ins
002 rem by Donald J. Bales on 2014-10-20
003 rem Seed the Workers table with the top 100 names
004 rem 100 last x 100 first x 26 middle = 260,000 entries
005
006 set serveroutput on size 1000000;
007
008 declare
009
010 -- Declare a type for a PL/SQL table of last names
011 TYPE last_name_table is table of TOP_100_LAST_NAMES%ROWTYPE
012 index by binary_integer;
013
014 -- Declare a type for a PL/SQL table of first names
015 TYPE first_name_table is table of TOP_100_FIRST_NAMES%ROWTYPE
016 index by binary_integer;
017
018 -- Declare a type for a PL/SQL table of middle initials
019 TYPE middle_name_table is table of A_THRU_Z%ROWTYPE
020 index by binary_integer;
021
022 -- Declare a type for a PL/SQL table of workers
023 TYPE worker_table is table of WORKERS%ROWTYPE
024 index by binary_integer;
025
026 -- This is the number of seconds since midnight
027 -- I'll use it to profile my code's performance.
028 n_start number :=
029 to_number(to_char(SYSDATE, 'SSSSS'));
030
031 -- Here, I declare four psuedo-constants to hold the
032 -- ID values from the code tables, rather than look
033 -- them up repeatedly during the insert process.
034 n_G_FEMALE GENDER_TYPES.id%TYPE;
035 n_G_MALE GENDER_TYPES.id%TYPE;
036 n_WT_CONTRACTOR WORKER_TYPES.id%TYPE;
037 n_WT_EMPLOYEE WORKER_TYPES.id%TYPE;
038
039 -- I'll use this to keep track of the number of
040 -- rows inserted.
041 n_inserted number := 0;
042
043 -- Declare the four PL/SQL tables that replace cursors
044 -- and the worker record
045 t_first first_name_table;
046 t_middle middle_name_table;
047 t_last last_name_table;
048 t_workers worker_table;
049
050 begin
051 -- Get the ID values for the codes
052 n_G_FEMALE := GENDER_TYPE.get_id('F'),
053 n_G_MALE := GENDER_TYPE.get_id('M'),
054 n_WT_CONTRACTOR := WORKER_TYPE.get_id('C'),
055 n_WT_EMPLOYEE := WORKER_TYPE.get_id('E'),
056
057 -- Bulk collect the tables into the PL/SQL tables
058 select * bulk collect into t_last from TOP_100_LAST_NAMES;
059 select * bulk collect into t_first from TOP_100_FIRST_NAMES;
060 select * bulk collect into t_middle from A_THRU_Z;
061
062 -- Loop through the last names
063 for l in t_last.first..t_last.last loop
064
065 -- While looping through the last names,
066 -- loop through the first names
067 for f in t_first.first..t_first.last loop
068
069 -- While looping through the last and first names
070 -- loop through the 26 letters in the English
071 -- Alphabet in order to get middle initials
072 for m in t_middle.first..t_middle.last loop
073
074 -- Initialize the table's rows
075
076 -- Get the PK using the table's package
077 t_workers(m).id := WORKER.get_id();
078
079 -- Flip flop from contractor to employee and back again
080 if t_workers(m).worker_type_id = n_WT_CONTRACTOR then
081 t_workers(m).worker_type_id := n_WT_EMPLOYEE;
082 else
083 t_workers(m).worker_type_id := n_WT_CONTRACTOR;
084 end if;
085
086 -- Get the External ID using the table's package
087 t_workers(m).external_id := WORKER.get_external_id();
088
089 -- The first, middle, and last names come from the cursors
090 t_workers(m).first_name := t_first(f).first_name;
091 t_workers(m).middle_name := t_middle(m).letter||'.';
092 t_workers(m).last_name := t_last(l).last_name;
093
094 -- get the name using the table's package
095 t_workers(m).name := WORKER.get_formatted_name(
096 t_workers(m).first_name,
097 t_workers(m).middle_name,
098 t_workers(m).last_name);
099
100 -- get a random date for a birth date
101 t_workers(m).birth_date := DATES.random(
102 to_number(to_char(SYSDATE, 'YYYY')) - 65,
103 to_number(to_char(SYSDATE, 'YYYY')) - 18);
104
105 -- select the corresponding ID value
106 if t_first(f).gender_code = 'F' then
107 t_workers(m).gender_type_id := n_G_FEMALE;
108 else
109 t_workers(m).gender_type_id := n_G_MALE;
110 end if;
111
112 end loop; -- t_middle
113
114 -- Now bulk bind the 26 insert statements
115 forall i in t_workers.first..t_workers.last
116 insert into WORKERS values t_workers(i);
117
118 n_inserted := n_inserted + sql%rowcount;
119
120 end loop; -- t_first
121
122 end loop; -- t_last
123 commit;
124 -- Display the results
125 pl(to_char(n_inserted)||' rows inserted in '||
126 (to_number(to_char(SYSDATE, 'SSSSS')) - n_start)||
127 ' seconds.'),
128 end;
129 /
In Listing 5-13, the magic happens on lines 74 through 110, where I populate a PL/SQL table’s records with values, and then, on lines 115 and 116, where I bulk bind the insert statements, so I have only one context switch for each set of 26 records. Using FORALL, my program now inserts 260,000 rows in an average time of 38 seconds. That’s about 6,842 rows per second, for a 71% improvement. That’s great, right? Well, what if I just use SQL?
Listing 5-14 is the “populate the Worker table” assignment, where I use a single SQL statement. How does it fare?
Listing 5-14. Using SQL to Improve the Insertion of Test Data, workers.ins
01 rem workers.ins
02 rem by Donald J. Bales on 2014-10-20
03 rem Seed the Worker table with the top 100 names
04 rem 100 last x 100 first x 26 middle = 260,000 entries
05
06 set serveroutput on size 1000000;
07
08 declare
09
10 -- This is the number of seconds since midnight
11 -- I'll use it to profile my code's performance.
12 n_start number :=
13 to_number(to_char(SYSDATE, 'SSSSS'));
14
15 -- Here, I declare four psuedo-constants to hold the
16 -- ID values from the code tables, rather than look
17 -- them up repeatedly during the insert process.
18 n_G_FEMALE GENDER_TYPES.id%TYPE;
19 n_G_MALE GENDER_TYPES.id%TYPE;
20 n_WT_CONTRACTOR WORKER_TYPES.id%TYPE;
21 n_WT_EMPLOYEE WORKER_TYPES.id%TYPE;
22
23 -- I'll use this to keep track of the number of
24 -- rows inserted.
25 n_inserted number := 0;
26
27 begin
28 -- Get the ID values for the codes
29 n_G_FEMALE := GENDER_TYPE.get_id('F'),
30 n_G_MALE := GENDER_TYPE.get_id('M'),
31 n_WT_CONTRACTOR := WORKER_TYPE.get_id('C'),
32 n_WT_EMPLOYEE := WORKER_TYPE.get_id('E'),
33
34 -- Use an INSERT INTO SELECT SQL statement
35 insert into WORKERS (
36 id,
37 worker_type_id,
38 external_id,
39 first_name,
40 middle_name,
41 last_name,
42 name,
43 birth_date,
44 gender_type_id)
45 select WORKERS_ID.nextval,
46 decode(mod(WORKERS_ID.currval, 2),
47 0, n_WT_EMPLOYEE, n_WT_CONTRACTOR),
48 lpad(to_char(EXTERNAL_ID_SEQ.nextval), 9, '0'),
49 first_name,
50 letter||'.',
51 last_name,
52 WORKER.get_formatted_name(
53 first_name, letter||'.', last_name),
54 DATES.random(
55 to_number(to_char(SYSDATE, 'YYYY')) - 65,
56 to_number(to_char(SYSDATE, 'YYYY')) - 18),
57 decode(gender_code, 'F', n_G_FEMALE, n_G_MALE)
58 from TOP_100_LAST_NAMES,
59 TOP_100_FIRST_NAMES,
60 A_THRU_Z;
61
62 n_inserted := n_inserted + sql%rowcount;
63
64 commit;
65
66 pl(to_char(n_inserted)||' rows inserted in '||
67 (to_number(to_char(SYSDATE, 'SSSSS')) - n_start)||
68 ' seconds.'),
69 end;
70 /
In Listing 5-14, a single INSERT ... SELECT statement, albeit a complex one, does almost all the work done by lines and lines of PL/SQL. But how does it perform? It inserts 260,000 rows in an average time of 21 seconds. That’s about 12,381 rows per second, for a 200% improvement. You can’t get any faster than using plain-old SQL. So what’s the moral of this story? Let me hear you say it. Know thy SQL!
When you’re comfortable with PL/SQL and are up to challenging yourself, pull out a good PL/SQL reference and read up on FORALL. For now, I think you’ll do better by improving your SQL skills instead.
Summary
In this chapter, you’ve gone from curs(or)ing, to fetching, to bulk collecting, to bulk binding, and then back to SQL again. CURSORs allow you to FETCH multiple rows from a SQL SELECT statement. You can FETCH rows manually or automatically, using a CURSOR FOR LOOP. You can use BULK COLLECT to improve the efficiency of loading an array with values from the database, and you can use FORALL to improve the efficiency of executing SQL statements with values from an array. But most important, you probably realize by now that when I stated in Chapter 1 that “knowing SQL is a prerequisite to learning PL/SQL,” I wasn’t joking.
All too often, I see poorly performing PL/SQL programs that are bloated with PL/SQL code that tries to do what SQL can simply do better. After all, it is the Procedural Language extension to SQL. PL/SQL’s job is to handle the procedural tasks, while SQL’s job is to handle manipulating data. Keep that in mind whenever you write a PL/SQL program.
Up to this point, although you’ve been keeping functions and procedures in a package for each table, you’ve still been acting as though these behaviors were something apart from the data they work with. In fact, in the real world, the opposite is true. Things in the real world don’t keep their behavior or actions someplace away from themselves; they are part of a whole, and that’s what you are going to look at next. You’ll explore storing the behavior with the attributes, or as I like to think of it: object-relational technology.