Oracle PL/SQL/PL SQL/Null Value

Материал из SQL эксперт
Перейти к: навигация, поиск

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>