Oracle PL/SQL - Simple CASE Expression

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. Simple CASE Expression

Introduction

A simple CASE expression has this syntax: 

CASE selector 
WHEN selector_value_1 THEN result_1 
WHEN selector_value_2 THEN result_2 
 ... 
WHEN selector_value_n THEN result_n 
 [ ELSE 
  else_result ] 
END

The selector is an expression.

Each selector_value and each result can be either a literal or an expression.

At least one result must not be the literal NULL.

The CASE expression returns the first result for which selector_value matches selector.

Remaining expressions are not evaluated in other branches.

If no selector_value matches selector, the CASE expression returns else_result if it exists and NULL otherwise.

The following code assigns the value of a simple CASE expression to the variable mark_description.

The selector is final_mark.

Demo

SQL>
SQL> DECLARE-- from   w w w.  java 2 s  . com
  2    final_mark CHAR(1) := 'B';
  3    mark_description VARCHAR2(20);
  4  BEGIN
  5    mark_description :=
  6      CASE final_mark
  7        WHEN 'A' THEN 'Excellent'
  8        WHEN 'B' THEN 'Very Good'
  9        WHEN 'C' THEN 'Good'
 10        WHEN 'D' THEN 'Fair'
 11        WHEN 'F' THEN 'Poor'
 12        ELSE 'No such final_mark'
 13      END;
 14      DBMS_OUTPUT.PUT_LINE ('Grade ' || final_mark || ' is ' || mark_description);
 15  END;
 16   /
Grade B is Very Good

PL/SQL procedure successfully completed.

SQL>

The following code uses a simple CASE statement to compare a single value to many possible values.

Demo

SQL>
SQL>--  ww w  .java2s . c  o  m
SQL> DECLARE
  2    final_mark CHAR(1);
  3  BEGIN
  4    final_mark := 'B';
  5
  6    CASE final_mark
  7      WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  8      WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
  9      WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
 10      WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
 11      WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
 12      ELSE DBMS_OUTPUT.PUT_LINE('No such final_mark');
 13    END CASE;
 14  END;
 15  /
Very Good

PL/SQL procedure successfully completed.

SQL>

In a simple CASE expression, if the selector in a simple CASE statement has the value NULL, it cannot be matched by WHEN NULL.

Instead, use a searched CASE statement with WHEN condition IS NULL.

Related Topics