Nvl function usage, nvl Function

Nvl function usage, nvl Function

A nvl function is a null value conversion function.

NVL (expression 1, expression 2)

If expression 1 is null, NVL returns the value of expression 2. Otherwise, expression 1 is returned. The purpose of this function is to convert a null value into an actual value. The expression value can be numeric, numeric, or date. However, expression 1 and expression 2 must belong to the same data type.

For numeric type: NVL (comm, 0 );

For bytes NVL (TO_CHAR (comm), 'no Commission ')

For date NVL (hiredate, '31-dec-99 ')

 

Practical example:

Query the annual salary of an employee. If it is null, use 0 instead.

Select (sal + nvl (comm, o) * 12 from emp where emp. pno =: pno;

Here, comm is an undefined variable, representing the digit type. Nvl returns 0.

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

If expression 1 is null, 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 sal value is returned. If comm is not null, the value of sal + comm is returned.