Oracle PL/SQL/PL SQL/Null Value
Assign value not null a null value
<source lang="sql">
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>
</source>
Is Null for a boolean expression
<source lang="sql">
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>
</source>
NVL deals with a boolean expression
<source lang="sql">
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>
</source>