Oracle PL/SQL/PL SQL/Null Value

Assign value not null a null value

 
SQL>
SQL>  declare
  2      myNumber number not null := 1;
  3      another_number number;
  4    begin
  5      myNumber := 2;
  6      myNumber := another_number;
  7    end;
  8    /
 declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 6

SQL>

Is Null for a boolean expression

SQL>
SQL> --  Effects of nulls on boolean expressions.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     a     INTEGER;
  3     n     INTEGER;     -- be our null value.
  4  BEGIN
  5     -- Assign a value to the variable A, but leave N null.
  6     a := 2;
  7     -- NOT NULL = NULL
  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>

NVL deals with a boolean expression

SQL>
SQL> --  Effects of nulls on boolean expressions.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     a     INTEGER;
  3     n     INTEGER;     -- be our null value.
  4  BEGIN
  5     -- Assign a value to the variable A, but leave N null.
  6     a := 2;
  7     -- TIP: try this if you want a null value to be considered "not equal".
  8     -- Be careful though, if BOTH A and N are NULL NVL will still return TRUE.
  9     IF NVL((a <> n),true) THEN
 10       DBMS_OUTPUT.PUT_LINE("The values are not equal.");
 11     ELSE
 12       DBMS_OUTPUT.PUT_LINE("The values are equal.");
 13     END IF;
 14     -- TIP: a three-valued IF construct.
 15  END;
 16  /
The values are not equal.
PL/SQL procedure successfully completed.
SQL>