Oracle PL/SQL - NVL for NULL value

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. Simple CASE Expression

Introduction

Oracle provides a very useful function NVL to deal with NULL value: 

variable:=nvl(value1,value2);

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.

Demo

SQL>
SQL> declare-- from w  w w. ja  va2  s .  c  o  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.

Related Topic