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.
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.
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.
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.
select s.STUDENT_ID, s.FIRST_NAME, s.LAST_NAME, s.CITY, NVL(s.ADDRESS, s.CITY) AS STU_ADDRESS from students s;
|1||Daniel||SCOTT||New York||New York|
|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.