当前位置： 文江博客话题详情

Oracle NVL 和 Coalesce 之间的差异

发布于 2024-07-23 01:19:37 字数 186 浏览 17 评论 0原文

Oracle 中的 NVL 和 Coalesce 之间是否存在不明显的差异？

明显的区别是，coalesce 将返回其参数列表中的第一个非 null 项，而 nvl 仅接受两个参数，如果不为 null，则返回第一个参数，否则返回第二个。

看来 NVL 可能只是合并的“基本案例”版本。

我错过了什么吗？

原文

Are there non obvious differences between NVL and Coalesce in Oracle?

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.

It seems that NVL may just be a 'Base Case" version of coalesce.

Am I missing something?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（8）

蘸点软妹酱 2024-07-30 01:19:37

COALESCE 是更现代的函数，是 ANSI-92 标准的一部分。

NVL 是 Oracle 特定的，它是在 80 出现任何标准之前引入的。

如果有两个值，它们是同义词。

然而，它们的实现方式不同。

NVL 始终评估两个参数，而 COALESCE 通常在发现第一个非 NULL 时停止评估（有一些例外，例如序列 < code>NEXTVAL)：

SELECT  SUM(val)
FROM    (
        SELECT  NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

它运行了近 0.5 秒，因为它生成了 SYS_GUID()，尽管 1 不是一个NULL。

SELECT  SUM(val)
FROM    (
        SELECT  COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

这可以理解 1 不是 NULL 并且不会计算第二个参数。

SYS_GUID 不会生成，并且查询是即时的。

COALESCE is more modern function that is a part of ANSI-92 standard.

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 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
        )

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
        )

This understands that 1 is not a NULL and does not evaluate the second argument.

SYS_GUID's are not generated and the query is instant.

回复 原文
无人接听 2024-07-30 01:19:37

NVL 将对第一个参数的数据类型进行隐式转换，因此以下内容不会出现

select nvl('a',sysdate) from dual;

COALESCE 需要一致数据类型的错误。

select coalesce('a',sysdate) from dual;

将抛出“数据类型不一致错误”

NVL will do an implicit conversion to the datatype of the first parameter, so the following does not error

select nvl('a',sysdate) from dual;

COALESCE expects consistent datatypes.

select coalesce('a',sysdate) from dual;

will throw a 'inconsistent datatype error'

回复 原文
香草可樂 2024-07-30 01:19:37

NVL 和 COALESCE 用于实现相同的功能，即在列返回 NULL 时提供默认值。

区别在于：

  1. NVL 仅接受 2 个参数，而 COALESCE 可以接受多个参数
    argument
  2. NVL 计算两个参数并且 COALESCE 首先停止
    出现非空值。
  3. NVL 根据第一个参数进行隐式数据类型转换
    给予它。 COALESCE 期望所有参数具有相同的数据类型。
  4. COALESCE 在使用 UNION 子句的查询中出现问题。 例子
    下面的
  5. COALESCE 是 ANSI 标准，而 NVL 是 Oracle 特定的。

以第三种情况为例。 其他情况都很简单。

select nvl('abc',10) from Dual; 将起作用，因为 NVL 会将数字 10 隐式转换为字符串。

select coalesce('abc',10) from Dual; 将失败，并显示错误 - 不一致的数据类型：预期 CHAR 得到 NUMBER

UNION 用例的示例

SELECT COALESCE(a, sysdate) 
from (select null as a from dual 
      union 
      select null as a from dual
      );

失败，并显示 ORA-00932: 不一致的数据类型：预期CHAR 获取 DATE

SELECT NVL(a, sysdate) 
from (select null as a from dual 
      union 
      select null as a from dual
      ) ;

成功。

更多信息：http://www. plsqlinformation.com/2016/04/difference- Between-nvl-and-coalesce-in-oracle.html

NVL and COALESCE are used to achieve the same functionality of providing a default value in case the column returns a NULL.

