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> 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.


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.

Related Topic