Oracle中的NVL和Coalesce之间是否存在不明显的差异?
明显的区别是coalesce将返回其参数列表中的第一个非空项，而nvl只接受两个参数，如果第一个参数不为空则返回第一个，否则返回第二个。
看来NVL可能只是联合的“基本情况”版本。
我遗漏了什么吗?
当前回答
事实上，我不能同意每一种说法。
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!
其他回答
另一个coalesce()不停止对第一个非空值求值的证明:
SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;
运行这个，然后检查my_sequence.currval;
虽然这一点很明显，甚至在汤姆提出这个问题时也提到了。但让我们再放一遍。
NVL只能有2个参数。Coalesce可能有2个以上。
Select nvl("， "，1) from dual;//Result: ORA-00909: invalid number of arguments .(无效参数个数。 从dual中选择coalesce("， "，'1');//返回1
事实上，我不能同意每一种说法。
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!
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
NVL:将null替换为value。
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，我们可以实现所有三个规则。