The differences are:

  1. NVL accepts only 2 arguments whereas COALESCE can take multiple
    arguments
  2. NVL evaluates both the arguments and COALESCE stops at first
    occurrence of a non-Null value.
  3. NVL does a implicit datatype conversion based on the first argument
    given to it. COALESCE expects all arguments to be of same datatype.
  4. COALESCE gives issues in queries which use UNION clauses. Example
    below
  5. COALESCE is ANSI standard where as NVL is Oracle specific.

Examples for the third case. Other cases are simple.

select nvl('abc',10) from dual; would work as NVL will do an implicit conversion of numeric 10 to string.

select coalesce('abc',10) from dual; will fail with Error - inconsistent datatypes: expected CHAR got NUMBER

Example for UNION use-case

SELECT COALESCE(a, sysdate) 
from (select null as a from dual 
      union 
      select null as a from dual
      );

fails with ORA-00932: inconsistent datatypes: expected CHAR got DATE

SELECT NVL(a, sysdate) 
from (select null as a from dual 
      union 
      select null as a from dual
      ) ;

succeeds.

More information : http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html

回复 原文
南风起 2024-07-30 01:19:37

计划处理方面也存在差异。

当搜索包含 nvl 结果与索引列的比较时，Oracle 能够通过串联分支过滤器形成优化计划。

create table tt(a, b) as
select level, mod(level,10)
from dual
connect by level<=1e4;

alter table tt add constraint ix_tt_a primary key(a);
create index ix_tt_b on tt(b);

explain plan for
select * from tt
where a=nvl(:1,a)
  and b=:2;

explain plan for
select * from tt
where a=coalesce(:1,a)
  and b=:2;

nvl：

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     2 |    52 |     2   (0)| 00:00:01 |
|   1 |  CONCATENATION                |         |       |       |            |          |
|*  2 |   FILTER                      |         |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_TT_B |     7 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |         |       |       |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | IX_TT_A |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:1 IS NULL)
   3 - filter("A" IS NOT NULL)
   4 - access("B"=TO_NUMBER(:2))
   5 - filter(:1 IS NOT NULL)
   6 - filter("B"=TO_NUMBER(:2))
   7 - access("A"=:1)

合并：

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    26 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TT_B |    40 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=COALESCE(:1,"A"))
   2 - access("B"=TO_NUMBER(:2))

积分转到 http://www.xt-r.com/2012 /03/nvl-coalesce-concatenation.html。

There is also difference is in plan handling.

Oracle is able form an optimized plan with concatenation of branch filters when search contains comparison of nvl result with an indexed column.

create table tt(a, b) as
select level, mod(level,10)
from dual
connect by level<=1e4;

alter table tt add constraint ix_tt_a primary key(a);
create index ix_tt_b on tt(b);

explain plan for
select * from tt
where a=nvl(:1,a)
  and b=:2;

explain plan for
select * from tt
where a=coalesce(:1,a)
  and b=:2;

nvl:

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     2 |    52 |     2   (0)| 00:00:01 |
|   1 |  CONCATENATION                |         |       |       |            |          |
|*  2 |   FILTER                      |         |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_TT_B |     7 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |         |       |       |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | IX_TT_A |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:1 IS NULL)
   3 - filter("A" IS NOT NULL)
   4 - access("B"=TO_NUMBER(:2))
   5 - filter(:1 IS NOT NULL)
   6 - filter("B"=TO_NUMBER(:2))
   7 - access("A"=:1)

coalesce:

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    26 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TT_B |    40 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=COALESCE(:1,"A"))
   2 - access("B"=TO_NUMBER(:2))

Credits go to http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html.

回复 原文
海的爱人是光 2024-07-30 01:19:37

Coalesce() 不会停止对第一个非空值进行求值的另一个证明：

SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;

运行此命令，然后检查 my_sequence.currval;

