Oracle CASE Statement

In Oracle Database, the CASE statement compares a list of conditions and returns one of multiple possible expressions.

Oracle Database’s CASE statement is very similar to the CASE expression (which is defined in the SQL standard (ISO/IEC 9075)). However, Oracle supports both the CASE expression and the CASE statement, and there’s a distinction between the two. The CASE statement can be used to execute of a sequence of PL/SQL statements, whereas the CASE expression returns a single value. Also, there’s a difference in how they deal with the lack of an ELSE clause when a condition is not met.

Syntax

The CASE statement can take two forms. The first form is known as the simple CASE statement:

[ <<label>> ] CASE case_operand
  WHEN boolean_expression THEN statement ;
    [ WHEN boolean_expression THEN statement ; ]...
      [ ELSE statement [ statement ]... ;
        END CASE [ label ] ;

The other form is the searched CASE statement:

[ <<label>> ] CASE
  WHEN boolean_expression THEN statement ;
    [ WHEN boolean_expression THEN statement ; ]...
      [ ELSE statement [ statement ]... ;
        END CASE [ label ];

The searched CASE statement evaluates a set of Boolean expressions to determine the result. When using this form, no case_operand is supplied.

Example – Simple CASE Statement

Here’s an example to demonstrate the simple CASE statement:

DECLARE
  cuisine VARCHAR(20);
BEGIN
  cuisine := 'Italian';

  CASE cuisine
    WHEN 'Thai' THEN DBMS_OUTPUT.PUT_LINE('Pad Thai');
    WHEN 'Italian' THEN DBMS_OUTPUT.PUT_LINE('Pizza');
    WHEN 'English' THEN DBMS_OUTPUT.PUT_LINE('Sunday Roast');
    ELSE DBMS_OUTPUT.PUT_LINE('Fish and Chips');
  END CASE;
END;
/

Result:

Pizza

If we change the value of the variable, we get a different result:

DECLARE
  cuisine VARCHAR(20);
BEGIN
  cuisine := 'Thai';

  CASE cuisine
    WHEN 'Thai' THEN DBMS_OUTPUT.PUT_LINE('Pad Thai');
    WHEN 'Italian' THEN DBMS_OUTPUT.PUT_LINE('Pizza');
    WHEN 'English' THEN DBMS_OUTPUT.PUT_LINE('Sunday Roast');
    ELSE DBMS_OUTPUT.PUT_LINE('Fish and Chips');
  END CASE;
END;
/

Result:

Pad Thai

When the value doesn’t match a condition, the value at ELSE is used:

DECLARE
  cuisine VARCHAR(20);
BEGIN
  cuisine := 'New Zealand';

  CASE cuisine
    WHEN 'Thai' THEN DBMS_OUTPUT.PUT_LINE('Pad Thai');
    WHEN 'Italian' THEN DBMS_OUTPUT.PUT_LINE('Pizza');
    WHEN 'English' THEN DBMS_OUTPUT.PUT_LINE('Sunday Roast');
    ELSE DBMS_OUTPUT.PUT_LINE('Fish and Chips');
  END CASE;
END;
/

Result:

Fish and Chips

Example – Searched CASE Statement

We could rewrite the above example to a searched CASE statement. Doing this means we would remove the base expression, but then include it in the WHEN part of the statement:

DECLARE
  cuisine VARCHAR(20);
BEGIN
  cuisine := 'Italian';

  CASE
    WHEN cuisine = 'Thai' THEN DBMS_OUTPUT.PUT_LINE('Pad Thai');
    WHEN cuisine = 'Italian' THEN DBMS_OUTPUT.PUT_LINE('Pizza');
    WHEN cuisine = 'English' THEN DBMS_OUTPUT.PUT_LINE('Sunday Roast');
    ELSE DBMS_OUTPUT.PUT_LINE('Fish and Chips');
  END CASE;
END;
/

Result:

Pizza

We get the same result when we pass the same value. However, there’s a difference in the processing. When we use the simple CASE statement, cuisine is only evaluated once. But when we use the searched CASE statement, cuisine is evaluated multiple times.

Omitting the ELSE Clause

If we omit the ELSE clause, and none of the conditions are met, the result is a CASE_NOT_FOUND error:

DECLARE
  cuisine VARCHAR(20);
BEGIN
  cuisine := 'New Zealand';

  CASE cuisine
    WHEN 'Thai' THEN DBMS_OUTPUT.PUT_LINE('Pad Thai');
    WHEN 'Italian' THEN DBMS_OUTPUT.PUT_LINE('Pizza');
    WHEN 'English' THEN DBMS_OUTPUT.PUT_LINE('Sunday Roast');
  END CASE;
END;
/

Result:

ORA-06592: CASE not found while executing CASE statement ORA-06512: at line 10
ORA-06512: at "SYS.DBMS_SQL", line 1721

But we don’t get the error if the case is found:

DECLARE
  cuisine VARCHAR(20);
BEGIN
  cuisine := 'English';

  CASE cuisine
    WHEN 'Thai' THEN DBMS_OUTPUT.PUT_LINE('Pad Thai');
    WHEN 'Italian' THEN DBMS_OUTPUT.PUT_LINE('Pizza');
    WHEN 'English' THEN DBMS_OUTPUT.PUT_LINE('Sunday Roast');
  END CASE;
END;
/

Result:

Sunday Roast

Note that the CASE expression (as opposed to the CASE statement being discussed here) will return NULL when there’s no ELSE clause and no condition is met.