The Beauty Is in the Details

Pay attention to the details in conditions, loops, and declarations, and create better code.

July/August 2004

Some of my past articles in Oracle Magazine have offered a variety of "big picture" best practices to help you write code properly. This article takes a different approach. It invites you to pay attention to seemingly minor details in the way you write IF statements, loops, and declarations.

I have found it best to think of a program as a kind of fractal construct, at least when it comes to code quality. A definition of fractal is "a geometric pattern that is repeated at ever smaller scales..." according to www.dictionary.com.

An application has an overall shape: its architecture, database design, and major subsystems. Every program within an application has an overall shape: its main algorithm, the high-level requirements it is supposed to meet, and so on. And when you open up that program, you find smaller shapes: the declaration section, the executable section, and so on. And then within those sections, you can find even smaller shapes: IF statements, loops, and assignments.

If you want to write high-quality software, you need to pay attention to the quality of every level or shape within your applications. And if the lowest-level constructs are written poorly and are hard to understand, that deficiency propagates itself to the highest levels and shapes of logic in a system.

Very few programs are simple enough to present a single, linear stream of logic. Instead, you encounter many forks in the road, conditional logic that determines which code is executed, based on the results of a Boolean (logical) expression. Here are some recommendations for making that conditional logic transparent and easy to follow: IF, ELSIF, or CASE. Consider the following:

PROCEDURE run_task (task_number_in
  IN INTEGER)
IS
BEGIN
   IF task_number_in = 1
   THEN
      rework_percentages;
   END IF;
   IF task_number_in = 2
   THEN
      calculate_margins;
   END IF;
    IF task_number_in = 3
    THEN
      update_records;
   END IF;
END run_task;

The task number determines which task is to be run (each task is a stored procedure). There is a problem, however. The IF conditions are mutually exclusive; if any one of them evaluates to TRUE, all the others will evaluate to FALSE. Yet run_task will evaluate every one of the IF statements. That is unnecessary and wasteful.

If your conditions are mutually exclusive, you should switch to the ELSIF construct within a single IF statement or use the CASE statement. The following offers a rewrite of that same logic, using ELSIF s:

PROCEDURE run_task (task_number_in
  IN INTEGER)
IS
BEGIN
   IF task_number_in = 1
   THEN
      rework_percentages;
   ELSIF task_number_in = 2
   THEN
      calculate_margins;
   ELSIF task_number_in = 3
   THEN
      update_records;
   END IF;
END run_task;

Now, as soon as one condition evaluates to TRUE, all others will be ignored. In Oracle9i and later, you have an even better option: the CASE statement. The following shows how you can express the same logic in a much more readable and compact format by using CASE :

PROCEDURE run_task (task_number_in
  IN INTEGER)
IS
BEGIN
   CASE task_number_in
     WHEN 1 THEN rework_percentages;
     WHEN 2 THEN calculate_margins;
     WHEN 3 THEN update_records;
   END CASE;
END run_task;

Programs, not puzzles. Lots of programmers send me examples of their code, either to ask for help or to show me something of which they are proud. I have discovered from reviewing these many gems that some developers like to demonstrate just how clever they are. This cleverness often results from a desire to show just how much functionality they can pack into a few lines of code. Clever is, unfortunately, a minus, not a plus, in code. You should strive to write code that is transparent, not a tightly knotted puzzle to be worked out by those who come after you.

Conditional logic is the place in code where the most-tangled knots appear. It is very important to follow some guidelines when it comes to IF statements:

• Expressions are often more readable and understandable when they are stated in a positive form. Consequently, you are probably better off avoiding the NOT operator in conditional expressions whenever possible. Listing 1 offers a side-by-side comparison of two IF statements. The left column uses NOT and is fairly difficult to see clearly. The right column introduces a single level of nesting and segments the conditions, making it easier to read.

• Keep the number of levels of nested IF s to a minimum: no more than three and preferably just two. You can "hide" additional levels of nesting behind local procedures. Leave it up to the programmers to look inside that procedure when and if they need to see that additional detail (and conditional nesting). Listing 2 shows such a local procedure.

Most PL/SQL programs will contain one or more loops. Loops (also known as iterators) are among the most sensitive areas of your code, in terms of both performance and bugs; the code inside the loop runs repeatedly, magnifying any issues.

PL/SQL offers several loop constructs.

The simple (also called infinite) loop:

LOOP
   <body of loop>
END LOOP;

The WHILE loop:

WHILE (condition)
LOOP
   <body of loop>
END LOOP;

The numeric FOR loop:

FOR <index> IN <low> .. <high>
LOOP
   <body of loop>
END LOOP;

The cursor FOR loop:

FOR <record> IN <cursor>
LOOP
   <body of loop>
END LOOP;

Generally, you should use a simple loop if you always want the body of the loop to execute at least once. Use a WHILE loop if you want to check the condition before executing the body the first time. Use a FOR loop when you plan to iterate through every value (integer or record) specified by the IN clause.

Just about every best-practice recommendation I offer regarding loops can be expressed in a simple maxim:

One way in, one way out (OWI-OWO).

This principle has been around for decades and is promoted in structured programming methodologies. It is well suited to PL/SQL in general and loops in particular. OWI-OWO means, generally, that there should be just one way to get into a loop (or start that loop) and that there should be a single way out of the loop—the termination of loop execution.

OWI-OWO is very important, because the alternative makes your code very difficult to understand, trace, debug, or maintain. Consider the code in Listing 3. This procedure accepts a collection of titles and searches through that list for a match against the value in title_in . I have used a FOR loop to look through each row in the collection. But on line 13, if I find a match, I immediately return out of the function. And on line 16, if the collection index value is NULL , I terminate the loop. After leaving the loop, my program returns NULL , indicating that no match was found.

There is just one way into the function (the header) and one way into the loop (the FOR statement). There are, however, three ways out of the loop (read all the way through the collection, find a match and RETURN , and EXIT when index is NULL ) and three ways out of the function ( RETURN indx , inside the loop; RETURN NULL , at the end of the executable section; and RETURN NULL , in the exception section).

Now suppose the function is returning the wrong value. To debug this program, I need to examine three exit points and determine which returned this bad value and why. Listing 4 offers a rewrite of this program that is much cleaner in structure and easier to manage. Note that it now uses a WHILE loop, because I may want to stop the loop prematurely. In fact, the loop will terminate if I have run out of values in my collection or have found a match (setting the return_value to a non- NULL index value).

The general OWI-OWO recommendation leads to the following specific guidelines to keep in mind when you write loops:

• Treat the FOR loop as a promise you are making to anyone reading or running your code: "I will allow that FOR loop to run to completion for each integer or record specified by the IN clause. I will not terminate it early or conditionally." Consequently, a FOR loop should never contain an EXIT or RETURN statement.

• The WHILE loop should terminate only according to the Boolean expression that comes directly after the WHILE keyword. WHILE loops should also not rely on EXIT or RETURN to stop the loop. Finally, when you want to avoid even one execution of the loop body, use the WHILE loop.

• The simple, or (potentially) infinite, loop should use the EXIT or EXIT WHEN statement to terminate loop execution. You should never have a RETURN statement within a simple loop; you should also try to have just a single EXIT statement. And if you ever see a simple loop that doesn't contain even one EXIT , you are looking at a problem (an infinite loop) waiting to happen. Finally, if you want the loop body to always execute at least once, use a simple loop and put your EXIT statement at the bottom of the loop body (this is the PL/SQL equivalent of REPEAT UNTIL ).

Sometimes you will find yourself moving from one type of loop to another as you fill out the implementation. For example, because the WHILE loop performs its condition check up front, the variables in the boundary expression must be initialized. The code for initializing the variables is often the same code needed to move to the next iteration in the WHILE loop. This redundancy creates a challenge in both debugging and maintaining the code: How do you remember to look at and update both code instances?

Consider this scenario: I need to write a procedure for calculating overdue charges for books; the maximum fine to be charged is $10, and I will stop processing when no books are overdue for a given date. My first attempt, built around a WHILE loop, is shown in Listing 5.

As is readily apparent in Listing 5, I duplicate the assignments of values to l_overdue_count (see lines 6-9 and 17-20). I would be far better off rewriting this code as shown in Listing 6, where I assign a value to the l_overdue_count variable just once (see lines 16-19).

If you find yourself writing and running the same code before the WHILE loop and at the end of the WHILE loop body, consider switching to a simple loop.

There are many types of elements we can declare in our programs. Often the declaration section is very long. The most important thing to keep in mind with this section is to give it as much structure and consistency as possible. You should, of course, follow clear naming conventions. I won't go into the details of such naming conventions in this article, but you will probably want to come up with a way to reflect the scope of the variables and the datatype.

Beyond coming up with good names, the other main challenge is to decide on the order in which variables are declared. You have many options, including:

• Alphabetical: List the variables according to their names, as in

  DECLARE
     l_count PLS_INTEGER;
     l_overdue BOOLEAN;
     l_title book.title%TYPE;

• In order of usage: Declare the variables in the order in which they are used in the executable section, as in

  CREATE OR REPLACE FUNCTION
        row_for_isbn (isbn_in IN book.isbn%TYPE)
     RETURN book%ROWTYPE
  IS
     l_isbn book.isbn%TYPE;
  -- Local copy of IN parameter
     return_value book%ROWTYPE;
  -- Value returned by function
  BEGIN

• By type: Group all the variables of a particular type, alphabetically, as in

  DECLARE
     l_count PLS_INTEGER;
     l_end PLS_INTEGER;
     l_start PLS_INTEGER;
     l_author book.author%TYPE;
          l_extended_title VARCHAR2(500);
          l_title book.title%TYPE;
          l_bestseller BOOLEAN;
          l_overdue BOOLEAN;

When you decide on conventions and standards, you should always keep the objectives in mind: to make it easier to read, maintain, and understand the code. From this perspective, I prefer the order-of-usage approach when declaring variables. With this approach, the declaration section follows the flow of the program itself. As I read through the executable section, I can easily relate it back to the relevant declarations. Best of all, if and when I decide to create local modules to hide some of the details of my program, I can quickly identify which declarations should also be moved into those local modules.

The danger of exploring the small details of coding is that you can get obsessed with every line of code you write, thereby greatly reducing productivity. In general, it is not worth obsessing over code. On the other hand, you should code with deliberation and intent. There is never any justification for being haphazard in the way you structure your software. You should have a reason for the way you write each line of code, even if you don't dwell on that reason.

Internalize the underlying principles that motivate your specific choices, and you will be able to achieve a high quality of code at a very nice typing rate.