Oracle PL/SQL - Searched CASE Expression with WHEN ... IS NULL

Introduction

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

Demo

SQL>
SQL> DECLARE--   w  w  w  .  j av a  2 s .  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