oracle怎么写三元表达式,【DB笔试面试455】条件表达式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)ENDAS后跟别名，也可以去掉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程序员面试笔试宝典》，作者：李华荣。