Use of Oracle NVL, NVL2, NULLIF, coalesce functions __ static functions

In Oracle functions in addition to character functions, date functions, mathematical functions, and conversion functions, and so on, there are some functions are common functions, such as: NVL, NVL2, Nullif, coalesce.
Let's take a look at their usage:

1.NVL function
NVL (EXPR1,EXPR2)

If the data type of the EXPR1 and Expr2 is the same:
If the expr1 is empty (null), then the EXPR2 is displayed,
If the value of the EXPR1 is not empty, the expr1 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
CLARK-1
SCOTT-1
KING-1
TURNER 0
ADAMS-1
JAMES-1
FORD-1
MILLER-1

Rows selected.

When two parameter data types are different, Oracle converts two parameters implicitly, and if an implicit conversion is not possible, the implicit conversion rules are as follows:
1. If parameter 1 is a character type, the parameter 2 is converted to the type of parameter 1, and the return value is VARCHAR2
2. If parameter 1 is a numeric value, the highest numeric precedence of two parameters is judged (for example, if the double-precision real number is higher than the single one), and then converted to a higher-priority value, the type is returned.

--The first parameter is number type, the second parameter is varchar type, cannot be implicitly converted, error.
Sql> Select ENAME,NVL (Comm, ' no comm ') from EMP;
Select ENAME,NVL (Comm, ' no comm ') from EMP
*
ERROR at line 1:
Ora-01722:invalid number


--Converts the first argument to a character type and the second parameter is the number type, then the second parameter automatically converts to a character type and returns a string.
Sql> Select ENAME,NVL (Comm | | ", -1) from EMP;

Ename NVL (comm| | ",-1)
-------------------- -------------------------
SMITH-1
ALLEN 300
WARD 500
JONES-1
MARTIN 1400
BLAKE-1
CLARK-1
SCOTT-1
KING-1
TURNER 0
ADAMS-1
JAMES-1
FORD-1
MILLER-1

Rows selected.

2.nvl2 function
NVL2 (EXPR1,EXPR2, EXPR3)

If EXPR1 is not NULL, return EXPR2 EXPR1 null and return EXPR3.
EXPR2 and EXPR3 types are different, EXPR3 will be converted to EXPR2 type, not converted, then the error.

Sql> Select ENAME,NVL2 (comm,comm,-1) from EMP;

Ename NVL2 (comm,comm,-1)
-------------------- ------------------
SMITH-1
ALLEN 300
WARD 500
JONES-1
MARTIN 1400
BLAKE-1
CLARK-1
SCOTT-1
KING-1
TURNER 0
ADAMS-1
JAMES-1
FORD-1
MILLER-1

Rows selected.

3. Nullif function
Nullif (EXPR1,EXPR2)
Returns null (NULL) if EXPR1 and expr2 are equal, or returns EXPR1.

--show that those who have changed jobs are working, working, not changing jobs, and showing null.
Sql> Col last_name for A15
Sql> Select E.last_name, E.job_id,j.job_id,nullif (e.job_id, j.job_id) "Old Job ID"
2 from Employees E, Job_history J
3 WHERE e.employee_id = j.employee_id
4 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_rep
Taylor Sa_rep Sa_man Sa_rep
Whalen Ad_asst Ac_account Ad_asst
Whalen Ad_asst Ad_asst

Ten rows selected.

4.coalesce function
COALESCE (Expr1, expr2, EXPR3 ... exprn)
Returns the first non-empty expression in an expression, or null if all is null.
All expressions must be of the same type, or they can be implicitly converted to the same type, otherwise an error is present.

The Coalese function is similar to the NVL function, except that the options are more.
-
-Here, the effect is the same as the NVL.
Sql> Select Ename,coalesce (comm,-1) from EMP;

ename COALESCE (comm,-1)
-------------------- -----------------
SMITH-1
ALLEN 300
WARD 500
JONES-1
MARTIN 1400
BLAKE-1
CLARK-1
SCOTT-1
KING-1
TURNER 0
ADAMS-1
JAMES-1
FORD-1
MILLER-1

Rows selected.

Sql> Select Ename,coalesce (comm,null,-2,-5) from EMP;

ename COALESCE (comm,null,-2,-5)
-------------------- -------------------------
SMITH-2
ALLEN 300
WARD 500
JONES-2
MARTIN 1400
BLAKE-2
CLARK-2
SCOTT-2
KING-2
TURNER 0
ADAMS-2
JAMES-2
FORD-2
MILLER-2

Rows selected.