PL/SQL NVL

The NVL function is a built-in function in Oracle PL/SQL that allows you to substitute a value for a null value.
The PL/SQL NVL function replace a null expression with other expression. This can be useful when you want to ensure that all values in a column are not null, for example.

NVL syntax

The syntax of the NVL function is as follows:

NVL (expression_1 , expression_2)

where expression_1 is the value to be checked for null, and expression_2 is the value that will be used if expression_1 is null.

NVL example

 
select NVL(null, 1) from dual;

Result: 1


select NVL(null, 'my expression') from dual;

Result: 'my expression'

For example, say you have a column called “Status” in a table, and you want to make sure that all values in this column are not null.

You could use the following PL/SQL statement:

UPDATE table_name 
SET Status = NVL(Status, 'Unknown') 
WHERE Status IS NULL; 

This would update any rows where the Status column is null, and set the value to ‘Unknown’.

Another example, if you have a column called “Total” in a table, and you want to make sure that all values in this column are greater than or equal to 0, you could use the following SQL statement:

UPDATE table_name 
SET Total = NVL(Total, 0) 
WHERE Total < 0; 

This would update any rows where the Total column is less than 0, and set the value to 0.

You can also use the NVL function in a SELECT statement:

SELECT NVL(column_name, 'Unknown') 
FROM table_name; 

This would return 'Unknown' for any rows where the value in the column is null.

NVL example

For example, we have the STUDENTS table, in which we have the CITY column filled in with values and the ADDRESS column not filled in with values. In the example below, we use the Oracle PL/SQL NVL function so that when the ADDRESS column is null, it is filled with the value from the CITY column.

STUDENT_ID	FIRST_NAME	LAST_NAME	CITY	ADDRESS
1	Daniel	SCOTT	New York
2	Anthony	SIMMONS	Chicago
3	Sophia	THOMPSON	Los Angeles

select s.STUDENT_ID, s.FIRST_NAME, s.LAST_NAME, 
s.CITY, NVL(s.ADDRESS, s.CITY) AS STU_ADDRESS
from students s;

STUDENT_ID	FIRST_NAME	LAST_NAME	CITY	STU_ADDRESS
1	Daniel	SCOTT	New York	New York
2	Anthony	SIMMONS	Chicago	Chicago
3	Sophia	THOMPSON	Los Angeles	Los Angeles

The PL/SQL NVL function is a handy way to substitute values for null values in Oracle PL/SQL.