What is the usage of nvl function in oracle

In Oracle, the nvl() function is used to return a non-null value from two expressions. The syntax is "nvl(expression 1, expression 2)"; if the calculation result of expression 1 is If the value is null, the function returns the result of expression 2. If the calculation result of expression 1 is not null, the function returns the result of expression 1.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the usage of nvl function in oracle

Oracle’s Nvl function

nvl() function

Returns a non-null value from two expressions .

Syntax

NVL(eExpression1, eExpression2)

Parameters

eExpression1, eExpression2

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

Return value type

Character type, date type, date and time type, numerical type, currency type, logical type or null value

Description

In When null values ​​are not supported or do not matter, you can use NVL( ) to remove null values ​​from calculations or operations.

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

Note: The types of the two parameters must match

Q: What is NULL?

Answer: When we don’t know the specific data, that is, it is unknown, we can use NULL.

We call it empty. In ORACLE, the length of a table column containing null values ​​is zero.

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

1. Primary key field (primary key),

2. Already defined Fields with NOT NULL restrictions

Description:

1. It is equivalent to having no value and is an unknown number.

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

3. Perform operations such as addition, subtraction, multiplication, and division on null values, and the result will still be null.

4. Use the NVL function to handle NULL.

5. Use keywords "is null" and "is not null" when comparing.

6. Null values ​​cannot be indexed, so some data that meets the conditions may not be found when querying. In count(*), use nvl (column name, 0) to process it and then check again.

7. When sorting, it is larger than other data (the index is sorted in descending order by default, small → large), so NULL values ​​​​are always ranked last.

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of What is the usage of nvl function in oracle. For more information, please follow other related articles on the PHP Chinese website!