WELCOME, GUEST
 
 
Minimize
Toad World Puzzler - July 2007
The Mysteries of CASE in PL/SQL
 
Did you know that PL/SQL supports CASE? This feature was introduced in Oracle9i database in two forms: the CASE expression and the CASE statement. CASE offers a nice alternative to the good, old IF statement, but there is at least one less-than-obvious aspect of PL/SQL's CASE.
 
First of all, there are two variations of CASE in PL/SQL: the CASE expression and CASE statement. The CASE expression (true to its namesake) is an expression that fits inside a PL/SQL statement. The CASE statement is a standalone executable statement.
 
I use CASE below in the fruit_translator function ("A" is for "Apple", "B" is for "Banana"). In the first version, I use a CASE expression, and in the second I use a CASE statement.
 
So here is the quiz: what will I see when I display the value returned by each function when I pass in a value of "C"? 
CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2
IS
   retval   VARCHAR2 (100);
BEGIN
   RETURN CASE
      WHEN letter_in = 'A' THEN 'Apple'
      WHEN letter_in = 'B' THEN 'Banana'
   END;
END fruit_translator;
/
SET SERVEROUTPUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Expression good for you = ' 
           || NVL (fruit_translator ('C'), 'Unknown Fruit'));
END;
/
CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2
IS
   retval VARCHAR2(100);
BEGIN   
   CASE
      WHEN letter_in = 'A' THEN retval := 'Apple';
      WHEN letter_in = 'B' THEN retval := 'Banana';
   END CASE;
   RETURN retval;
END fruit_translator;
/
SET SERVEROUTPUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Statement good for you = ' 
           || NVL (fruit_translator ('C'), 'Unknown Fruit'));
END;
/
Submit