PL/SQL 101: Writing conditional logic in PL/SQL

PL/SQL offers a number of options when it comes to writing conditional logic, as in variations on "If this then that". This post reviews them, and provides examples.

You can choose from the following:

• IF statement - IF, IF-THEN, IF-ELSE, IF-ELSIF-ELSE
• CASE statement - simple and searched CASE
• CASE expression - the expression "version" of the statement (and one of my favorite elements of PL/SQL)

IF Statement

It's hard to imagine a programming language without a version of IF, and PL/SQL is no exception. 

The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement can take one of three forms, but they all have this in common: they start with "IF" and then end with "END IF;".

IF THEN

The simplest IF statement: if the boolean expression evaluates to TRUE then execute the statements between THEN and END IF. Example:

BEGIN
   IF l_hire_date < SYSDATE
   THEN
      send_survey_request (l_employee_id);
   END IF;
END;

Tips:

• You can put the IF expression inside parentheses, but you do not have to.
• If you've got a really complex, multi-part expression, consider assigning it to a variable first, as I show below. That way, you can more easily trace and test what that expression evaluates to.

So instead of this (an example from the code base of the Oracle Dev Gym)....

BEGIN
   IF     l_is_competitive = qdb_config.c_yes
      AND l_ce_status = qdb_competition_mgr.c_compev_ranked
      AND (   qdb_comp_event_mgr.is_closed (comp_event_id_in)
           OR comp_event_id IS NULL)
   THEN
      get_answer_info;
   END IF;
END;
/

....consider writing your code as follows:

BEGIN
   l_ok_to_see_answer :=
          l_is_competitive = qdb_config.c_yes
      AND l_ce_status = qdb_competition_mgr.c_compev_ranked
      AND (   qdb_comp_event_mgr.is_closed (comp_event_id_in)
           OR comp_event_id IS NULL);

   qdb_utilities.trace_activity ('get_answer check', l_ok_to_see_answer);

   IF l_ok_to_see_answer
   THEN
      get_answer_info;
   END IF;
END;

Of course, you will need to call your own trace utility, or better yet use the open source Logger.

IF THEN ELSE

Offer an alternative action if your boolean expression does not evaluate to TRUE.

BEGIN
   IF l_hire_date < SYSDATE
   THEN
      send_survey_request (l_employee_id);
   ELSE
      send_invite_to_new_hire_meetup (l_employee_id);
   END IF;
END;

Tips:

• Same tip as with IF for moving complex expressions into a variable.
• There is a big difference between "does not evaluate to TRUE" and "evaluates to FALSE". That expression could evaluate to NULL. In the above IF-ELSE, an invitation will be sent to that "employee" even if the hire_date is NULL. Probably not what you want to do.
• If you need different actions for FALSE and NULL conditions, then you need to use ELSIF (below).

IF THEN ELSIF

When the logic gets tough, the tough start using ELSIFs.

Sometimes it's not an either-or situation. It's more of a this-or that-or the other-or wait a minute something else! Or as noted above, maybe you just need a separate clause for NULL, as in:

BEGIN
   IF l_hire_date < SYSDATE
   THEN
      send_survey_request (l_employee_id);

   ELSIF l_hire_date >= SYSDATE
   THEN
      send_invite_to_new_hire_meetup (l_employee_id);

   ELSE
      remove_from_employee_table (l_employee_id);
   END IF;
END;

And of course there's nothing stopping you from having LOTS of ELSIF clauses:

BEGIN
   IF l_hire_date < ADD_MONTHS (SYSDATE, -12)
   THEN
      send_survey_request (l_employee_id);

   ELSIF l_hire_date BETWEEN ADD_MONTHS (SYSDATE, -12) AND SYSDATE
   THEN
      send_newhire_survey (l_employee_id);

   ELSIF EXTRACT (YEAR FROM l_hire_date) = EXTRACT (YEAR FROM SYSDATE)
   THEN
      send_invite_to_new_hire_meetup (l_employee_id);

   ELSIF EXTRACT (YEAR FROM l_hire_date) = EXTRACT (YEAR FROM SYSDATE) + 1
   THEN
      add_to_waitlist (l_employee_id);

   ELSE
      remove_from_employee_table (l_employee_id);
   END IF;
END;

Tips:

• Avoid repeated evaluations of the same expressions in multiple IF-ELSIF clauses. I extract the year number for hire date and SYSDATE twice in the block above. These are fast operations, so it's no big deal, but if you call an "expensive" application function multiple times, assign the call to a variable and then reference the variable multiple times in the IF statement.
• If you start building a long list of ELSIF clauses, consider switching to a CASE statement. It's cleaner and easier to read/manage.

