DEVELOPER: PL/SQL Practices
On Continuing and IndexingBy Steven Feuerstein
Best practices for PL/SQL in Oracle Database 11g and multilevel, string-indexed collections
I have been assigned one of those big blobs of spaghetti code to maintain, and in particular I have to make changes to a very complicated loop. I want to be able to make a "surgical strike"—put the new rules in place and then bypass the rest of the logic in the loop body with the minimum-possible fuss. What's the best way to do this?
Isn't it painful to make changes to an existing program that is packed full of contorted logic ("spaghetti code")? And isn't it positively terrifying to make those changes when you don't have a regression test for the program that you can run afterward to ensure that no bugs were inadvertently introduced?
Yet that is what we are often called upon to do.
When faced with this situation, the smart thing to do is to make the smallest, most isolated change possible, thereby minimizing the ripple effect.
It sounds to me like you have a situation along these lines:
PROCEDURE someone_elses_mess /* || Author: Long-Gone Consultant || Maintained by: Terrified Employee */ IS BEGIN ... lots and lots of convoluted code FOR index IN 1 .. my_collection.COUNT LOOP ... hard-to-understand logic here ... more of the same here END LOOP; END;
and you need to add some code between "hard-to-understand logic here" and "more of the same here." If a certain condition is met, you want to execute some new code and then skip over the rest of the loop body and move on to the next iteration.
You can accomplish this in a few ways:
Here's what the loop body might look like with an IF statement:
BEGIN ... lots and lots of convoluted code FOR index IN 1 .. my_collection.COUNT LOOP ... hard-to-understand logic here IF new_condition THEN ... new code here ELSE ... more of the same here END IF; END LOOP; END;
Here's the approach with GOTO:
BEGIN ... lots and lots of convoluted code FOR index IN 1 .. my_collection.COUNT LOOP ... hard-to-understand logic here IF new_condition THEN ... new code here GOTO end_of_loop; END IF; ... more of the same here <<end_of_loop>> NULL; -- Placeholder END LOOP; END;
And, finally, here's the approach with CONTINUE (new to Oracle Database 11g):
BEGIN ... lots and lots of convoluted code FOR index IN 1 .. my_collection.COUNT LOOP ... hard-to-understand logic here IF new_condition THEN ... new code here CONTINUE; END IF; ... more of the same here END LOOP; END;
Now, with code this simple, all three of these approaches look reasonable and get the job done. But if you are dealing with an extremely complex, convoluted program, the IF statement gets tricky. You have to make sure you set up the ELSE clause properly and enclose the correct logic. Which means that you have to find the END LOOP statement for this loop, which could be hundreds of lines later in the program, with many other END LOOPs in between.
The GOTO allows you to simply branch to the end of the loop, but, again, you must find the end of that loop and then add both the label and the placeholder "NULL;" statement so that the GOTO has someplace executable to go .
No, the best solution to this problem—available when you upgrade to Oracle Database 11g—is to simply tell the PL/SQL runtime that you want to continue with the loop execution, skipping the rest of the body for this iteration. Clean, simple, and declarative.
And, by the way, just as with the EXIT statement, you can use CONTINUE in a WHEN clause and also specify an END label (helpful with nested loops).
Here is an example of CONTINUE WHEN:
BEGIN ... lots and lots of convoluted code FOR index IN 1 .. my_collection.COUNT LOOP ... hard-to-understand logic here /* I want to execute this new logic and then "escape." */ ... new code here CONTINUE WHEN new_condition; ... more of the same here END LOOP; END;
The following block shows how you can skip not only the rest of the inner loop but also the outer loop by specifying a label with CONTINUE:
BEGIN <<outer_loop >> FOR o_index IN 1 .. my_collection.COUNT LOOP <<inner_loop>> FOR i_index IN your_collection.FIRST .. your_collection.LAST LOOP ... lots of code /* Skip the rest of this and the outer loop if condition is met. */ CONTINUE outer_loop WHEN condition_is_met; ... more inner loop logic END LOOP inner_loop; ... more outer loop logic END LOOP outer_loop; END;
How Do I Track My Songs?
I work for a radio station (call it WORA—not the real name), and I need to write a program that keeps track of how many times a song is requested and played within a given period and also track the count of songs in one of our two categories: folk and rock (I am simplifying things for the question). Although the list of available songs is stored in a database table, this tracking information is not stored in the database; it is active only during the current session. I see how I could write the program by creating a few database tables and writing a bunch of SQL, but I wonder if there might not be an easier way.
There is definitely a much easier way: use multilevel, string-indexed collections!
As you will see, although the syntax for multilevel, string-indexed collections can be a little bit tricky at first, once you are up to speed and as long as you are careful about how you define these structures, they can make your life very, very easy.
Now, when building code as you describe in your question, you should start by describing the functionality you need in the package specification. Listing 1, for example, is the specification that I believe corresponds to your requirements (much simplified, I am sure, from the reality at the radio station and based on a table called wora_songs).
Code Listing 1: Package specification for wora_manager
SQL> PACKAGE wora_manager 2 IS 3 c_folk CONSTANT CHAR (4) := 'FOLK'; 4 c_rock CONSTANT CHAR (4) := 'ROCK'; 5 6 PROCEDURE reset_counts; 7 8 PROCEDURE song_requested (title_in IN wora_songs.title%TYPE); 9 10 PROCEDURE song_played (title_in IN wora_songs.title%TYPE); 11 12 FUNCTION song_requested_count (title_in 13 IN wora_songs.title%TYPE) 14 RETURN PLS_INTEGER; 15 16 FUNCTION folk_requested_count 17 RETURN PLS_INTEGER; 18 END wora_manager;
Summarizing the wora_manager package specification, in Listing 1:
Yes, I know—you need additional functions for rock songs and "number of times a song was played," but I have limited space in this column, so you will have to add those. I also will not include in this column code that is not germane to the demonstration of the use of the collections, such as the inserts into the wora_songs table and a function to look up the category for a song title. You can, however, view all of this code in the download file .
Listing 2 shows an example that uses the wora_manager package.
Code Listing 2: Using the wora_manager package
SQL> BEGIN 2 wora_manager.song_requested ( 3 'If I were a rich man'); 4 5 wora_manager.song_requested ('Peace train'); 6 7 DBMS_OUTPUT.PUT_LINE ( 8 wora_manager.song_requested_count ( 9 'If I were a rich man') 10 ); 11 wora_manager.song_played ('Peace train'); 12 END;
Once the package specification is defined and compiled, you should set up the tests for these programs—before you start writing the package body. With my tests in place, I can move on to the implementation. Let's recap the data I need to keep track of:
There are many ways to implement data structures to store this data. Listing 3 shows the approach I took for the wora_manager package body.
Code Listing 3: Package body for wora_manager
SQL> CREATE OR REPLACE PACKAGE BODY wora_manager 2 IS 3 TYPE counts_rt IS RECORD ( 4 requested_count PLS_INTEGER 5 , played_count PLS_INTEGER 6 ); 7 8 TYPE song_counts_tt IS TABLE OF counts_rt 9 INDEX BY wora_songs.title%TYPE; 10 11 TYPE by_category_tt IS TABLE OF song_counts_tt 12 INDEX BY wora_songs.CATEGORY%TYPE; 13 14 g_song_data by_category_tt;
The following are key lines in the wora_manager package body (Listing 3):
Code Listing 4: Song_requested procedure
SQL> PROCEDURE song_requested (title_in IN VARCHAR2) 2 IS 3 l_category wora_songs.CATEGORY%TYPE := 4 category_for_title (title_in); 5 BEGIN 6 g_song_data (l_category) (title_in).requested_count := 7 g_song_data (l_category) (title_in).requested_count + 1; 8 EXCEPTION 9 WHEN NO_DATA_FOUND 10 THEN 11 g_song_data (l_category) (title_in).requested_count := 1; 12 END song_requested;
In the song_requested procedure, the user passes in a song title, so I call a function to retrieve the category for that title. These two pieces of information (category and title) are the two index values for my g_song_data collection of collections.
As you can see in line 6 of Listing 4, the syntax I use to identify the record that holds the count information for this song is
g_song_data (l_category) (title_in)
In other words, g_song_data (l_ category) takes me to the element in the collection of collections for that category (there are at most two elements in this "outer" collection, because I am working only with folk and rock songs). Then I go after the counts for a particular song by using its title as the index in the "inner" collection.
I then increment the requested_count by 1. If this is the first time the song is requested, Oracle Database will raise the NO_DATA_FOUND exception, because I tried to look at an element for an index that was not defined. So I trap that exception and set the count to 1.
To make these steps a bit clearer, consider the anonymous block in Listing 5.
Code Listing 5: Anonymous block
SQL> DECLARE 2 l_songs_in_category song_counts_tt; 3 l_song_counts counts_rt; 4 BEGIN 5 l_songs_in_category := g_song_data ('FOLK'); 6 l_song_counts := 7 l_songs_in_category (If I had a rocket launcher'); 8 DBMS_OUTPUT.put_line (l_song_counts.requested_count); 9 END;
I have broken out the intermediate data structures. I declare a collection of count records (l_songs_in_category) and a record of counts (l_song_counts). In Listing 5, line 5, I get all the song counts for folk songs. In lines 6 and 7, I get the counts (a record) for one song. In line 8, I display one field of the record.
I am sure that if you are new to collections and multilevel collections, this can take a little getting used to. Yet, consider how much I am able to do with such a small amount of code. I simply provide the string name (title or category) as the index value, and PL/SQL automatically sorts out all the information for me and keeps track of it all very neatly.
The logic for retrieving the count for a given song is also very simple, as shown in Listing 6.
Code Listing 6: song_requested_count function
SQL> FUNCTION song_requested_count (title_in IN VARCHAR2) 2 RETURN PLS_INTEGER 3 IS 4 l_category wora_songs.CATEGORY%TYPE := 5 category_for_title (title_in); 6 BEGIN 7 RETURN g_song_data 8 (l_category) (title_in).requested_count; 9 EXCEPTION 10 WHEN NO_DATA_FOUND 11 THEN 12 RETURN 0; 13 END song_requested_count;
Steven Feuerstein (email@example.com) is Quest Software's PL/SQL evangelist. He has published 10 books on Oracle's programming language, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media). Feuerstein's self-appointed mission in life these days is to improve the quality and quantity of PL/SQL code testing.