Plsql_ Basic Series 6_ judgment Operation Nvl/nullif/coalesce/nvl2

2014-12-08 Baoxinjian

I. Summary

The following functions are available for any data type and are suitable for use with null values:
NVL (EXPR1, EXPR2)
NVL2 (Expr1, EXPR2, EXPR3)
Nullif (EXPR1, EXPR2)
COALESCE (EXPR1, expr2, ..., exprn)

Second, case-NVL

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

2. 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 value of the first parameter is displayed.

3. For example:

Sql>Select ENAME,NVL (Comm,-1)FromEmp Ename NVL (COMM,-1) —————— – ———— SMITH-1300ward 500jones - 1martin 1400blake - 1ford -1-1     

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

Third, case-NVL2

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

2. 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.

3. For example:

Sql>Select ENAME,NVL2 (Comm,-1,1)FromEmp Ename NVL2 (COMM,-1,1) —————— – ————— SMITH 1allen - 1ward -11martin -1blake 1clark 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.

Iv. Cases-Nullif

1. The format is as follows: Nullif (EXP1,EXPR2)

2. The implication is that the NULLIF function is to return null (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 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_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_CL ERK pu_mantaylor sa_rep sa_man sa_reptaylor sa_rep sa_repwhalen ad_asst ac_account ad_asstwhalen AD_ASST AD_ASST  pre>

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

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

V. Case-COALESCE

1. The format is as follows: Coalese (Expr1, expr2, EXPR3 ... exprn)

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

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

Plsql_ Basic Series 6_ judgment Operation Nvl/nullif/coalesce/nvl2