Introduction to the use of nvl function of oracle

Nvl Function of Oracle

nvl () function

Returns 1 non-null value from two expressions.

Grammar

NVL(eExpression1, eExpression2)

Parameter

eExpression1, eExpression2

If eExpression1 evaluates to an null value, NVL () returns eExpression2. If eExpression1 evaluates to a value other than null, eExpression1 is returned. eExpression1 and eExpression2 can be any one data type. NVL () returns. NULL if the results of eExpression1 and eExpression2 are both null values.

Return value type

Character, date, datetime, numeric, monetary, logical, or null values

Description

NVL () can be used to remove the null value in calculation or operation when the null value is not supported or the null value is irrelevant.

select nvl (a. name, 'Empty') as name from a joinschool b on a. ID=b. ID

Note: The types of the two parameters should match

Q: What is NULL?

A: When we don't know exactly what data we have, that is, we can use NULL.

We call it null, and in ORACLE, the length of table columns with null values is zero.

ORACLE allows fields of any 1 data type to be empty, except for the following two cases:

1. Primary key field (primary key),

2. Fields that have been defined with NOT NULL restrictions

Description:

1, which is equivalent to having no value, is unknown.

2. NULL is different from 0, empty string and space.

3. Add, subtract, multiply and divide null values, and the result is still null.

4. NULL is processed using NVL function.

5. The keywords used in comparison are "is null" and "is not null".

6. Null values cannot be indexed, so some qualified data may not be found when querying.

In count (*), it is processed with nvl (column name, 0) and then checked.

7. When sorting, it is larger than other data (the index is arranged in descending order by default, small → large).

So the NULL value always comes last.