EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL nvl2

PL/SQL nvl2

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:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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:

PLSQL nvl2 1

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:

PLSQL nvl2 2

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:

PLSQL nvl2 3

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:

blank brackets ‘ ‘

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:

3 arguments are provided

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

We hope that this EDUCBA information on “PL/SQL nvl2” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Triggers in PL/SQL
  2. PL/SQL Collections
  3. PL/SQL Data Types
  4. Loops in PL/SQL
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Software Development Bundle3000+ Hours of HD Videos | 149 Learning Paths | 600+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more