为什么 NVL 总是评估第二个参数
有谁知道，为什么Oracle的NVL（和NVL2）函数总是评估第二个参数，即使第一个参数不是NULL？
简单测试：
CREATE FUNCTION nvl_test RETURN NUMBER AS
BEGIN
dbms_output.put_line('Called');
RETURN 1;
END nvl_test;
SELECT NVL( 0, nvl_test ) FROM Dual
返回 0，但也打印 Called。
nvl_test 已被调用，即使结果被忽略，因为第一个参数不是 NULL。
Does anyone know, why Oracle's NVL (and NVL2) function always evaluate the second parameter, even if the first parameter is not NULL?
Simple test:
CREATE FUNCTION nvl_test RETURN NUMBER AS
BEGIN
dbms_output.put_line('Called');
RETURN 1;
END nvl_test;
SELECT NVL( 0, nvl_test ) FROM dual
returns 0, but also prints Called.
nvl_test has been called, even though the result is ignored since first parameter is not NULL.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
一直都是这样，因此 Oracle 必须保持这种方式以保持向后兼容。
使用
COALESCE来获取短路行为。It's always been that way, so Oracle has to keep it that way to remain backwards compatible.
Use
COALESCEinstead to get the short-circuit behaviour.在这篇文章中，Tom Kyte 确认了
decode和case短路，但没有确认nvl，但他没有给出理由或说明原因。只是声明为：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:926029357278#14932880517348
所以在你的情况下你应该使用
decode或case而不是nvl如果查询中将调用昂贵的函数。Here is a post where Tom Kyte confirms that
decodeandcaseshort circuit but notnvlbut he doesn't give justification or documentation for why. Just states it to be:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:926029357278#14932880517348
So in your case you should use
decodeorcaseinstead ofnvlif an expensive function will be called in your query.一般来说，在调用函数之前评估第二个参数是有意义的，因为通常这就是函数的调用方式：评估函数的所有参数并将评估值发送到函数。
然而，对于像 NVL 这样非常常见的系统函数，我认为 PL/SQL 可以进行优化，将函数调用视为特殊情况。但也许这比听起来更困难（对我来说），因为我确信 Oracle 的开发人员会想到这种优化。
In general, it would make sense that the second parameter is evaluated before calling the function, because in general that is how functions are called: all arguments to the function are evaluated and the evaluated values are sent to the function.
However, in the case of a very common system function like NVL, I would have thought PL/SQL could optimise, treating the function call as a special case. But perhaps that is more difficult than it sounds (to me), as I'm sure this optimisation would have occurred to the developers of Oracle.
它们显然没有短路，但我在 Oracle 文档中找不到任何参考资料。
查看此讨论：http://forums.oracle.com/forums/thread .jspa?messageID=3478040
They are obviously not short-circuiting, but I can't find any references in Oracle documentation.
Check out this discussion: http://forums.oracle.com/forums/thread.jspa?messageID=3478040