sql - CASE 语句和 DECODE 是否等效？

Question

看起来简单的 CASE 表达式和 DECODE 函数是等效的，它们返回的结果应该是相同的。他们是吗？

文档有以下关于 simple CASE expression 的说明:

The simple CASE expression returns the first result for which selector_value matches selector. Remaining expressions are not evaluated. If no selector_value matches selector, the CASE expression returns else_result if it exists and NULL otherwise.

将此与 DECODE function 进行比较，描述似乎是相同的。

DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

如 searched CASE expression可以等同于简单，这也可以解释为相同。

这三个语句似乎都返回相同的结果 0。

select case 1 when 2 then null else 0 end as simple_case
     , case when 1 = 2 then null else 0 end as searched_case
     , decode(1, 2, null, 0) as decode
  from dual

简单的 CASE 表达式和 DECODE 函数(以及在特定情况下搜索的 CASE 表达式)是否总是返回相同的结果？

Answer 1

简短的回答，没有。

稍长的答案几乎是。

看起来只是从每个语句中获得的结果是相同的。如果我们使用 DUMP函数来评估返回的数据类型，你会明白我的意思:

SQL> select dump(case 1 when 2 then null else 0 end) as simple_case
  2       , dump(case when 1 = 2 then null else 0 end) as searched_case
  3       , dump(decode(1, 2, null, 0)) as decode
  4    from dual;

SIMPLE_CASE        SEARCHED_CASE      DECODE
------------------ ------------------ -----------------
Typ=2 Len=1: 128   Typ=2 Len=1: 128   Typ=1 Len=1: 48

SQL Fiddle

您可以看到 DECODE 的数据类型为 1，而两个 CASE 语句“返回”数据类型为 2。使用 Oracle 的 Data Type Summary , DECODE 返回 VARCHAR2(数据类型 1)，而 CASE 语句“返回”数字(数据类型 2)。

我认为发生这种情况是因为，顾名思义，DECODE 是一个函数而 CASE 不是，这意味着它们在内部的实现方式不同。没有真正的方法可以证明这一点。

你可能认为这并没有真正影响任何事情。如果您需要它是一个数字，Oracle 会在 implicit conversion rules 下将字符隐式转换为数字。 ， 对？这也不是真的，它 won't work in a UNION因为数据类型必须相同； Oracle 不会进行任何隐式转换以使您的工作变得轻松。其次，这是 Oracle 关于隐式转换的说法:

Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:

• SQL statements are easier to understand when you use explicit data type conversion functions.

• Implicit data type conversion can have a negative impact on performance, especially if the data type of a column value is converted to that of a constant rather than the other way around.

• Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.

• Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.

这不是一个漂亮的 list 。但倒数第二点让我很好地开始约会。如果我们采用上一个查询并将其转换为使用日期的查询:

select case sysdate when trunc(sysdate) then null 
                    else sysdate 
       end as simple_case
     , case when sysdate = trunc(sysdate) then null 
            else sysdate 
       end as searched_case
     , decode(sysdate, trunc(sysdate), null, sysdate) as decode
  from dual;

再一次，在这个查询上使用 DUMP，CASE 语句返回数据类型 12，一个 DATE。 DECODE 已转换 sysdate变成一个 VARCHAR2。

SQL> select dump(case sysdate when trunc(sysdate) then null
  2                           else sysdate
  3              end) as simple_case
  4       , dump(case when sysdate = trunc(sysdate) then null
  5                   else sysdate
  6              end) as searched_case
  7       , dump(decode(sysdate, trunc(sysdate), null, sysdate)) as decode
  8    from dual;

SIMPLE_CASE          
---------------------------------- 
Typ=12 Len=7: 120,112,12,4,22,18,7 
SEARCHED_CASE
---------------------------------- 
Typ=12 Len=7: 120,112,12,4,22,18,7
DECODE
---------------------------------- 
Typ=1 Len=19: 50,48,49,50,45,49,50,45,48,52,32,50,49,58,49,55,58,48,54

SQL Fiddle

请注意(在 SQL Fiddle 中)已使用 session 将日期转换为字符 NLS_DATE_FORMAT .

将日期隐式转换为 VARCHAR2 可能会导致问题。如果您打算使用 TO_CHAR , 要将您的日期转换为字符，您的查询将在您不期望的地方中断。

SQL> select to_char( decode( sysdate
  2                         , trunc(sysdate), null
  3                         , sysdate )
  4                 , 'yyyy-mm-dd') as to_char
  5    from dual;
select to_char( decode( sysdate
                *
ERROR at line 1:
ORA-01722: invalid number

SQL Fiddle

同样，日期算术不再有效:

SQL>
SQL>
SQL> select decode(sysdate, trunc(sysdate), null, sysdate) + 1 as decode
  2    from dual;
select decode(sysdate, trunc(sysdate), null, sysdate) + 1 as decode
       *
ERROR at line 1:
ORA-01722: invalid number

SQL Fiddle

有趣的是，如果可能的结果之一为 NULL，则 DECODE 仅将表达式转换为 VARCHAR2。如果默认值为 NULL，则不会发生这种情况。例如:

SQL> select decode(sysdate, sysdate, sysdate, null) as decode
  2    from dual;

DECODE
-------------------
2012-12-04 21:18:32

SQL> select dump(decode(sysdate, sysdate, sysdate, null)) as decode
  2    from dual;

DECODE
------------------------------------------    
Typ=13 Len=8: 220,7,12,4,21,18,32,0

SQL Fiddle

请注意，DECODE 返回了 13 的数据类型。这没有记录，但我假设是一种日期类型，如日期算术等。

简而言之，尽可能避免解码；您可能不一定会得到您期望的数据类型。致 quote Tom Kyte :

Decode is somewhat obscure -- CASE is very very clear. Things that are easy to do in decode are easy to do in CASE, things that are hard or near impossible to do with decode are easy to do in CASE. CASE, logic wise, wins hands down.

为了完整起见，DECODE 和 CASE 之间有两个功能差异。

DECODE 不能在 PL/SQL 中使用。

CASE 不能用于直接比较空值

SQL> select case null when null then null else 1 end as case1
  2        , case when null is null then null else 1 end as case2
  3        , decode(null, null, null, 1) as decode
  4    from dual
  5         ;

     CASE1      CASE2 DECODE
---------- ---------- ------
         1

SQL Fiddle