Another proof that coalesce() does not stop evaluation with the first non-null value:

SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;

Run this, then check my_sequence.currval;

回复 原文
蓝礼 2024-07-30 01:19:37

NVL：用值替换 null。

COALESCE：返回表达式列表中的第一个非空表达式。

表：PRICE_LIST

+----------------+-----------+
| Purchase_Price | Min_Price |
+----------------+-----------+
| 10             | null      |
| 20             |           |
| 50             | 30        |
| 100            | 80        |
| null           | null      |
+----------------+-----------+

下面是

的示例
[1] 设定销售价格，所有产品加收10%利润。
[2] 如果没有购买标价，则销售价格为最低价格。 清仓出售。
[3] 如果也没有最低价格，则将销售价格设置为默认价格“50”。

SELECT
     Purchase_Price,
     Min_Price,
     NVL(Purchase_Price + (Purchase_Price * 0.10), Min_Price)    AS NVL_Sales_Price,
COALESCE(Purchase_Price + (Purchase_Price * 0.10), Min_Price,50) AS Coalesce_Sales_Price
FROM 
Price_List

用现实生活中的实际例子来解释。

+----------------+-----------+-----------------+----------------------+
| Purchase_Price | Min_Price | NVL_Sales_Price | Coalesce_Sales_Price |
+----------------+-----------+-----------------+----------------------+
| 10             | null      | 11              |                   11 |
| null           | 20        | 20              |                   20 |
| 50             | 30        | 55              |                   55 |
| 100            | 80        | 110             |                  110 |
| null           | null      | null            |                   50 |
+----------------+-----------+-----------------+----------------------+

您可以看到，使用 NVL 我们可以实现规则 [1],[2]
但是使用 COALSECE 我们可以实现所有三个规则。

NVL: Replace the null with value.

COALESCE: Return the first non-null expression from expression list.

Table: PRICE_LIST

+----------------+-----------+
| Purchase_Price | Min_Price |
+----------------+-----------+
| 10             | null      |
| 20             |           |
| 50             | 30        |
| 100            | 80        |
| null           | null      |
+----------------+-----------+

Below is the example of

[1] Set sales price with adding 10% profit to all products.
[2] If there is no purchase list price, then the sale price is the minimum price. For clearance sale.
[3] If there is no minimum price also, then set the sale price as default price "50".

SELECT
     Purchase_Price,
     Min_Price,
     NVL(Purchase_Price + (Purchase_Price * 0.10), Min_Price)    AS NVL_Sales_Price,
COALESCE(Purchase_Price + (Purchase_Price * 0.10), Min_Price,50) AS Coalesce_Sales_Price
FROM 
Price_List

Explain with real life practical example.

+----------------+-----------+-----------------+----------------------+
| Purchase_Price | Min_Price | NVL_Sales_Price | Coalesce_Sales_Price |
+----------------+-----------+-----------------+----------------------+
| 10             | null      | 11              |                   11 |
| null           | 20        | 20              |                   20 |
| 50             | 30        | 55              |                   55 |
| 100            | 80        | 110             |                  110 |
| null           | null      | null            |                   50 |
+----------------+-----------+-----------------+----------------------+

You can see that with NVL we can achieve rules [1],[2]
But with COALSECE we can achieve all three rules.

回复 原文
奶气 2024-07-30 01:19:37

尽管这一点是显而易见的，甚至在提出这个问题的汤姆提出的方式中也提到过。 但让我们再放一次。

NVL 只能有 2 个参数。 合并可能超过 2 个。

select nvl('','',1) from Dual; //结果：ORA-00909：参数数量无效
select coalesce('','','1') from Dual; //输出：返回1

Though this one is obvious, and even mentioned in a way put up by Tom who asked this question. But lets put up again.

NVL can have only 2 arguments. Coalesce may have more than 2.

