Oracle_nvl、nvl2、nullif、coalesce、decode

mjtvpkzuxf

　　ref: http://blog.csdn.net/xiangsir/article/details/8601513
　　
　　NVL(EXPR1，EXPR2)
　　NVL2(EXPR1，EXPR2，EXPR3)
　　NULLIF(EXPR1，EXPR2)
　　COALESCE(EXPR1,,..,EXPRn)
　　decode
　　
　　
　　--------NVL
　　nvl(COMMISSION_PCT,0)
　　如果第一个参数为null，则返回第二个参数
　　如果第一个参数为非null，则返回第一个参数
　　
　　经典示例：计算年薪（工资+提成）
　　SELECT LAST_NAME,SALARY,NVL(COMMISSION_PCT,0),
　　(SALARY*12)+(SALARY*12*NVL(COMMISSION_PCT,0)) ANNUAL_SALARY
　　FROM EMPLOYEES
　　WHERE LAST_NAME='Matos';
　　
　　--------NVL2
　　NVL2(COMMISSION_PCT,'SAL_COMM','SAL')
　　如果第一个参数为null，则返回第三个参数
　　如果第一个参数为非null，则返回第二个参数
　　
　　经典示例：
　　SELECT LAST_NAME,SALARY,COMMISSION_PCT,
　　NVL2(COMMISSION_PCT,'SAL+COMM','SAL') INCOME
　　FROM EMPLOYEES
　　WHERE DEPARTMENT_ID IN (50,80);
　　
　　---------NULLIF
　　nullif(length(first_name),length(last_name))
　　如果两个参数值相等，则返回 null
　　如果两个参数不等，则返回第一个表达式值
　　
　　
　　SELECT FIRST_NAME ,LENGTH(FIRST_NAME) "EXPR1",
　　LAST_NAME, LENGTH(LAST_NAME) "expr2",
　　nullif(length(first_name),length(last_name)) result
　　FROM EMPLOYEES;
　　
　　---------COALESCE
　　COALESCE(EXPR1,EXPR2,EXPR3...EXPRn)
　　从左往右数，遇到第一个非null值，则返回该非null值。
　　多层判断
　　
　　
　　SELECT LAST_NAME,EMPLOYEE_ID,
　　COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id),
　　'No commission and no manager')
　　FROM EMPLOYEES;
　　
　　
　　-----------条件表达式
　　IF-THEN-ELSE
　　
　　
　　CASE 语句(SQL标准，编写繁杂)
　　DECODE 函数
　　DECODE(COL|EXPRESSION,SERACH1,RESULT1
　　[,search2,result2,...,]
　　[,default])
　　如果 search1 = expression 则返回 result1  
　　如果 search2 = expression 则返回 result2 
　　---------CASE
　　示例：
　　SELECT LAST_NAME,JOB_ID,SALARY,
　　CASE JOB_ID WHEN 'IT_PROG' THEN 1.10*SALARY
　　WHEN 'ST_CLERK' THEN 1.15*SALARY
　　WHEN 'SA_REP' THEN 1.20*SALARY
　　ELSE SALARY END "REVISED SALARY"
　　FROM EMPLOYEES;
　　
　　
　　---------DECODE
　　示例:
　　DECODE函数的作用：它可以将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。 
　　区别于SQL的其它函数，DECODE函数还能识别和操作空值。 
　　语法如下： 
　　DECODE(control_value,value1,result1[,value2,result2…][,default_result]); 
　　control _value 试图处理的数值。DECODE函数将该数值与后面的一系列的偶序相比较，以决定返回值。 
　　value1 是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应 
　　result1 是一组成序偶的结果值。 
　　default_result 未能与任何一个值匹配时，函数返回的默认值。 
　　
　　示例如下： select decode( x , 1 , ‘x is 1 ’, 2 , ‘x is 2 ’, ‘others’) from dual 
　　
　　ref: http://blog.iyunv.com/uid-22948773-id-3056294.html
　　
　　NVL,NVL2,COALESCE,DECODE,CASE  这几个函数或者表达式都有一定的共性，用于将NULL替换为某个值或者替换某个值为另一个值。
　　但也有一定的区别，如下：
　　
　　SQL> SELECT NVL(1,1/0) FROM DUAL;
　　SELECT NVL(1,1/0) FROM DUAL
　　*
　　第 1 行出现错误:
　　ORA-01476: divisor is equal to zero
　　
　　SQL> SELECT NVL(NULL,1/0) FROM DUAL;
　　SELECT NVL(NULL,1/0) FROM DUAL
　　*
　　第 1 行出现错误:
　　ORA-01476: divisor is equal to zero
　　
　　SQL> SELECT NVL2(NULL,1,2) FROM DUAL;
　　NVL2(NULL,1,2)
　　--------------
　　2
　　SQL> SELECT NVL2(NULL,1/0,2) FROM DUAL;
　　SELECT NVL2(NULL,1/0,2) FROM DUAL
　　*
　　第 1 行出现错误:
　　ORA-01476: divisor is equal to zero
　　
　　SQL> SELECT NVL2(1,1,1/0) FROM DUAL;
　　SELECT NVL2(1,1,1/0) FROM DUAL
　　*
　　第 1 行出现错误:
　　ORA-01476: divisor is equal to zero
　　
　　在任何时候只要NVL函数中的值触发异常，就会报错，即使没有执行，NVL2函数类似。
　　
　　而COALESCE,DECODE,和CASE表达式只会执行到这个表达式才会报错。
　　SQL> SELECT COALESCE(NULL,1/0) FROM DUAL;
　　SELECT COALESCE(NULL,1/0) FROM DUAL
　　*
　　第 1 行出现错误:
　　ORA-01476: divisor is equal to zero
　　
　　SQL> SELECT COALESCE(1,1/0) FROM DUAL;--没有执行1/0到不会报错
　　COALESCE(1,1/0)
　　---------------
　　1
　　SQL> SELECT CASE 1 WHEN 1 THEN 1 ELSE 1/0 END FROM DUAL;  --没有执行1/0到不会报错
　　CASE1WHEN1THEN1ELSE1/0END
　　-------------------------
　　1
　　SQL> SELECT CASE 0 WHEN 1 THEN 1 ELSE 1/0 END FROM DUAL;
　　SELECT CASE 0 WHEN 1 THEN 1 ELSE 1/0 END FROM DUAL
　　*
　　第 1 行出现错误:
　　ORA-01476: divisor is equal to zero
　　
　　SQL> SELECT DECODE(1,1,1,1/0) FROM DUAL; --没有执行1/0到不会报错
　　DECODE(1,1,1,1/0)
　　-----------------
　　1
　　SQL> SELECT DECODE(1,0,1,1/0) FROM DUAL;
　　SELECT DECODE(1,0,1,1/0) FROM DUAL
　　*
　　第 1 行出现错误:
　　ORA-01476: divisor is equal to zero