Jul 2007: The Mysteries of CASE in PL/SQL

Home

Products

Community

Experts

Knowledge

Downloads

Hello, you are not logged in. Login or sign up

Experts >> Steven Feuerstein's PL/SQL Obsession >> Puzzles and Quizzes >> Jul 2007: The Mysteries of CASE in PL/SQL


		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