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屋！