OK, so much for the kinda boring IF statement. Let's have fun with CASE!

CASE Statement

CASE was added to PL/SQL in Oracle Database 9i, back in 2001 or so. How do I know? Because I looked through the various editions of Oracle PL/SQL Programming until I found its first appearance in the index. :-). I was very happy when CASE was added, especially CASE expressions (which can be used in both SQL and PL/SQL, by the way)

A CASE statement is logically equivalent to an IF-ELSIF statement, but a CASE statement "presents" better (easier to read, often involving less code).

The CASE statement chooses from a sequence of conditions, and runs the corresponding statement. , and has these forms:

• Simple, which evaluates a single expression and compares it to several potential values.
• Searched, which evaluates multiple conditions and chooses the first one that is true.

Here's a simple CASE statement:

FUNCTION grade_translator (grade_in IN VARCHAR2)
   RETURN VARCHAR2
IS
   retval   VARCHAR2 (100);
BEGIN
   CASE grade_in
      WHEN 'A'
      THEN
         retval := 'Excellent';
      WHEN 'B'
      THEN
         retval := 'Very Good';
      WHEN 'C'
      THEN
         retval := 'Good';
      WHEN 'D'
      THEN
         retval := 'Fair';
      WHEN 'F'
      THEN
         retval := 'Poor';
   END CASE;

   RETURN retval;
END;

Here's a searched CASE statement version of that same logic:

FUNCTION grade_translator (grade_in IN VARCHAR2)
   RETURN VARCHAR2
IS
   retval   VARCHAR2 (100);
BEGIN
   CASE
      WHEN grade_in = 'A'
      THEN
         retval := 'Excellent';
      WHEN grade_in = 'B'
      THEN
         retval := 'Very Good';
      WHEN grade_in = 'C'
      THEN
         retval := 'Good';
      WHEN grade_in = 'D'
      THEN
         retval := 'Fair';
      WHEN grade_in = 'F'
      THEN
         retval := 'Poor';
      ELSE
         retval := 'No such grade';
   END CASE;

   RETURN retval;
END;

Hopefully it's clear to you that using a searched CASE statement in which each WHEN clause has the same "variable = value" format is a poor choice. That's a perfect use case for a simple CASE.

Also, if all you are doing inside each WHEN clause is assigning a value to the same variable, that's a piece of code that is crying out for a CASE expression (next section).

Here's a more interesting example of a searched CASE statement, taken from the workout manager package for the Oracle Dev Gym:

   FUNCTION can_change_workout (workout_id_in IN INTEGER)
      RETURN BOOLEAN
   IS
      l_count   INTEGER;
      l_start   DATE;
      l_end     DATE;
   BEGIN
      SELECT wo.start_date, wo.end_date
        INTO l_start, l_end
        FROM dg_workouts wo
       WHERE workout_id = workout_id_in;

      CASE
         WHEN l_end <= SYSDATE
         THEN
            RETURN FALSE;
         WHEN l_start > SYSDATE
         THEN
            RETURN TRUE;
         ELSE
            /* The week has started. Have you done anything yet? */

            SELECT COUNT (*)
              INTO l_count
              FROM dg_user_wo_results uwor, dg_user_workouts uwo
             WHERE     uwor.completed_date IS NOT NULL
                   AND uwo.workout_id = workout_id_in;

            RETURN l_count = 0;
      END CASE;
   END;

The CASE statement is appropriate when a different action is to be taken for each alternative. The CASE expression is great for when you need to evaluate or return a different value for each alternative. I love how CASE expressions de-clutter my code. Let's take a look.

CASE Expression

A CASE expression "liberates" CASE from being its own statement. As an expression, CASE can be part of a statement:

• The right hand side of an assignment
• Passed as an actual argument to the formal parameter of a subprogram
• Part of a larger expression

It's one of my favorite parts of PL/SQL, because it let's me write more concise code. To give you a sense of that, suppose that I need to write a function that implements this silly requirement:

• Put a "-" between all three numbers.
• If s1 is like 'T%' then upper case the string.
• If s2 is NULL, there should be just one - between s1 and s2.
• If s3's length > 6 then lower case the string.

Here's the function using the IF statement:

FUNCTION plch_full_string (
    s1   IN VARCHAR2
 ,  s2   IN VARCHAR2
 ,  s3   IN VARCHAR2) RETURN VARCHAR2
