CASE 语句和 DECODE 是否等效?
看起来简单的 CASE 表达式和 DECODE 函数是等价的，它们返回的结果应该是相同的.是吗?
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 表达式:
简单的 CASE 表达式返回第一个结果selector_value 匹配选择器.剩下的表达不是评估.如果没有 selector_value 匹配选择器，则 CASE 表达式如果存在则返回 else_result，否则返回 NULL.
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.
将此与解码函数进行比较，描述似乎是相同的.
Comparing this to the DECODE function, the descriptions seem to be identical.
DECODE 将 expr 与每个搜索值一一比较.如果 expr 相等到一个搜索，然后 Oracle 数据库返回相应的结果.如果未找到匹配项，则 Oracle 返回默认值.如果省略默认值，然后 Oracle 返回 null.
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.
由于搜索的CASE表达式可以等价于simple，这也可以解释为一样.
As the searched CASE expression can be equivalent to the simple, this could be construed to be the same as well.
这三个语句似乎都返回相同的结果，0.
These three statements all seem to return the same result, 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 表达式)总是返回相同的结果吗?
Do the simple CASE expression and the DECODE function (and in specific circumstances the searched CASE expression) always return the same result?
推荐答案
Ben 写了一篇关于 DECODE 和 CASE 差异的冗长回答.他演示了 DECODE 和 CASE 可能会为明显相同的一组值返回不同的数据类型，而没有正确解释为什么会发生这种情况.
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.
DECODE() 非常规范:它始终是第一个 result 参数的数据类型.Oracle 将隐式转换应用于所有其他结果参数.如果(比如说)第一个结果参数是数字并且默认值是日期，它会抛出一个错误.
DECODE() is quite prescriptive: it is always the datatype of the first result parameter. 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.
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
文档中对此进行了描述:了解更多信息.
This is described in the documentation: find out more.
在第一种情况下，第一个结果参数是 NULL，Oracle 决定将其视为 VARCHAR2.如果我们将其更改为第一个结果参数为数字且默认值为空，则 DECODE() 语句将返回 NUMBER；DUMP() 证明确实如此.
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.
而 CASE 坚持所有返回的值具有相同的数据类型，如果不是这种情况，将抛出编译错误.它不会应用隐式转换.这也包含在文档中.在此处阅读.
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.
差异归结为这一点.下面的 DECODE 语句会运行，CASE 语句不会:
The difference boils down to this. The following DECODE statement will run, the CASE statement won't:
select decode(1, 1, 1, '1') from dual;
select case 1 when 1 then 1 else '1' end from dual;
强制性 SQL 小提琴.
相关文章