select nvl('','',1) from dual; //Result: ORA-00909: invalid number of arguments
select coalesce('','','1') from dual; //Output: returns 1

回复 原文
ㄟ。诗瑗 2024-07-30 01:19:37

其实我不能同意每一个说法。

“COALESCE 希望所有参数都具有相同的数据类型。”

这是错误的，见下文。 参数可以是不同的数据类型，这也有记录：< em>如果所有出现的 expr 都是数值数据类型或任何可以隐式转换为数值数据类型的非数值数据类型，则 Oracle 数据库确定具有最高数值优先级的参数，将其余参数隐式转换为该数据类型，并且返回该数据类型。。 实际上，这甚至与常见表达式“COALESCE 在第一次出现非空值时停止”相矛盾，否则测试用例 4 不应该引发错误。

另外，根据测试用例 5 COALESCE 执行参数的隐式转换。

DECLARE
    int_val INTEGER := 1;
    string_val VARCHAR2(10) := 'foo';
BEGIN

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '1. NVL(int_val,string_val) -> '|| NVL(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('1. NVL(int_val,string_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '2. NVL(string_val, int_val) -> '|| NVL(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('2. NVL(string_val, int_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '3. COALESCE(int_val,string_val) -> '|| COALESCE(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('3. COALESCE(int_val,string_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '4. COALESCE(string_val, int_val) -> '|| COALESCE(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('4. COALESCE(string_val, int_val) -> '||SQLERRM ); 
    END;

    DBMS_OUTPUT.PUT_LINE( '5. COALESCE(SYSDATE,SYSTIMESTAMP) -> '|| COALESCE(SYSDATE,SYSTIMESTAMP) );

END;

Output:

1. NVL(int_val,string_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
2. NVL(string_val, int_val) -> foo
3. COALESCE(int_val,string_val) -> 1
4. COALESCE(string_val, int_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
5. COALESCE(SYSDATE,SYSTIMESTAMP) -> 2016-11-30 09:55:55.000000 +1:0 --> This is a TIMESTAMP value, not a DATE value!

Actually I cannot agree to each statement.

"COALESCE expects all arguments to be of same datatype."

This is wrong, see below. Arguments can be different data types, that is also documented: If all occurrences of expr are numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.. Actually this is even in contradiction to common expression "COALESCE stops at first occurrence of a non-Null value", otherwise test case No. 4 should not raise an error.

Also according to test case No. 5 COALESCE does an implicit conversion of arguments.

DECLARE
    int_val INTEGER := 1;
    string_val VARCHAR2(10) := 'foo';
BEGIN

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '1. NVL(int_val,string_val) -> '|| NVL(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('1. NVL(int_val,string_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '2. NVL(string_val, int_val) -> '|| NVL(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('2. NVL(string_val, int_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '3. COALESCE(int_val,string_val) -> '|| COALESCE(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('3. COALESCE(int_val,string_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '4. COALESCE(string_val, int_val) -> '|| COALESCE(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('4. COALESCE(string_val, int_val) -> '||SQLERRM ); 
    END;

    DBMS_OUTPUT.PUT_LINE( '5. COALESCE(SYSDATE,SYSTIMESTAMP) -> '|| COALESCE(SYSDATE,SYSTIMESTAMP) );

END;

Output:

1. NVL(int_val,string_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
2. NVL(string_val, int_val) -> foo
3. COALESCE(int_val,string_val) -> 1
4. COALESCE(string_val, int_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
5. COALESCE(SYSDATE,SYSTIMESTAMP) -> 2016-11-30 09:55:55.000000 +1:0 --> This is a TIMESTAMP value, not a DATE value!
回复 原文
~没有更多了~

关于作者

唔猫

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

已经忘了多久

文章 0 评论 0

15867725375

文章 0 评论 0

LonelySnow

文章 0 评论 0

走过海棠暮

文章 0 评论 0

轻许诺言

文章 0 评论 0

信馬由缰

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文