Database, SQL and PL/SQL

On Continuing and Indexing

Best practices for PL/SQL in Oracle Database 11g and multilevel, string-indexed collections

By Steven Feuerstein Oracle ACE Director

November/December 2007

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
   ... lots and lots of convoluted code
   FOR index IN 1 .. my_collection.COUNT
      ... hard-to-understand logic here 
      ... more of the same here    

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:

  • Adding an IF statement

  • Using a GOTO statement

  • In Oracle Database 11g, taking advantage of the new CONTINUE statement

Here's what the loop body might look like with an IF statement:

   ... lots and lots of convoluted code
   FOR index IN 1 .. my_collection.COUNT
      ... hard-to-understand logic here 
      IF new_condition
         ... new code here
         ... more of the same here
      END IF;

Here's the approach with GOTO:

   ... lots and lots of convoluted code
   FOR index IN 1 .. my_collection.COUNT
      ... hard-to-understand logic here 
      IF new_condition
         ... new code here
         GOTO end_of_loop;
      END IF;
      ... more of the same here
      NULL; -- Placeholder

And, finally, here's the approach with CONTINUE (new to Oracle Database 11g):

   ... lots and lots of convoluted code
   FOR index IN 1 .. my_collection.COUNT
      ... hard-to-understand logic here 
      IF new_condition
         ... new code here
      END IF;
      ... more of the same here

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:

   ... lots and lots of convoluted code
   FOR index IN 1 .. my_collection.COUNT
      ... 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

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:

   <<outer_loop >>
   FOR o_index IN 1 .. my_collection.COUNT
      FOR i_index 
            IN your_collection.FIRST .. 
         ... 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;
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';
 6        PROCEDURE reset_counts;
 8        PROCEDURE song_requested (title_in IN wora_songs.title%TYPE);
10        PROCEDURE song_played (title_in IN wora_songs.title%TYPE);
12        FUNCTION song_requested_count (title_in 
13        IN wora_songs.title%TYPE)
14           RETURN PLS_INTEGER;
16        FUNCTION folk_requested_count
17           RETURN PLS_INTEGER;
18    END wora_manager;

Summarizing the wora_manager package specification, in Listing 1:

  • Lines 3 and 4: I declare constants to avoid multiple instances of hard-coded literals.

  • Line 6: I provide a program to reset counts as needed.

  • Lines 8-10: The song_requested and song_played procedures record the fact that a particular song (by title) was requested or played.

  • Lines 12-17: I can retrieve the number of times a particular song was requested or songs in the folk category were requested.

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

 2        wora_manager.song_requested (
 3          'If I were a rich man');
 5        wora_manager.song_requested ('Peace train');
 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:

  • How many songs were requested/played within a category

  • How many times a song was requested/played

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

 2     IS
 3        TYPE counts_rt IS RECORD (
 4           requested_count   PLS_INTEGER
 5         , played_count      PLS_INTEGER
 6        );
 8        TYPE song_counts_tt IS TABLE OF counts_rt
 9           INDEX BY wora_songs.title%TYPE;
11        TYPE by_category_tt IS TABLE OF song_counts_tt
12           INDEX BY wora_songs.CATEGORY%TYPE;
14        g_song_data   by_category_tt;

The following are key lines in the wora_manager package body (Listing 3):

  • Lines 3-6: a record type to hold the two types of counts—requested and played.

  • Lines 8 and 9: a collection type (PL/SQL's version of an array) in which each element of the collection is one of those records, and the index into the collection is a string—the title of the song.

  • Lines 11-13: a collection type in which each element of the collection is another collection, of those song count records, and the index into the collection is a string—the category (either folk or rock).

  • Line 14: a variable based on the "collection of collections" type. This variable will hold all the count information.

Listing 4 shows the implementation of the song_requested procedure.

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

 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)
 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;
11         THEN
12              RETURN 0;
13     END song_requested_count;
Next Steps

READ more Feuerstein
Best Practice PL/SQL
PL/SQL in Oracle Magazine
Oracle PL/SQL Best Practices 2nd Edition
Oracle PL/SQL Programming

READ Oracle Database 11g PL/SQL documentation
Oracle Database PL/SQL Language Reference
Oracle Database PL/SQL Packages and Types Reference
"What's New in PL/SQL?"

Oracle Database 11g
script for creating wora_songs table



Photography by Dmitri Popov, Unsplash