Skip to main content

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!

Comments

  1. decode is useful if you have something short and concise, e.g. decode(some_value, 'Y', 1, 0) For anything long and complicated case is better.

    Also, the null related functions nvl and nvl2 are incredibly handy particularly for something short and concise e.g. nvl2(some_value, 'A', 'B') Again, for anything long and complicated, case is better.

    ReplyDelete
    Replies
    1. I was thinking about including NVL but then, well, got lazy. :-)

      In terms of DECODE, yes, it can save you a few keystrokes, but it isn't part of the ANSI standard and can be harder to read (tho not with the most trivial, which is what you are proposing it for).

      I do suggest that developers not take the shortcut in this case, and stick with CASE.

      Delete
    2. I believe Oracle converts the decode to case at parse time?

      Delete
  2. Not only that DECODE should be converted to CASE.
    But also NVL should be converted to COALESCE. The reason is better evaluation.

    ReplyDelete
    Replies
    1. I assume you mean that NVL always evaluates the second expression, even if the first is NOT NULL. Good point! And a great topic for a quiz at the Oracle Dev Gym. https://devgym.oracle.com :-)

      Delete
  3. Maybe contentious, but exception handling can be a form of conditional logic?

    Begin
    Select name Into vName From emp;
    ProcessName(vName)
    Exception When NoDataFound
    ProcessNoName;
    End;

    ReplyDelete
    Replies
    1. Well, it's not contentious in that you are correct that one could employ exception handling as a form of conditional logic.

      The controversy is over whether or not your SHOULD (I know, I know: that's what you meant).

      I believe you should not and I do not want to even encourage people to think that way. Perhaps a good subject for another post:

      Writing conditional logic the wrong way in PL/SQL

      :-)

      Delete
    2. I'd like to see a post with some alternatives to the above.

      (But not Select MAX(name)... to suppress the error!)

      Delete
    3. Peter, just so I understand, do you mean alternatives to the use of exception handling and other sub-optimal ways to implement conditional logic, with a reworking that uses the intended forms?

      Delete
    4. Yes. I'll admit to writing coded like the above. I've also coded subqueries in a SQL CASE statement. But if there's a more elegant solution to writing PL/SQL conditional logic that involves a query that may return zero rows, I'm all eyes and ears.

      Delete
    5. I think that the only consideration is what goes into the exception section, not "Should I trap NDF with my select-into and then execute different code as a result?"

      I suggest that the exception section should hold an absolute minimum of application logic, so I would opt for something like this:

      BEGIN
      BEGIN
      SELECT .. INTO var FROM ...;
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
      l_notfound := TRUE;
      END;

      IF l_notfound THEN ...
      ELSE
      ...continue program flow...
      END IF;
      END;

      Argh, I hate how blogger doesn't let me use fixed font. But I hope you get the idea.

      Delete
    6. I *LIKE* it, Steven. I will use this form from on, but with two tweaks. I will set the boolean in either case, and I will use a true-named boolean. Following the true leg of an IF with a negative_name gives me fits on days when I'm not playing my A-game. ;)

      Delete
    7. Yes to both your tweaks; they are an improvement on my quick hack of code. Stay positive! :-)

      Delete
    8. Mmmm... this is the way I used to do it in the past, but after some discussions with colleagues in the past, I slowly switched to their approach of using a cursor fetch together with the %NOTFOUND option for the second case. The result is the same (not sure about performance), but you avoid using exceptions for your application logic.

      Delete
    9. I have demonstrated to a few developers how much less efficient using exception handling as a conditional expression can be. Once they understand the performance implications they usually avoid the practice.

      Delete
    10. That's the way to do it. But that should not in and of itself push you away from implicits. It would argue, instead, for a nuanced coding approach, with this more nuanced understanding.

      Specifically, if you know that a select-into will mostly or very often fail, then switch to explicit. But generally our select-into's almost always will find their row. So the overall efficiency for the application could be greater with implicits.

      Delete
    11. Reply to Erwin Knop: sorry about delay. Generally select-into is faster than an explicit fetch, but perhaps not enough for you to worry about. Perhaps.

      See, context is all. If this is a query executed lots and lots of times, and almost always returns a row - select-into is better. If not executed often, meh.

      If executed lots of times, and mostly fails, an explicit cursor is probably best.

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel