VF=>user  ID=>    Login Feedback FAQ Blog
1,077,316 quizzes played | 3,164 active players

Actions


  On Conditional Logic in PL/SQL: CASE in PL/SQL (Oracle PL/SQL)      Add to Favorites

Summary

Be careful about mixing the two forms of CASE together (searched and simple). You can end up with very confusing code!

Details

Assumes Oracle Database 10g Release 2 or higher
Intermediate Quiz, IDs: 15301/1478703/935919

Reviewer(s): Vitaliy Lyanchevskiy

The Question

Which of the choices display "my_flag is NULL" after execution?

The Choices [↑]

Explanation of Result Icons
Choice 1 (29256)
DECLARE
   my_flag   BOOLEAN;
BEGIN
   CASE my_flag
      WHEN my_flag IS NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is NULL');
      WHEN TRUE
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is TRUE');
      ELSE
         DBMS_OUTPUT.PUT_LINE ('my_flag is FALSE');
   END CASE;
END;
/

I have written a simple CASE (which is of the form CASE expression WHEN ...), but then my WHEN clauses follow a typical searched CASE format (CASE WHEN expr1 ... WHEN expr2 ...). The result is some very confused code.




82%
Choice 2 (29257) [Do Not Use]
DECLARE
   my_flag   BOOLEAN;
BEGIN
   CASE
      WHEN my_flag IS NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is NULL');
      WHEN TRUE
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is TRUE');
      ELSE
         DBMS_OUTPUT.PUT_LINE ('my_flag is FALSE');
   END CASE;
END;
/

A confusing piece of code that just happens to work - because my_flag is always NULL. Notice the second WHEN clause: "WHEN TRUE" - well, if the first WHEN clause fails, this one certainly will always work. It's the sort of typo that can slip into your code too easily. And when you read the code you could get confused and think "Yeah, that's right. When my_flag is TRUE...." But that's not really what it is saying!




53%
Choice 3 (29258)
DECLARE
   my_flag   BOOLEAN;
BEGIN
   DBMS_OUTPUT.PUT_LINE (
         'my_flag is '
      || CASE my_flag
            WHEN FALSE THEN 'FALSE'
            WHEN TRUE THEN 'TRUE'
            ELSE 'NULL'
         END);
END;
/

A CASE expression that neatly does the trick.




82%
Choice 4 (29259)
DECLARE
   my_flag   BOOLEAN;
BEGIN
   CASE 
      WHEN my_flag IS NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is NULL');
      WHEN my_flag = TRUE
      THEN
         DBMS_OUTPUT.PUT_LINE ('my_flag is TRUE');
      ELSE
         DBMS_OUTPUT.PUT_LINE ('my_flag is FALSE');
   END CASE;
END;
/

A searched CASE statement, with no expression after CASE, it works as expected.




74%

Answer [↑]

PL/SQL has three categories of control statements:

  • Conditional selection statements, which run different statements for different data values. The conditional selection statements are IF and CASE.
  • Loop statements, which run the same statements with a series of different data values. The loop statements are the basic LOOP, FOR LOOP, and WHILE LOOP.
    The EXIT statement transfers control to the end of a loop. The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration. Both EXIT and CONTINUE have an optional WHEN clause, where you can specify a condition.
  • Sequential control statements, which are not crucial to PL/SQL programming. The sequential control statements are GOTO, which goes to a specified statement, and NULL, which does nothing.

Oracle offers the IF and CASE statements to support conditional logic in PL/SQL blocks.

The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement comes in these forms:

  • IF THEN

  • IF THEN ELSE

  • IF THEN ELSIF

The CASE statement chooses from a sequence of conditions, and runs the corresponding statement. The CASE statement 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.

As to when you should use IF and when CASE, I suggest the following guidelines:

1. If the purpose of the IF statement is to assign a value to a variable, then use a CASE expression. IF is always a statement, and thus cannot be embedded inside an expression. Compare the two approaches below:

DECLARE
   l_day_type   VARCHAR2 (10);
BEGIN
   IF TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN')
   THEN
      l_day_type := 'Weekend';
   ELSE
      l_day_type := 'Weekday';
   END IF;

   l_day_type :=
      CASE
         WHEN TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN 'Weekend'
         ELSE 'Weekday'
      END;
END;
/

With CASE, I can get all the work done in a single statement.

2. If you have a long list of ELSIF clauses comparing values, consider switching to CASE. Here's an example: first, I implement my "grade translator" with an IF statement:

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

   RETURN retval;
END;
/

and now the CASE expression:

CREATE OR REPLACE FUNCTION grade_translator (grade_in IN VARCHAR2)
   RETURN VARCHAR2
IS
BEGIN
   RETURN CASE grade_in
             WHEN 'A' THEN 'Excellent'
             WHEN 'B' THEN 'Very Good'
             WHEN 'C' THEN 'Good'
             WHEN 'D' THEN 'Fair'
             WHEN 'F' THEN 'Poor'
             ELSE 'No such grade'
          END;
END;
/

Statistics and Rankings [↑]

Rank Percentile Score Seconds% Correct
Median
92
50
341
102
75
Best
1
100
486
42
100

Click to View Verification Code [↑]

Quiz Link and Tweet [↑]

Pass along a link the quiz or use our text to tweet about this quiz. Thanks for helping to spread the word!

 
About Oracle | Terms of Use | Your Privacy Rights | Copyright 2010-2017, Oracle Corporation