IS
   l_return   VARCHAR (32767);
BEGIN
   IF s1 LIKE 'T%'
   THEN
      l_return := UPPER (s1);
   ELSE
      l_return := s1;
   END IF;

   IF s2 IS NOT NULL
   THEN
      l_return := l_return || '-' || s2;
   END IF;

   IF LENGTH (s3) > 6
   THEN
      l_return := l_return || '-' || LOWER (s3);
   ELSE
      l_return := l_return || '-' || s3;
   END IF;

   RETURN l_return;
END;

And now with a CASE expression:

FUNCTION plch_full_string (
    s1   IN VARCHAR2
 ,  s2   IN VARCHAR2
 ,  s3   IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
   RETURN    CASE
                WHEN s1 LIKE 'T%' THEN UPPER (s1)
                ELSE s1
             END
          || CASE WHEN s2 IS NULL THEN NULL ELSE '-' END
          || s2
          || '-'
          || CASE
                WHEN LENGTH (s3) > 6 THEN LOWER (s3)
                ELSE s3
             END;
END;

Right? Isn't that so much better? Here's another example of shrinking your code with CASE expressions, a progression from CASE statement to CASE expression to CASE expression directly inside call to DBMS_OUTPUT.PUT_LINE.

PROCEDURE show_pig_species_v1 (feature_in IN VARCHAR2)
IS
   l_guinea_pig_species   VARCHAR2 (100);
BEGIN
   CASE feature_in
      WHEN 'curly'
      THEN
         l_guinea_pig_species := 'Texel';
      WHEN 'long'
      THEN
         l_guinea_pig_species := 'Peruvian';
      WHEN 'rosettes'
      THEN
         l_guinea_pig_species := 'Abyssinian';
   END CASE;

   DBMS_OUTPUT.put_line ('Species=' || l_guinea_pig_species);
END;

PROCEDURE show_pig_species_v2 (feature_in IN VARCHAR2)
IS
   l_guinea_pig_species   VARCHAR2 (100);
BEGIN
   l_guinea_pig_species :=
      CASE feature_in
         WHEN 'curly' THEN 'Texel'
         WHEN 'long' THEN 'Peruvian'
         WHEN 'rosettes' THEN 'Abyssinian'
      END;

   DBMS_OUTPUT.put_line ('Species=' || l_guinea_pig_species);
END;

PROCEDURE show_pig_species_v3 (feature_in IN VARCHAR2)
IS
BEGIN
   DBMS_OUTPUT.put_line (
         'Species='
      || CASE feature_in
            WHEN 'curly' THEN 'Texel'
            WHEN 'long' THEN 'Peruvian'
            WHEN 'rosettes' THEN 'Abyssinian'
         END);
END;

Tips on CASE

• If a CASE statement fails to find a match in any WHEN clauses and there is no ELSE clause, an error is raised: ORA-06592: CASE not found while executing CASE statement.
• If a CASE expression fails to find a match in any WHEN clauses and there is no ELSE clause, it returns NULL.
• You can use the CASE expression inside SQL. You can't use IF and CASE statements inside a SQL statement, because, well, it's already it's own statement, and statements cannot have within them other statements. 

Hey, What About DECODE?

And then there is DECODE. If you've used DECODE, that means that either you've been working with Oracle Database for a looooong time, or you should have used CASE but instead somehow latched onto this anachronism. :-)

DECODE offers CASE-like functionality, but can only be used inside SQL (either outside of a PL/SQL block, or from within PL/SQL). It's been a part of SQL long before CASE was added to SQL. Haven't used it yet? Don't bother. You should use CASE instead. Have it in your existing SQL statements? Consider converting it to CASE the next time you need to work on those SQL statements.

I won't even bother showing you examples, but here is a link to appropriately aged doc on DECODE.

To Conclude

When you need to write conditional logic in your PL/SQL blocks (and who doesn't, now and again?), you have a nice range of offerings. Whether you go with the "traditional" IF statement or opt for CASE, you'll be able to get the job done with a minimum of fuss.

I simply suggest that you follow a few handy guidelines:

• Avoid redundancies in your code ("WHEN x = y" over and over in your CASE clauses or repeated execution of same union in ELSIF clauses).
• Look for opportunities to use the CASE expression to cut down on code volume and improve readability.
• Assign complex Boolean expressions to variables or constants, rather than "embedding" those expressions inside your conditional statements. That way you can examine/trace the results of those expression more easily, and debug your code more quickly.

Finally, visit the Oracle Dev Gym to take lots of quizzes on these PL/SQL features!