Implementing DECODE for PL/SQL

By Steve Feuerstein
OrReview, January/February 1997

Steve reminds us all just how useful DECODE is and how nice it would be to be able to use DECODE in PL/SQL programs.

In the Fall issue, Curtis Copley presented an extensive architecture for using DECODE to handle conditional logic inside a SQL statement. I was struck by the article for two reasons. First, these days, I recommend that developers use PL/SQL functions inside SQL in place of the more difficult-to-understand DECODE and, second, the article reminded me of just how useful DECODE is and just how nice it would be to be able to use DECODE in PL/SQL programs.

Does that sound contradictory? I hope that by the time you finish this article all will be clear -- and you will see how you can, in fact, implement your own version of DECODE for use in PL/SQL.

DECODE is a special construct introduced by Oracle Corp. to let you perform conditional logic in SQL. Why is something special needed to do something as ordinary as conditional logic (the noble IF statement)? The reason is that SQL is not a procedural language. It is, instead, a declarative, set-at-a-time language in which you describe a set of data and the action you want to perform on that set. In the world of SQL, you are never supposed to have to "dirty" your hands with such complications as "if this and if that."

Of course, in the real world you want to do stuff like that all the time, so Oracle was kind enough to provide DECODE, which offers very concise syntax for what is essentially a CASE statement. The following query, for example, displays the name of an employee and the words Rich, Richer, or Richest, depending on that employee's salary:

SELECT ename,
       DECODE (sal, 
               100000, 'Rich', 
               1000000, 'Richer', 
               10000000, 'Richest') 
  FROM emp;

So in a single statement I was able to express the logic: If sal = 100000 then display "Rich" otherwise if sal = 1000000 then display "Richer" otherwise if sal = 10000000 then display "Richest." That is very handy -- and quite impossible in PL/SQL.

You see, although Oracle made almost every single one of its SQL functions available in PL/SQL, it left DECODE behind. As a result, you cannot compile code such as that shown in Listing 1.

The valid PL/SQL equivalent of this logic is more straightforward and easy to read:

IF v_sal = 100000
THEN
   v_description := 'Rich';
ELSIF v_sal = 1000000
THEN
   v_description := 'Richer';
ELSIF v_sal = 10000000
THEN
   v_description := 'Richest';
END IF;

So why am I writing an article about this topic? DECODE comes in handy in SQL because there is no if statement (except that now you can execute PL/SQL functions inside SQL, so you can have your cake and eat it, too, in SQL). In PL/SQL, there is no DECODE because you have the if statement. It turns out that I am writing this article (and offering a DECODE-like functionality in PL/SQL) because I am lazy.

When Lazy is Good

Laziness is not a good excuse for any particular programming practice. You should always take the time to do the job right the first time (or get as close as you can). On the other hand, I don't like to type any more code than is absolutely necessary. The less code I write, the fewer bugs I introduce into my programs and the less code I (or the next person in line) have to maintain. Furthermore, if I can reduce my code volume by using prebuilt, tested program units (as opposed to short cuts or clever tricks), my code quality also improves.

