Oracle PL/SQL - IS [NOT] NULL Operator


The IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is not NULL.

The IS NOT NULL operator does the opposite. Comparisons involving NULL values always yield NULL.

To test whether a value is NULL, use IF value IS NULL, as in these examples:


SQL> DECLARE-- from w  w  w .j  a v  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.