NVL Function is used if there are null values. : NVL « Conversion Functions « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Conversion Functions
3. NVL

1. NVL function returns a substitute if a value is null.
2. NVL function takes two arguments.
3. The first argument is the field or attribute to check.
4. The second argument is the value that you want to replace the null value by.
5. For example, "NVL(value, 10)": we are looking for null values in the "value" column, and would like to replace the null value in the "value" column by 10.
6. NVL does not change the actual data in the table.

SQL>
SQL> -- create demo table
SQL> create table myTable(
  2    id           NUMBER(2),
  3    value        NUMBER(6,2)
  4  )
  5  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into myTable(ID,  value)values (1,9)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (2,2.11)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (3,3.44)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (4,NULL)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (5,10)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (6,3)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (7,-5.88)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (8,123.45)
  2  /

1 row created.

SQL> insert into myTable(ID,  value)values (9,98.23)
  2  /

1 row created.

SQL>
SQL> select * from myTable
  2  /

        ID      VALUE
---------- ----------
         1          9
         2       2.11
         3       3.44
         4
         5         10
         6          3
         7      -5.88
         8     123.45
         9      98.23

9 rows selected.

SQL>
SQL> SELECT id, NVL(value, 10) From myTable
  2  /

        ID NVL(VALUE,10)
---------- -------------
         1             9
         2          2.11
         3          3.44
         4            10
         5            10
         6             3
         7         -5.88
         8        123.45
         9         98.23

9 rows selected.

SQL>
SQL> -- clean the table
SQL> drop table myTable
  2  /

Table dropped.

SQL>