Oracle PL/SQL - Rules for NULL value

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

Introduction

All logical operations including NOT involving NULL return NULL.

NULL in IF/THEN/ELSE or CASE statements is interpreted as FALSE.

Most operations including built-in functions, arithmetic operation with any NULL operand return NULL.

Rule Exception

  • Concatenations of strings ignore NULL.
  • DECODE can compare values with NULL.
  • REPLACE function can take NULL as a third parameter.

To check for NULL values, use the syntax: 

value IS [NOT] NULL

Demo

SQL>
SQL> declare-- from   w w  w .  j av  a 2  s  . c  om
  2      v1_nr NUMBER;
  3      v2_nr NUMBER :=1;
  4  begin
  5      if v1_nr is null then
  6          DBMS_OUTPUT.put_line('V1 is NULL');
  7      elsif v1_nr is not null then
  8          DBMS_OUTPUT.put_line('V1 is not NULL');
  9      else
 10          DBMS_OUTPUT.put_line('Undefined result');
 11      end if;
 12
 13      if v2_nr is null then
 14          DBMS_OUTPUT.put_line('V2 is NULL');
 15      elsif v2_nr is not null then
 16          DBMS_OUTPUT.put_line('V2 is not NULL');
 17      else
 18          DBMS_OUTPUT.put_line('Undefined result');
 19      end if;
 20  end;
 21  /
V1 is NULL
V2 is not NULL

PL/SQL procedure successfully completed.

SQL>

Related Topic