PL/SQL nvl2

Introduction to PL/SQL nvl2

In PL/SQL, nvl2() function is used to substitute the provided value or expression on the basis of null and not null value. It is basically an extension of the nvl() function of PL/SQL which takes 2 arguments. Function nvl2() takes 3 arguments, i.e. the original string or expression, other two being the substituted value based on the original string being null or not. Oracle provides the flexibility that the original string or expression can be of any data type and the substituted strings, i.e. other 2 arguments can also be of any data type except ‘LONG’. Oracle also performs specific actions if the 2 arguments except the original string are of different data type.

Syntax:

Below given is the basic syntax of using the nvl2 function in a PL/SQL block:

NVL2 (expr1, expr2, expr3)

Where,

• expr1: It is the string or the expression which is to be tested in the function nvl2.
• expr2: It is the value which is to be returned as a result of the function if the expr1 is not null.
• expr3: It is the value which is to be returned as a result of the function if the expr1 is null.

Return Type:

• Function NVL2 returns the substituted value based on the string or expression provided.

How does nvl2 Work in PL/SQL?

Important points related to the working of nvl2 function in PL/SQL are given below:

1. Function nvl2 in PL/SQL extends the functionality of nvl function provided by Oracle.

2. Function nvl2 basically decides the value to be returned from the query based on the null and not null value.

3. It takes 3 parameters, first being the original string or expression, second being the substituted string if the provided expression is not null and third being the substituted string if the expression is null.

4. Original string ‘expr1’ can be of any data type.

5. Parameters ‘expr2’ and ‘expr3’ also can be of any data type except long.

6. In case when the ‘expr2’ and ‘expr3’ are of different data types:

• If ‘expr2’ is of numeric data type, Oracle implicitly checks which argument is of high precedence data type and then converts either expr2 or expr3 to high precedence data type and returns that data type.
• If ‘expr2’ is a character data type, Oracle before comparing, converts the ‘expr3’ to a data type of ‘expr2’ and returns the VARCHAR2 in character set of ‘expr2’. Conversion of data type does not takes place if ‘expr3’ is a null constant.

Following versions of Oracle PL/SQL supports the nvl2() function:

• Oracle 12c
• Oracle 11g
• Oracle 10a
• Oracle 9i
• Oracle 8i

Examples of PL/SQL nvl2

Given below are the examples mentioned:

Example #1

Code:

SELECT
NVL2(NULL, 10, 98) as New_value
FROM
dual;

Output:

Explanation:

• In the above example, 3 arguments are provided in the nvl2() function. The first argument is ‘NULL’ which is a null value. So, according to the nvl2() function property, it will return expr3 if the provided expression is NULL.
• So, in the above function ‘expr3’ is ‘98’. Hence ‘98’ is printed in the column ‘new_value’ on the console to the user.

Example #2

Code:

SELECT
NVL2('hello', 10, 98) as New_value
FROM
dual;

Output:

Explanation:

• In the above example, 3 arguments are provided in the nvl2() function. The first argument is ‘hello’ which is a character data type and is not null. So, according to the nvl2() function property, it will return expr2 provided in the function which is ‘10’ in the above example.
• Hence ‘10’ is printed in the column ‘new_value’ on the console to the user.

Example #3

Code:

SELECT
NVL2(100, 'hello', 'hi') as string_value
FROM
dual;

Output:

Explanation:

• In the above example, 3 arguments are provided in the nvl2() function. The first argument is 100 which is a numeric data type and is not null. So, according to the nvl2() function property, it will return expr2 provided in the function which is ‘hello’ in the above example.
• Hence ‘hello’ is printed in the column ‘string_value’ on the console to the user.

Example #4

Code:

SELECT
NVL2('', 'this is not null value', 'this is a null value') as string_value
FROM
dual;

Output:

Explanation:

• In the above example, 3 arguments are provided in the nvl2() function. The first argument is the null value which is represented as blank brackets ‘ ‘. So, according to the nvl2() function property, it will return expr3 provided in the function if the expression ot the original string is null. So expr3 is ‘this is a null value’ in the above example.
• Hence ‘this is a null value’ is printed in the column ‘string_value’ on the console to the user.

Example#5

Code:

SELECT
NVL2('hello hello', 'this is for not null value', '34') as string_value
FROM
dual;

Output:

Explanation:

• In the above example, 3 arguments are provided in the nvl2() function. The first argument is the ‘hello hello’ which is not a null value. As we can see above, that both the expr2 and expr3 are of different data types, so the Oracle performs the implicit conversions and according to that appropriate action would be taken. So, according to the nvl2() function property, it will return expr2 provided in the function if the expression ot the original string is not null. So expr2 is ‘this is for not null value’ in the above example.
• Hence ‘this is for not null value’ is printed in the column ‘string_value’ on the console to the user.

Conclusion

Above description clearly explains what the nvl2 function is and how it works in a PL/SQL block. For a programmer, working on real databases, it is important to understand the concept of null and not null values and how to deal with them according to the resulting data from the tables in various situations clearly. Data represented to the user is according to the requirements of the user and showing null values is not an appropriate choice in many cases. So this function helps to deal with such situations.

Recommended Articles

This is a guide to PL/SQL nvl2. Here we discuss the introduction, how does nvl2 work in PL/SQL and examples for better understanding. You may also have a look at the following articles to learn more –

1. Triggers in PL/SQL
2. PL/SQL Collections
3. PL/SQL Data Types
4. Loops in PL/SQL