In the context of DECODE and conditional logic, though, what exactly am I thinking about not typing? Consider this scenario: I want to build a function (let's call it "fullname") to construct a full name from individual components (first name and last name) in the following format:

LAST, FIRST

But in order to make the function sufficiently flexible, I can call fullname without specifying the first and last name elements. The code for fullname follows:

FUNCTION fullname 
   (last_in IN VARCHAR2,
    first_in IN VARCHAR2 := NULL)
RETURN VARCHAR2
IS
BEGIN
   IF first_in IS NOT NULL
   THEN
      RETURN (last_in || ', ' || first_in);
   ELSE
      RETURN (last_in);
   END IF;
END fullname;

I must, in other words, "special case" the NULL first name so that I don't append a comma to the last name and then have nothing after it. It's not a very complicated program, but from another perspective, look at all of the code I need to write to deal with that wrinkle. What could I have done with a DECODE-like function in PL/SQL? In the following rewrite of fullname, I use a function called "ifelse" to "test drive" the approach:

FUNCTION fullname 
   (last_in IN VARCHAR2,
    first_in IN VARCHAR2 := NULL)
RETURN VARCHAR2
IS
BEGIN
   RETURN 
      (last_in || 
       ifelse (first_in IS NULL, NULL, ', ') || 
       first_in);
END fullname;

Now my program consists of a single return statement. The call to ifelse expresses directly the logic of fullname: If there is not first name, skip the comma. This lets me write source using an "in-line" coding style. Instead of building at different levels of indentation, I can express my requirements in a more straightforward manner.

Following is the very simple code behind the ifelse interface:

CREATE OR REPLACE FUNCTION ifelse
   (bool_in IN BOOLEAN,
    tval_in IN VARCHAR2,
    fval_in IN VARCHAR2)
   RETURN VARCHAR2
IS
BEGIN
   IF bool_in
   THEN
      RETURN tval_in;
   ELSE
      RETURN fval_in;
   END IF;
END;

Clearly, I am not talking rocket science here. Instead, I am seeking to demonstrate how you can encapsulate relatively simple logic behind a procedural interface and then use that procedure (or function) to clean up the code you subsequently write.

I appreciate the value of ifelse most sharply when I need to execute a sequence of conditional statements. In PL/Vision (a third-party library of packages for PL/SQL that I developed), for example, I often need to use dynamic SQL to perform queries against the ALL_OBJECTS data dictionary view. This view contains a row for every object to which I have access. I want to let users query objects by their name, type, and/or owner (schema). The code shown in Listing 2 constructs a WHERE clause against ALL_OBJECTS that lets me easily include one or all of these filters.

Without ifelse, I would have to write a very long and cumbersome string of IF statements, as well as declaring variables to hold the individual components, as I figured out what would go into the WHERE clause (try it yourself and experience the traditional solution). With ifelse, it all comes together in a very compact way.

Handling Complex Default Values

Another thing I like about the ifelse function is that I can use it in the declaration section of my programs to implement complex default value assignments. I like to keep the bodies of my programs as short and focused as possible; the body should contain only that logic needed to express the business requirement being implemented. So I find myself getting irritated when I have to write code such as the following:

PROCEDURE calc_stuff (date_in IN DATE)
IS
   v_date DATE DEFAULT SYSDATE;
BEGIN
   IF date_in < ADD_MONTHS (SYSDATE, -3)
   THEN
      v_date := date_in;
   END IF;
   . . .

The first four lines in this procedure do nothing but initialize my local date variable, v_date, based on the incoming argument and its relation to today's date. I must, in other words, distribute the code for the defaulting of v_date between the declaration and execution sections. If, on the other hand, I use ifelse, I can instead place all of this logic directly in the assignment of the default value for that variable:

PROCEDURE calc_stuff (date_in IN DATE)
IS
   v_date DATE DEFAULT 
      ifelse (date_in < ADD_MONTHS (SYSDATE, -3), 
         date_in, SYSDATE);
BEGIN
   . . .

This approach frees up the execution section to focus on the calculations required by calc_stuff.

There's only one problem with this use of ifelse: It won't compile. Why not? Because ifelse is a string function that accepts two strings as the last two arguments -- not two dates! The implementation of ifelse you saw earlier in this article can only be used when you are working with string datatypes. The solution to this problem is very straightforward: Build a package to "house" multiple versions of ifelse that support different datatypes.

Decoding Different Datatypes

If you create ifelse as a standalone string function and then also want another ifelse to be another standalone date function, you must give each of these functions different names, as in:

FUNCTION ifelsestg
   (bool_in IN BOOLEAN,
    tval_in IN VARCHAR2,
    fval_in IN VARCHAR2)
   RETURN VARCHAR2
FUNCTION ifelsedate
   (bool_in IN BOOLEAN,
    tval_in IN DATE,
    fval_in IN DATE)
   RETURN DATE

This is a very awkward way both to write and to call reusable code elements. A much superior style is to use the PL/SQL package. You can overload different programs with the same name inside a package (see either of my books for more details on packages and overloading). The result, in the context of the ifelse function and the PL/Vision library, is the portion of the PLV package shown in Listing 3.

By taking advantage of the packaged approach, my calls to ifelse will change to the dot notation shown here in my "new" version of calc_stuff:

PROCEDURE calc_stuff (date_in IN DATE)
IS
   v_date DATE DEFAULT 
      PLV.ifelse (date_in < ADD_MONTHS (SYSDATE, -3), 
         date_in, SYSDATE);
BEGIN
   . . .

and I would then provide a complete "suite" of in-line conditional logic functions for my PL/SQL programs!

Taking a Good Idea Too Far

Of course, you can also go overboard with this in-line approach to coding. One of the problems I have with Oracle's SQL DECODE is that you end up with very dense, hard-to-read logic. The same thing can occur with my PLV.ifelse function. Consider Listing 4, which shows the function, embedded_suffix, from the PLVtrc package. (You can see Listing 4 in the electronic version of this article on the Web at www.oreview.com.)

This function constructs a string whose contents depend on the arguments provided. I can replace these two IF statements, as well as the declaration of a local variable, with nothing more than Listing 5. (You can see Listing 5 in the electronic version of this article on the Web at www.oreview.com.) But it is far from clear to me that the in-line solution with "nested" calls to PLV.ifelse is preferable.

A Report Card on Ifelse

As I hope is clear from the usages of ifelse in this article, this technique presents both advantages and disadvantages. The advantages are:

• You can write less code and concentrate more of your effort on implementing complex business rules -- which is, after all, what we are all (more or less) paid to do.
• In some instances, you can avoid declaring local variables to hold transient values. Avoiding declarations becomes very important when you are manipulating strings. If you declare a local string, you must give it a length. So either you place a limit on the size of string you can handle, or you declare the maximum length of 32,767 -- which is relatively expensive in terms of memory and CPU.
• Oddly enough, because ifelse is not nearly as flexible as DECODE (which is a true CASE statement, accepting any number of option-value pairs), you are not likely to use it in ways that produce spaghetti code.

The disadvantages of ifelse include:

• It can be harder to understand conditional logic "embedded" inside a call to the ifelse function.
• In some scenarios, using ifelse will result in unnecessary additional CPU. Consider the embedded_suffix example in the previous section. Because all logic is in a single line, I have two identical calls to PLV.ifelse inside the outer call to PLV.ifelse. Both of these arguments will be evaluated and passed to PLV.ifelse, even though only one will be used.

Taking these pluses and minuses into account, you do not want to "blindly" replace every IF statement in your PL/SQL applications with a call to PLV.ifelse (or your own version of this technique). Choose your moments carefully and the result will be an optimal blend of concise yet readable code.

LISTING 1. Because Oracle does not make the DECODE SQL function available in PL/SQL, you cannot compile the code shown here.

v_description := 
   DECODE (v_sal,                /*   THIS   */
           100000, 'Rich',       /* WILL NOT */ 
           1000000, 'Richer',    /* COMPILE! */
           10000000, 'Richest');

LISTING 2. The code shown here constructs a WHERE clause against ALL_OBJECTS.

 'WHERE ' | | 
SUBSTR 
(
ifelse (name_is IS NOT NULL, 'AND object_name LIKE :name', NULL) | |
ifelse (type_is IS NOT NULL, 'AND object_type LIKE :type', NULL) | |
ifelse (owner_is IS NOT NULL, 'AND owner LIKE :owner', NULL),
5);

LISTING 3. A portion of the PLV package that shows the result of overloading different programs with the same name inside a package to write and call reusable code elements.

CREATE OR REPLACE PACKAGE PLV
IS
   FUNCTION ifelse
      (bool_in IN BOOLEAN, 
       tval_in IN BOOLEAN, 
       fval_in IN BOOLEAN)
   RETURN BOOLEAN;

   FUNCTION ifelse
      (bool_in IN BOOLEAN, 
       tval_in IN DATE, 
       fval_in IN DATE)
   RETURN DATE;

   FUNCTION ifelse
      (bool_in IN BOOLEAN, 
       tval_in IN NUMBER, 
       fval_in IN NUMBER)
   RETURN NUMBER;

   FUNCTION ifelse
      (bool_in IN BOOLEAN, 
       tval_in IN VARCHAR2, 
       fval_in IN VARCHAR2)
   RETURN VARCHAR2;
END PLV;

Listing 4. The function, embedded_suffix, from the PLVtrc package.

CREATE OR REPLACE FUNCTION embedded_suffix 
   (suffix_in IN VARCHAR2, delim_in IN VARCHAR2 := ' ') 
   RETURN VARCHAR2
IS
   return_value VARCHAR2(500);
BEGIN
   /* Function returns TRUE if user wants to display the current module */
   IF displaying_module
   THEN
      /* Call PL/Vision function to retrieve the current module name. */
      return_value := PLVtrc.current_module;
   END IF;

   IF suffix_in IS NULL
   THEN
      return_value := delim_in || return_value;
   ELSE
      return_value := 
         delim_in || suffix_in || ' ' || return_value;
   END IF;
   RETURN return_value;
END;

Listing 5. The code shown in Listing 4 can be greatly simplified by replacing the two IF statements, as well as well as the declaration of a local variable, with the code shown here.

 CREATE OR REPLACE FUNCTION embedded_suffix 
       (suffix_in IN VARCHAR2, delim_in IN VARCHAR2 := ' ') 
       RETURN VARCHAR2
    IS
    BEGIN
       RETURN 
          (PLV.ifelse 
             (suffix_in IS NULL, 
              delim_in || PLV.ifelse (displaying_module, module(3), NULL)),
              delim_in || suffix_in || ' ' || 
                 PLV.ifelse (displaying_module, module(3), NULL))));
    END;