oracle NVL 功能可以级联吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1108950/
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
Can NVL Function be Cascaded?
提问by hrishi
can nvl() function be cascaded,...it was asked me in IBM interview .....and why????
nvl() 函数可以级联吗，...IBM 面试时问过我.....为什么？？？？
回答by cagcowboy
Better yet, use COALESCE
更好的是，使用 COALESCE
回答by OMG Ponies
My answer:
我的答案：
Yes, NVL can be cascaded. It's something I'd expect to see on code ported from Oracle versions up to and including 8i, because COALESCE wasn't supported until Oracle 9i.
COALESCE is an ANSI SQL 99 standard, and works like a CASE/SWITCH statement in how it evaluates each expression in order and does not continue evaluating expressions once it hits a non-null expression. This is called short-circuiting. Another benefit of using COALESCE is that the data types do not need to match, which is required when using NVL.
是的，NVL 可以级联。这是我希望在从 Oracle 版本（包括 8i）移植的代码中看到的东西，因为直到 Oracle 9i 才支持 COALESCE。
COALESCE 是 ANSI SQL 99 标准，它的工作方式类似于 CASE/SWITCH 语句，它如何按顺序评估每个表达式，并且一旦遇到非空表达式就不会继续评估表达式。这称为短路。使用 COALESCE 的另一个好处是数据类型不需要匹配，这在使用 NVL 时是必需的。
This:
这个：
SELECT COALESCE( 1.5 / NULL,
SUM(NULL),
TO_CHAR(SYSDATE, 'YYYY') ) abc
FROM DUAL
...will return: 2009(for the next ~32 days, anyways)
...将返回：（2009无论如何，接下来的~32天）
回答by borjab
Why not? For example:
为什么不？例如：
select NVL( null, NVL( null, 1 )) from dual
It can be something like:
它可以是这样的：
select NVL( delete_date, NVL( edit_date, create_date )) AS last_change
from Table
May be they wanted you to say that it is deterministic function. So it is reentrant.
可能他们想让你说它是确定性函数。所以它是可重入的。
回答by Philip Schlump
The most common reason that I have seen for cascaded NVL's is that databases change over time. The original design had a table that later was changed to have more columns in the table. Alter statements created the new columns as NULL allowed and this was taken advantage of with view so that the code on top did not need to change. The problem was that a single column, change_date, was replaced with multiple columns. Update_Date, Comment_Date, and Approval_date. Each of the 3 new columns is now combined to get a "change_date" in the view with
我看到的级联 NVL 的最常见原因是数据库随时间变化。最初的设计有一个表，后来被更改为在表中包含更多列。Alter 语句创建了允许 NULL 的新列，这在视图中得到了利用，因此不需要更改顶部的代码。问题是单个列change_date 被多个列替换。Update_Date、Comment_Date 和 Approval_date。3 个新列中的每一个现在都组合在一起以在视图中获得一个“change_date”
create or replace view OldTableNmae as
select other_columns
, nvl ( update_date, nvl ( comment_date, approval_date ) ) change_date
, more_columns
from new_table
/
回答by doundarric
As others said, NVL can be cascaded, but the preferred solution would be to use COALESCE instead. However, the two functions are not entirely interchangeable:
正如其他人所说，NVL 可以级联，但首选的解决方案是使用 COALESCE。但是，这两个功能并不完全可以互换：
1) as mentioned elsewhere, COALESCE only evaluates the arguments from the first until it meets one that does not evaluate to null
1) 如别处所述，COALESCE 仅从第一个开始评估参数，直到遇到不评估为 null 的参数
2) however, COALESCE requires all arguments to be of the same data type, thus being STRICTERthan NVL, which will first attempt an implicit conversion.
2）然而，COALESCE要求所有参数具有相同的数据类型的，因此是更严格的比NVL，这将首先尝试的隐式转换。
E.g.
例如
SELECT COALESCE( 1.5 / NULL,
SUM(NULL),
TO_CHAR(SYSDATE, 'YYYY') ) abc
FROM DUAL
throws in fact the error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
实际上抛出错误 ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
NVL would instead return the current year
NVL 将改为返回当前年份
SELECT NVL( 1.5 / NULL,
NVL( SUM(NULL),
TO_CHAR(SYSDATE, 'YYYY') ) ) abc
FROM DUAL
Other examples:
其他例子：
select coalesce('some text',sysdate) from dual;
throws ORA-00932: inconsistent datatypes: expected CHAR got DATE, while
抛出ORA-00932: inconsistent datatypes: expected CHAR got DATE，而
select nvl('some text',sysdate) from dual;
returns some text, but
返回some text，但是
select nvl(sysdate,'some text') from dual;
throws ORA-01841: (full) year must be between -4713 and +9999, and not be 0(because the implicit conversion attempt of 'some text' to a date has failed)
抛出ORA-01841: (full) year must be between -4713 and +9999, and not be 0（因为“某些文本”到日期的隐式转换尝试失败）
