PLSQL _ basic series 6_determine the operation NVL/NULLIF/COALESCE/NVL2, plsqlnvl

PLSQL _ basic series 6_determine the operation NVL/NULLIF/COALESCE/NVL2, plsqlnvl

BaoXinjian

I. Summary

The following functions are applicable to any data type and use null values:
NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2,..., exprn)

 

Ii. Case study-NVL

1. The NVL function format is as follows: NVL (expr1, expr2)

2. Meaning: if the first oracle parameter is blank, the value of the second parameter is displayed. If the value of the first parameter is not blank, the original value of the first parameter is displayed.

3. For example:

SQL> select ename,NVL(comm, -1) from emp;ENAME NVL(COMM,-1)——————– ————SMITH -1ALLEN 300WARD 500JONES -1MARTIN 1400BLAKE -1FORD -1MILLER -1

The original values of-1 are all null values.

 

Iii. Case study-NVL2

 

1. NVL2 Function Format: NVL2 (expr1, expr2, expr3)

2. Meaning: if the first parameter of the function is null, the value of the second parameter is displayed. If the value of the first parameter is not empty, the value of the third parameter is displayed.

3. For example:

SQL> select ename,NVL2(comm,-1,1) from emp;ENAME NVL2(COMM,-1,1)——————– —————SMITH 1ALLEN -1WARD -1JONES 1MARTIN -1BLAKE 1CLARK 1SCOTT 1

In the above example. If the result is 1, it is not null, but the original value of-1 is null.

 

Iv. Case study-NULLIF

1. The format is as follows: NULLIF (exp1, expr2)

2. Meaning: The NULLIF function is used to return NULL if exp1 and exp2 are equal. Otherwise, the first value is returned.

3. For example:

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 jWHERE e.employee_id = j.employee_idORDER BY last_name;
LAST_NAME JOB_ID JOB_ID Old Job ID————————————————– ——————– ——————– ——————–De Haan AD_VP IT_PROG AD_VPHartstein MK_MAN MK_REP MK_MANKaufling ST_MAN ST_CLERK ST_MANKochhar AD_VP AC_MGR AD_VPKochhar AD_VP AC_ACCOUNT AD_VPRaphaely PU_MAN ST_CLERK PU_MANTaylor SA_REP SA_MAN SA_REPTaylor SA_REP SA_REPWhalen AD_ASST AC_ACCOUNT AD_ASSTWhalen AD_ASST AD_ASST

We can see all the employees. If job_id and job_histroy.job_id are equal, NULL is output in the result, otherwise the result is displayed as employee. Job_id

The role here is to show the original work of those who have changed their jobs.

 

V. Case study-COALESCE

1. The format is as follows: COALESE (expr1, expr2, expr3 ..... Exprn)

2. Meaning: The Role of the COALESE function is that the NVL function is a bit similar. Its advantage is that there are more options.

 

Reference: http://www.cnblogs.com/lzhdim/archive/2008/12/24/1361391.html