Oracle provides a very useful function NVL to deal with NULL value:
If the first value is not NULL, then return it; otherwise return the second value.
You can use expressions, variables, functions, and literals in NVL.
SQL> SQL> declare-- from w w w.j a va2 s. co m 2 v_nr NUMBER; 3 begin 4 v_nr := nvl( v_nr , 0 ) + 1; 5 DBMS_OUTPUT.put_line('New value: <'||v_nr||'>'); 6 end; 7 / New value: <1> PL/SQL procedure successfully completed. SQL>
Here, the NVL function checks to see whether the value of v_nr is NULL.
Because it is NULL, returns 0(the second value).
Because NULL is no longer involved in the addition operation, a result is returned.