NVL和Coalesce之间的Oracle差异

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

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

看来NVL可能只是联合的“基本情况”版本。

我遗漏了什么吗?

当前回答

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

区别在于:

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

第三种情况的例子。其他情况很简单。

Select nvl('abc'，10) from dual;将工作，因为NVL将做数字10到字符串的隐式转换。

Select coalesce('abc'，10) from dual;将失败的错误-不一致的数据类型:预期的CHAR得到数字

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

2016-01-16 18:06:51

其他回答

在计划处理方面也有不同。

当搜索包含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。

2014-10-07 18:15:18

事实上，我不能同意每一种说法。

COALESCE期望所有参数具有相同的数据类型。

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.

根据测试用例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!

2016-11-30 08:50:17

NVL将隐式转换为第一个参数的数据类型，因此下面的操作不会出错

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

COALESCE期望一致的数据类型。

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

将抛出'不一致的数据类型错误'

2009-06-05 05:13:19

虽然这一点很明显，甚至在汤姆提出这个问题时也提到了。但让我们再放一遍。

NVL只能有2个参数。Coalesce可能有2个以上。

Select nvl("， "，1) from dual;//Result: ORA-00909: invalid number of arguments .(无效参数个数。从dual中选择coalesce("， "，'1');//返回1

2014-11-19 05:28:00