Oracle PL/SQL - Simple CASE Expression with WHEN NULL

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

Introduction

If selector has the value NULL, it cannot be matched by WHEN NULL.

To match NULL value, use a searched CASE expression with WHEN boolean_expression IS NULL.

Demo

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

PL/SQL procedure successfully completed.

SQL>

The following code uses a searched CASE expression to match NULL value.

Demo

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

PL/SQL procedure successfully completed.

SQL>

Related Topic