条件表达式CASE和DECODE的区别是什么？

DB宝 2018-12-10

934

♣题目部分条件表达式CASE和DECODE的区别是什么？

     
♣答案部分


在SQL语句中使用IF-THEN-ELSE逻辑，可以使用两种方法：CASE表达式、DECODE函数。
1、CASE表达式
SQL中CASE的使用方法具有两种格式：简单CASE函数和CASE搜索函数。
简单CASE函数使用方式如下所示：
CASE SEX 
         WHEN '1' THEN '男' 
         WHEN '2' THEN '女' 
ELSE '其它' END AS "类别"
CASE搜索函数使用方式如下所示：
CASE WHEN SEX = '1' THEN '男' 
      WHEN SEX = '2' THEN '女' 
ELSE '其它' END AS "类别" 
以上两种方式可以实现相同的功能。简单CASE函数的写法相对比较简洁，但是和CASE搜索函数相比，功能方面会有些限制，例如编写判断式，下面的例子无法使用简单CASE函数来实现：
CASE WHEN SEX = '1' AND AGE>60 THEN  '爷爷'
      WHEN SEX = '2' AND AGE>60 THEN '奶奶' 
ELSE '其它' END AS "类别"
需要注意的是，CASE函数只返回第一个符合条件的值，剩下的CASE部分将会被自动忽略。例如下面的SQL语句，永远无法得到“第二类”这个结果。
CASE WHEN COL_1 IN ( 'A', 'B') THEN '第一类' 
         WHEN COL_1 IN ('A')       THEN '第二类' 
ELSE '其它' END AS "类别"
CASE表达式可以在SQL中实现IF-THEN-ELSE型的逻辑，而不必使用PL/SQL。CASE的工作方式与DECODE类似，但推荐使用CASE，因为它与ANSI兼容。
对于CASE表达式，需要注意以下几点内容：
（1）以CASE开头，以END结尾。
（2）分支中WHEN后跟条件，THEN为显示结果。
（3）ELSE为除此之外的默认情况，类似于高级语言程序中SWITCH CASE的DEFAULT，可以不加。
（4）END AS后跟别名，也可以去掉AS。
2、DECODE函数
DECODE的语法如下所示：
DECODE(VALUE,IF1,THEN1,IF2,THEN2,IF3,THEN3,...,ELSE)，表示如果VALUE等于IF1，那么DECODE函数的结果返回THEN1，...，如果不等于任何一个IF值，那么返回空。
在使用DECODE函数时，需要注意以下几点内容：
（1）Oracle在调用DECODE函数的时候，需要预先确定列的类型。
（2）确定DECODE返回值类型，是依据参数中第一个条件返回类型，之后所有的返回类型都依据第一个类型进行强制类型转换。
（3）当Oracle在第一个条件返回类型为NULL的时候，默认将其作为字符串处理。
例如，下面的例子中，DECODE函数的返回值以SAL列为标准，即为数值型，而7499的返回值为字符串，所以，会报错：
SYS@lhrdb> SELECT * FROM SCOTT.EMP M WHERE M.EMPNO IN (7369, 7499);
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
如下的SQL语句会报错（ORA-01722: invalid number）：
SELECT DECODE(M.EMPNO, 7369, M.SAL, 7499, M.JOB)
  FROM SCOTT.EMP M
 WHERE M.EMPNO IN (7369, 7499);
若修改为如下形式，将SAL的列变为字符串就可以正常运行了。
SELECT DECODE(M.EMPNO, 7369, M.SAL||'', 7499, M.JOB)
  FROM SCOTT.EMP M
 WHERE M.EMPNO IN (7369, 7499);
 

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。