I chose to write on this topic in order to increase the readability of programs and to maintain a standard way of validating values from within PL/SQL blocks, whether be it Anonymous blocks or Stored Procedures or Stored Functions or Packages. There has been always need for writing efficient piece of code before delivering the final one.
Validating NULL Values
NVL Function
This function is boon to programmers. I always chose to use this function because of its simplicity. In Oracle one must always remember that while validating with a NULL value the result you are going to get is also a NULL. So in order to avoid this it is better to use NVL() function.
Syntax:
NVL(variable1, variable2)
Example:
SELECT NVL(NULL, 'This is the output') null_test FROM dual;
Result:
null_test
This is the output
Both the parameters are required for NVL function to perform. You can use this function in WHERE clause so that you are not going to omit any NULL values from your query.
NVL2 Function
NVL2 function is an extension of NVL function that it can return two values, one when the variable to be tested is NULL and when the variable to be tested is NOT NULL.
Syntax:
NVL2(variable1, variable1_if_not_null, variable1_if_null)
Example 1:
SELECT NVL2(NULL,'NOT NULL','NULL') nvl2_test FROM dual;
Result:
nvl2_test
NULL
Example 2:
SELECT NVL2('some value','NOT NULL','NULL') nvl2_test FROM dual;
Result:
nvl2_test
NOT NULL
Validating NOT NULL Values
While validating NOT NULL values there is no need to use such functions as the value will be present already in the variable. But if the check is for whether the value is null or not then NVL2 function will be best suitable for the purpose.
