Difference Between NVL and NVL2
Why are we using NVL and NVL2 functions ?
Any arithmetic operation performed on NULL, Again it will become NULL. To overcome this problem oracle introduced NVL and NVL2 functions.
Example : NULL + 50 = NULL
As per the above example, System returns NULL.
NVL( ) :-
NVL is an pre-defined function which is used to replace (or) substitute user defined values in place of NULL value.
Syntax: NVL( Value1, Value2)
Here Value1 and value2 must be belongs to the same datatype.
If Value1 is NULL then NVL function returns Value2 other wise it returns Value1.
Example: 1) NVL( null, 20) Output: 20
2) NVL(30, 20) Output: 30
Below is the real time example to use NVL( ) functions.
SQL> Select EmployeeName, Salary, Increment, salary+NVL(Increment,0) from Employee;
So if we sum/add the salary and Increment without NVL function Then system will return NULL value. So we have to careful while addition with NULL values.
If the increment is NULL then the above query returns 0 in place of NULL .
Example: SAL + NVL(increment, 0)
10000 + NVL(NULL, 0)
10000 + 0
Final Output:10000.
In the same way, We can replace NULL with different values.
NVL2 ( ):
It is also substitute (or) replace user-defined value in place of NULL. This is introduced in oracle 9i version and it accepts three parameters.
Syntax: NVL2( Value1, Value2, Value3)
In this case, If Value1 is NULL then it returns Value 3. Otherwise it returns Value2.
Example : NVL( null, 10, 20) Output: 20
NVL( 50, 10, 20) Output: 10.
Question)Assume, We have a requirement that If increment is NULL then update increment to 1000.
And the same way if Increment is NOT NULL then update increment as increment+500.
Solution:
SQL> update employee set increment= NVL2(increment, increment+500,1000);
So once we run the update command, If increment is NULL then system updates the increment as 1000. Otherwise it updates increment+500.
If any queries, Please drop a comment below.
Thank You
Comments
Post a Comment