Oracle NVL和Coalesce之间的差异 [英] Oracle Differences between NVL and Coalesce
问题描述
在Oracle中NVL和Coalesce之间没有明显的区别吗?
Are there non obvious differences between NVL and Coalesce in Oracle?
明显的区别是,合并将返回其参数列表中的第一个非null项,而nvl仅接受两个参数,如果不为null则返回第一个,否则返回第二个.
The obvious differences are that coalesce will return the first non null item in its parameter list whereas nvl only takes two parameters and returns the first if it is not null, otherwise it returns the second.
似乎NVL可能只是合并的基本案例"版本.
It seems that NVL may just be a 'Base Case" version of coalesce.
我想念什么吗?
推荐答案
COALESCE
是更现代的功能,它是ANSI-92
标准的一部分.
COALESCE
is more modern function that is a part of ANSI-92
standard.
NVL
是Oracle
特有的,它是在80
之前引入的,没有任何标准.
NVL
is Oracle
specific, it was introduced in 80
's before there were any standards.
在有两个值的情况下,它们是同义词.
In case of two values, they are synonyms.
但是,它们的实现方式有所不同.
However, they are implemented differently.
NVL
总是对两个参数求值,而COALESCE
通常在找到第一个非NULL
时停止求值(有一些例外,例如序列NEXTVAL
):
NVL
always evaluates both arguments, while COALESCE
usually stops evaluation whenever it finds the first non-NULL
(there are some exceptions, such as sequence NEXTVAL
):
SELECT SUM(val)
FROM (
SELECT NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
FROM dual
CONNECT BY
level <= 10000
)
尽管1
不是NULL
,但它会生成SYS_GUID()
,因此运行了将近0.5
秒.
This runs for almost 0.5
seconds, since it generates SYS_GUID()
's, despite 1
being not a NULL
.
SELECT SUM(val)
FROM (
SELECT COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
FROM dual
CONNECT BY
level <= 10000
)
这理解1
不是NULL
,并且不求值第二个参数.
This understands that 1
is not a NULL
and does not evaluate the second argument.
SYS_GUID
不会生成,查询是即时的.
SYS_GUID
's are not generated and the query is instant.
这篇关于Oracle NVL和Coalesce之间的差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!