CASE语句和DECODE是否等效? [英] Are a CASE statement and a DECODE equivalent?
问题描述
似乎简单的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匹配选择器.其余的表达式不是 评估.如果没有选择器匹配选择器，则使用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.
将此与 DECODE函数进行比较，说明似乎是相同的.
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表达式可以等同于简单，这也可以解释为相同.
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.如果我们对其进行更改，以使第一个结果参数为数字，而默认值为null，则DECODE()语句将返回NUMBER；否则，它将返回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;
这篇关于CASE语句和DECODE是否等效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
