NVL Functions in Oracle

Reprinted from: http://blog.sina.com.cn/s/blog_46e9573c01015ik8.html

The functions previously described in Oracle are string processing, date functions, mathematical functions, conversion functions, and so on, and a class of functions are general functions. Mainly: NVL,NVL2,NULLIF,COALESCE, these functions can be used on all types.

The following is a brief introduction to the use of several functions.

Before you introduce this, you have to understand what a null value in Oracle is NULL

1.NVL function

The format of the NVL function is as follows: NVL (EXPR1,EXPR2)

The meaning is: if the first parameter of Oracle is empty then the value of the second parameter is displayed, and if the value of the first parameter is not NULL, the first parameter is displayed.

For example:

Sql> Select ENAME,NVL (Comm,-1) from EMP;

Ename NVL (comm,-1)

------- ----

SMITH-1

ALLEN 300

WARD 500

JONES-1

MARTIN 1400

BLAKE-1

FORD-1

MILLER-1

Which shows that 1 of the original values are all null values

2 NVL2 function

The format of the NVL2 function is as follows: NVL2 (EXPR1,EXPR2, EXPR3)

The meaning is: if the first argument of the function is empty then the value of the second parameter is displayed, and if the value of the first parameter is not NULL, the value of the third parameter is displayed. Sql> Select ENAME,NVL2 (comm,-1,1) from EMP;

Ename NVL2 (comm,-1,1)

------- -----

SMITH 1

ALLEN-1

WARD-1

JONES 1

MARTIN-1

BLAKE 1

CLARK 1

SCOTT 1

In the example above. The result is 1 of the original is not empty, and the result is-1 of the original value is empty.

3. Nullif function

The effect of the Nullif (EXP1,EXPR2) function is to return null (NULL) if EXP1 and exp2 are equal, otherwise the first value is returned.
Here is an example. Using the HR schema in Oracle, if HR is locked, enable

The role here is to show the people who have changed their jobs and work.

Sql> Select E.last_name,e.job_id,j.job_id,nullif (e.job_id, j.job_id) "Old Job ID"

From Employees E, Job_history J

WHERE e.employee_id = j.employee_id

ORDER by Last_Name;

Last_Name job_id job_id Old JOB ID

----------------- ------- ------- -------

De Haan AD_VP It_prog AD_VP

Hartstein Mk_man Mk_rep Mk_man

Kaufling St_man St_clerk St_man

Kochhar AD_VP ac_mgr AD_VP

Kochhar AD_VP Ac_account AD_VP

raphaely Pu_man St_clerk Pu_man

Taylor Sa_rep Sa_man Sa_rep

Taylor Sa_rep Sa_rep

Whalen Ad_asst Ac_account Ad_asst

Whalen Ad_asst Ad_asst

You can see all the employee. job_id and job_histroy.job_id are equal, the output null in the result is empty, otherwise the employee is displayed. job_id

4.Coalesce function

The function of the Coalese function is somewhat similar to the functions of NVL, and its advantage is that there are more options.

The format is as follows:

COALESCE (Expr1, EXPR2, EXPR3.....EXPRN)

Represents a placeholder that can specify multiple expressions. All expressions must be of the same type, or can be implicitly converted to the same type.
Returns the first non-empty expression in the expression, such as the following statement: SELECT COALESCE (null,null,3,4,5) from dual its return result is: 3
If all arguments are NULL, COALESCE returns a null value. COALESCE (expression1,... N) is equivalent to this case function:
This function is actually a cyclic use of NVL, and this is not an example.

NVL Functions in Oracle