Oracle PL/SQL Tutorial/PL SQL Data Types/NULL

All logical operations (including NOT) involving NULL always return NULL

In a logical group of IF/THEN/ELSE or CASE statements, NULL is interpreted as FALSE.

Most operations (built-in functions, arithmetic) with any NULL operand return NULL with the following exceptions:

Concatenations of strings ignore NULL.

DECODE (which we discuss later) can compare values with NULL.

The REPLACE function can take NULL as a third parameter.

You should check for NULL values by using the syntax:

   <source lang="sql">

variable|expression|function IS [NOT] NULL</source>

Compare Integer value with NULL value

   <source lang="sql">

SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    a     INTEGER;
 3    b     BOOLEAN;
 4    n     INTEGER;     --this will be our null value.
 5  BEGIN
 6    a := 2;
 7
 8    --TRUE or NULL = TRUE
 9    IF (a = 2) OR (a <> n) THEN
10      DBMS_OUTPUT.PUT_LINE("TRUE or NULL = TRUE");
11    ELSE
12      DBMS_OUTPUT.PUT_LINE("TRUE or NULL = NULL");
13    END IF;
14  END;
15  /

TRUE or NULL = TRUE PL/SQL procedure successfully completed. SQL></source>

Compare NULL value and OR operator

   <source lang="sql">

SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    a     INTEGER;
 3    b     BOOLEAN;
 4    n     INTEGER;     --this will be our null value.
 5  BEGIN
 6    a := 2;
 7
 8    IF (a = n) OR (a <> n) THEN
 9      DBMS_OUTPUT.PUT_LINE("(a = n) or (a <> n) is true");
10    ELSE
11      DBMS_OUTPUT.PUT_LINE("(a = n) or (a <> n) is not true");
12    END IF;
13  END;
14  /

(a = n) or (a <> n) is not true PL/SQL procedure successfully completed. SQL></source>

Compare with NULL value and "AND" operator

   <source lang="sql">

SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    a     INTEGER;
 3    b     BOOLEAN;
 4    n     INTEGER;     --this will be our null value.
 5  BEGIN
 6    a := 2;
 7
 8    --TRUE and NULL = NULL
 9    IF (a = 2) AND (a <> n) THEN
10      DBMS_OUTPUT.PUT_LINE("TRUE and NULL = TRUE");
11    ELSE
12      DBMS_OUTPUT.PUT_LINE("TRUE and NULL = NULL");
13    END IF;
14  END;
15  /

TRUE and NULL = NULL PL/SQL procedure successfully completed. SQL></source>

Compare with NULL value for equality

   <source lang="sql">

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    a     INTEGER;
 3    b     BOOLEAN;
 4    n     INTEGER;     --this will be our null value.
 5  BEGIN
 6    a := 2;
 7
 8    IF a = n THEN
 9      DBMS_OUTPUT.PUT_LINE("a = n is true");
10    ELSE
11      DBMS_OUTPUT.PUT_LINE("a = n is not true");
12    END IF;
13  END;
14  /

a = n is not true PL/SQL procedure successfully completed.</source>

Compare with NULL value for non-equality

   <source lang="sql">

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    a     INTEGER;
 3    b     BOOLEAN;
 4    n     INTEGER;     --this will be our null value.
 5  BEGIN
 6    a := 2;
 7
 8    IF a <> n THEN
 9      DBMS_OUTPUT.PUT_LINE("a <> n is true");
10    ELSE
11      DBMS_OUTPUT.PUT_LINE("a <> n is not true");
12    END IF;
13  END;
14  /

a <> n is not true PL/SQL procedure successfully completed. SQL> SQL></source>

In PL/SQL the Boolean expression NULL=NULL evaluates to FALSE.

You need to wrap the selector in an NVL expression to be sure that it could never be NULL:

   <source lang="sql">

SQL> create or replace function f_getDateType (in_dt DATE)

 2  return VARCHAR2
 3  is
 4      v_out VARCHAR2(10);
 5  begin
 6      case nvl(to_char(in_dt,"d") , 0)
 7          when 0 then
 8          -- value will be null if in_dt is null
 9              v_out:="<NULL>";
10          when 1 then
11              v_out:="SUNDAY";
12          when 7 then
13              v_out:="SATURDAY";
14          else
15              v_out:="WEEKDAY";
16      end case;
17      return v_out;
18  end;
19  /

Function created. SQL> SQL></source>

Short-circuit evaluation with conditional structures.

PL/SQL stops evaluating the expression as soon as the result can be determined.

   <source lang="sql">

SQL> declare

 2      v_day NUMBER := TO_CHAR(TO_DATE("20060101","YYYYMMDD"),"D");
 3
 4      function f_DayNr return NUMBER is
 5      begin
 6          DBMS_OUTPUT.put_line("Called function");
 7          return v_day;
 8      end;
 9  begin
10      if f_DayNr in (1,7) or (f_DayNr not in (1,7) and (f_DayNr between 0 and 6 or f_DayNr between 19 and 23))
11      then
12          DBMS_OUTPUT.put_line(v_day || ": Off-peak");
13      else
14          DBMS_OUTPUT.put_line(v_day || ": Peak");
15      end if;
16  end;
17  /

Called function 1: Off-peak PL/SQL procedure successfully completed. SQL></source>

Use IS NULL in IF statement

   <source lang="sql">

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    a     INTEGER;
 3    b     BOOLEAN;
 4    n     INTEGER;     --this will be our null value.
 5  BEGIN
 6    a := 2;
 7
 8    IF (NOT (a = n)) IS NULL THEN
 9      DBMS_OUTPUT.PUT_LINE("NOT NULL = NULL");
10    END IF;
11  END;
12  /

NOT NULL = NULL PL/SQL procedure successfully completed. SQL></source>

Use NVL in IF statement to deal with the NULL value comparison

   <source lang="sql">

SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    a     INTEGER;
 3    b     BOOLEAN;
 4    n     INTEGER;     --this will be our null value.
 5  BEGIN
 6    a := 2;
 7
 8    IF NVL((a <> n),true) THEN
 9      DBMS_OUTPUT.PUT_LINE("The values are not equal.");
10    ELSE
11      DBMS_OUTPUT.PUT_LINE("The values are equal.");
12    END IF;
13  END;
14  /

The values are not equal. PL/SQL procedure successfully completed. SQL></source>

variable:=nvl(value1,value2);

If the value1 is not NULL, then return it; otherwise return the value2.

You can use expressions, variables, functions, and literals in NVL, as long as both variables are of the same datatype

   <source lang="sql">

SQL> declare

 2       v NUMBER;
 3  begin
 4       v:=nvl(v,0)+1;
 5       DBMS_OUTPUT.put_line("New value: <"||v||">");
 6  end;
 7  /

New value: <1> PL/SQL procedure successfully completed. SQL></source>