是CASE语句和DECODE等效语句

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

关于简单的CASE表达式，文档有以下内容：

简单CASE表达式返回第一个结果selector_value与selector匹配。其余表达式不是评估。如果没有selector_value与选择器匹配，则CASE表达式如果存在，则返回else_result，否则返回NULL。

将其与DECODE函数进行比较，说明似乎完全相同。

DECODE将expr与每个搜索值逐一进行比较。如果expr相等则Oracle数据库返回相应的结果。如果未找到匹配项，则Oracle返回默认值。如果省略默认，则Oracle返回null。

由于搜索到的CASE表达式可以等效于simple，因此也可以理解为相同。

这三个语句似乎都返回了相同的结果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函数)是否总是返回相同的结果？

简短回答，无

稍长一点的答案是接近。

只有显示从每个语句中获得的结果是相同的。如果我们使用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的数据类型摘要，DECODE返回VARCHAR2(数据类型1)，而CASE语句则"返回"数字(数据类型2)。

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

你可能会认为这并没有真正影响到任何事情。如果你需要它是一个数字，Oracle会根据隐式转换规则将字符隐式转换为数字，对吧？这也不是真的，它在UNION中不起作用，因为的数据类型具有相同的；Oracle不会做任何隐式转换来简化您的工作。其次，以下是Oracle对隐式转换的看法：

Oracle建议您指定显式转换，而不是依赖于隐式或自动转换，原因如下：
使用显式数据类型转换函数时，SQL语句更容易理解。
隐式数据类型转换可能会对性能产生负面影响，尤其是当列值的数据类型转换为常量的数据类型时，而不是相反。
隐式转换取决于它发生的上下文，并且可能不会在每种情况下都以相同的方式工作。例如，从日期时间值到VARCHAR2值的隐式转换可能会返回意外的年份，具体取决于NLS_DATE_FORMAT的值参数
隐式转换的算法可能会在软件版本和Oracle产品之间发生变化。显式转换的行为更可预测。

这不是一个漂亮的列表；但倒数第二点让我很好地了解了约会。如果我们接受上一个查询并将其转换为使用日期的查询：

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中)DATE已使用会话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

有趣的是，DECODE仅在其中一个可能的结果为NULL时才将表达式转换为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。这没有记录在案，但我认为这是一种日期运算等有效的日期类型。

简而言之，尽可能避免DECODE；您可能不一定会得到所期望的数据类型。引用Tom Kyte:

解码有些晦涩——CASE非常非常清楚。解码中容易做的事情在CASE中很容易做，困难或解码几乎是不可能的，在CASE中很容易做到。CASE，逻辑明智的人会轻而易举地获胜。

为了完整起见，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

Ben写了一篇关于DECODE和CASE之间差异的长篇回答。他演示了DECODE和CASE可能会为明显相同的值集返回不同的数据类型，但没有正确解释为什么会发生这种情况。

DECODE()非常规范：它始终是第一个结果参数的数据类型。Oracle将隐式转换应用于所有其他结果参数。如果(比如)第一个结果参数是数字，而默认值是日期，它将抛出一个错误。

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

文档中对此进行了描述：了解更多信息。

在第一个场景中，第一个结果参数为NULL，Oracle决定将其视为VARCHAR2。如果我们更改它，使第一个结果参数为数字，默认值为null，则DECODE()语句将返回一个NUMBER；DUMP()证明了这一点。

而CASE坚持所有返回的值都具有相同的数据类型，如果不是这样，则会引发编译错误。它不会应用隐式转换。这也包含在文档中。请在此处阅读。

差异归根结底是这样的。以下DECODE语句将运行，CASE语句不会运行：

select decode(1, 1, 1, '1') from dual;
select case 1 when 1 then 1 else '1' end from dual;

强制性SQL Fiddle。

我知道我来晚了，但在这里发帖是因为如果有人搜索它，希望它能有所帮助。我已经为相同的创建了一个MsSql脚本

Declare @Var varchar(399)='DECODE(MyColumnName,''A'',''Auto'',''M'',''Manual'')'
Begin
Declare @Count int, @Counter int=1
Declare @TempTable table (ID int identity(1,1),Items varchar(500))
Declare @SqlText varchar(max)
Select @Var=Replace(Replace(@Var,'DECODE(',''),')','')
Insert Into @TempTable
Select * FROM [dbo].[Split] ( @Var ,',')
--Select * from @TempTable
Select @Count=Count(ID) from @TempTable
While(@Counter<=@Count)
Begin
If(@Counter=1)
Begin
Select @SqlText='Case ' +Items from @TempTable Where ID=1
End
Else If(@Counter=@Count)
Begin
Select @SqlText+=' Then ' +Items +' End' from @TempTable Where ID=@Counter 
End
Else If(@Counter%2=0)
Begin
Select @SqlText +=' When ' +Items from @TempTable Where ID=@Counter
End
Else If(@Counter%2=1)
Begin
Select @SqlText +=' Then ' +Items from @TempTable Where ID=@Counter
End
Set @Counter+=1
End
Select @SqlText SqlServerCaseStatement
End

我在上面的脚本中使用了Split函数，如果你需要这个函数，你可以参考Romil的答案——如何将逗号分隔的值拆分到列

相关内容

最新更新

热门标签：