NVL,NVL2,COALESCE,DECODE,CASE表达式的一点小区别

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