oracle CASE 语句和 DECODE 是否等效？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13712763/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Are a CASE statement and a DECODE equivalent?
提问by Ben
It seems like the simple CASE expression and the DECODE function are equivalent and that the results returned by them should be identical. Are they?
看起来简单的 CASE 表达式和 DECODE 函数是等效的，它们返回的结果应该是相同的。他们是吗？
The documentation has the following to say about the simple CASE expression:
该文档对简单的 CASE 表达式有以下说明：
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.
简单的 CASE 表达式返回 selector_value 匹配选择器的第一个结果。不评估剩余的表达式。如果没有 selector_value 匹配选择器，则 CASE 表达式如果存在则返回 else_result，否则返回 NULL。
Comparing this to the DECODE function, the descriptions seem to be identical.
将其与DECODE 函数进行比较，描述似乎是相同的。
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.
DECODE 将 expr 与每个搜索值一一比较。如果 expr 等于搜索，则 Oracle 数据库返回相应的结果。如果未找到匹配项，则 Oracle 返回默认值。如果省略 default，则 Oracle 返回 null。
As the searched CASE expressioncan be equivalent to the simple, this could be construed to be the same as well.
由于搜索到的 CASE 表达式可以等同于简单的，这也可以解释为相同。
These three statements all seem to return the same result, 0.
这三个语句似乎都返回相同的结果 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
Do the simple CASE expression and the DECODE function (and in specific circumstances the searched CASE expression) always return the same result?
简单的 CASE 表达式和 DECODE 函数（以及在特定情况下搜索的 CASE 表达式）是否总是返回相同的结果？
采纳答案by APC
Ben has written a lengthy answer on the differences between DECODE and CASE. He demonstrates that DECODE and CASE may return different datatypes for apparently the same set of values without properly explaining why this happens.
Ben 写了一篇关于 DECODE 和 CASE 之间差异的冗长答案。他演示了 DECODE 和 CASE 可能会为明显相同的一组值返回不同的数据类型，而没有正确解释为什么会发生这种情况。
DECODE() is quite prescriptive: it is always the datatype of the first resultparameter. Oracle applies implicit conversion to all the other result parameters. It will throw an error , if (say) the first result parameter is numeric and the default value is a date.
DECODE() 非常规范：它始终是第一个结果参数的数据类型。Oracle 将隐式转换应用于所有其他结果参数。如果（比如说）第一个结果参数是数字并且默认值是日期，它会抛出一个错误。
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
This is described in the documentation: find out more.
这在文档中有描述：了解更多。
In the first scenario the first result parameter is NULL, which Oracle decides to treat as VARCHAR2. If we change it so that the first result parameter is numeric and the default value is null the DECODE() statement will return a NUMBER; a DUMP() proves that this is so.
在第一种情况下，第一个结果参数是 NULL，Oracle 决定将其视为 VARCHAR2。如果我们将其更改为第一个结果参数为数字且默认值为空，则 DECODE() 语句将返回 NUMBER；DUMP() 证明确实如此。
Whereas CASE insists that all the returned values have the same datatype, and will throw a compilation error if this is not the case. It won't apply implicit conversion. This is also covered in the documentation. Read it here.
而 CASE 坚持所有返回的值都具有相同的数据类型，如果不是这种情况，将抛出编译错误。它不会应用隐式转换。这也包含在文档中。在这里阅读。
The difference boils down to this. The following DECODE statement will run, the CASE statement won't:
差异归结为这一点。以下 DECODE 语句将运行，CASE 语句不会：
select decode(1, 1, 1, '1') from dual;
select case 1 when 1 then 1 else '1' end from dual;
回答by Ben
Short answer, no.
简短的回答，没有。
The slightly longer answer is nearly.
稍长的答案几乎是。
It only appearsthat the result obtained from each statement is identical. If we use the DUMPfunction to evaluate the data types returned you'll see what I mean:
似乎只是从每个语句中获得的结果是相同的。如果我们使用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
You can see that the data type of the DECODE is 1, whereas the two CASE statements "return" a data type of 2. Using Oracle's Data Type Summary, DECODE is returning a VARCHAR2 (data type 1) whereas the CASE statements are "returning" numbers (data type 2).
您可以看到 DECODE 的数据类型为 1，而两个 CASE 语句“返回”数据类型为 2。使用 Oracle 的数据类型摘要，DECODE 返回 VARCHAR2（数据类型 1），而 CASE 语句“返回” " 数字（数据类型 2）。
I assume this occurs because, as the names suggest, DECODE is a functionand CASE isn't, which implies they have been implemented differently internally. There's no real way to prove this.
我认为发生这种情况是因为，顾名思义，DECODE 是一个函数而 CASE 不是，这意味着它们在内部的实现方式不同。没有真正的方法可以证明这一点。
You might think that this doesn't really affect anything. If you need it to be a number Oracle will implicitly convert the character to a number under the implicit conversion rules, right? This isn't true either, it won't work in a UNIONas the data types haveto be identical; Oracle won't do any implicit conversion to make things easy for you. Secondly, here's what Oracle says about implicit conversion:
您可能认为这并没有真正影响任何事情。如果你需要它是一个数字 Oracle 会在隐式转换规则下将字符隐式转换为数字，对吗？这也不是真的，它在 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.
Oracle 建议您指定显式转换，而不是依赖隐式或自动转换，原因如下：
使用显式数据类型转换函数时，SQL 语句更容易理解。
隐式数据类型转换会对性能产生负面影响，尤其是在将列值的数据类型转换为常量而不是相反时。
隐式转换取决于它发生的上下文，并且可能不会在每种情况下都以相同的方式工作。例如，从日期时间值到 VARCHAR2 值的隐式转换可能会返回意外的年份，具体取决于 NLS_DATE_FORMAT 参数的值。
隐式转换算法可能会因软件版本和 Oracle 产品而异。显式转换的行为更可预测。
That's not a pretty list; but the penultimate point brings me nicely on to dates. If we take the previous query and convert it into one that uses a date instead:
这不是一个漂亮的清单。但倒数第二点让我很好地开始约会。如果我们采用上一个查询并将其转换为使用日期的查询：
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;
Once again, using DUMP on this query the CASE statements return data type 12, a DATE. The DECODE has converted sysdateinto a VARCHAR2.
再一次，在这个查询上使用 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
Note (in the SQL Fiddle) that the DATE has been converted into a character using the sessions NLS_DATE_FORMAT.
请注意（在 SQL Fiddle 中）已使用会话NLS_DATE_FORMAT将 DATE 转换为字符。
Having a date that's been implicitly converted into a VARCHAR2 can cause problems. If you're intending to use TO_CHAR, to convert your date into a character, your query will break where you're not expecting it.
将日期隐式转换为 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
Equally, date arithmetic no longer works:
同样，日期算术不再有效：
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
Interestingly DECODE only converts the expression to a VARCHAR2 if one of the possible results is NULL. If the default value is NULL then this doesn't happen. For instance:
有趣的是，如果可能的结果之一为 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
Note that the DECODE has returned a data type of 13. This isn't documented but is, I assume, a type of date as date arithmetic etc. works.
请注意，DECODE 返回了 13 的数据类型。这没有记录，但我假设是一种日期类型，如日期算术等。
In short, avoid DECODE if you possibly can; you might not necessarily get the data types you're expecting. To 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.
解码有点晦涩——CASE 非常非常清楚。在解码中容易做的事情在 CASE 中很容易做到，在解码中很难或几乎不可能做的事情在 CASE 中很容易做到。案例，逻辑明智，赢得了胜利。
Just to be complete there are two functionaldifferences between DECODE and CASE.
为了完整起见，DECODE 和 CASE 之间有两个功能差异。
- DECODE cannot be used within PL/SQL.
CASE cannot be used to compare nulls directly
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
- 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
回答by Anand Singh Sengar
I know i am too late but posting here because if someone search for that hopefully it could help. I have created a MsSql script for the same-
我知道我为时已晚，但在这里发帖是因为如果有人搜索它，希望它会有所帮助。我为相同的创建了一个 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
I used Split function in above script, if you need that function you can refer Romil's answer - How to split a comma-separated value to columns
我在上面的脚本中使用了 Split 函数，如果你需要这个函数，你可以参考 Romil 的回答 -如何将逗号分隔的值拆分为列
