NVL function Usage

A NVL function is a null-value conversion function

NVL (expression 1, expression 2)

If expression 1 is a null value, NVL returns the value of expression 2, otherwise returns the value of expression 1. The purpose of this function is to convert a null value (NULL) into an actual value. The value of its expression can be numeric, character, and date. But the data type of expression 1 and expression 2 must be the same type.

For digital type: NVL (comm,0);

For character type NVL (To_char (comm), ' No Commission ')

For date type NVL (HireDate, ' 31-dec-99 ')

Practical Examples:

Query for an employee's annual salary, if empty, replace it with 0

Select (SAL+NVL (comm,o)) *12 from EMP where emp.pno=:p no;

Here, Comm is an undefined variable that refers to a numeric type. NVL returns to 0.

Two NVL2 (expression 1, expression 2, expression 3)

If expression 1 is empty, the return value is the value of expression 3. If expression 1 is not empty, the return value is the value of expression 2.

For example NVL2 (comm, ' Sal+comm ', Sal)

NVL2 function Test Comm

If comm is empty, the value of the SAL is returned. If Comm is not empty (null), the value of the expression Sal+comm is returned.

NVL function Usage