Oracle中的NVL和Coalesce之间是否存在不明显的差异?
明显的区别是coalesce将返回其参数列表中的第一个非空项，而nvl只接受两个参数，如果第一个参数不为空则返回第一个，否则返回第二个。
看来NVL可能只是联合的“基本情况”版本。
我遗漏了什么吗?
当前回答
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，我们可以实现所有三个规则。
其他回答
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，我们可以实现所有三个规则。
NVL将隐式转换为第一个参数的数据类型，因此下面的操作不会出错
select nvl('a',sysdate) from dual;
COALESCE期望一致的数据类型。
select coalesce('a',sysdate) from dual;
将抛出'不一致的数据类型错误'
在计划处理方面也有不同。
当搜索包含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。
事实上，我不能同意每一种说法。
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只能有2个参数。Coalesce可能有2个以上。
Select nvl("， "，1) from dual;//Result: ORA-00909: invalid number of arguments .(无效参数个数。 从dual中选择coalesce("， "，'1');//返回1
推荐文章
- NVL和Coalesce之间的Oracle差异
- 在SQL server查询中将NULL替换为0
- 在SQL中修改表的模式名
- 如何在SQL Server 2005的一条语句中更新两个表?
- 如何创建临时表与SELECT * INTO tempTable从CTE查询
- 用于查找计数为>的记录的SQL查询
- “从Table1左连接Table2”和“从Table2右连接Table1”可以互换吗?
- 在SQL Server的选择语句中使用带TOP的变量，而不是动态的
- 自然连接和内部连接的区别
- MySQL现在()+1天
- 在SQL中转换月号到月名函数
- 不可重复读和幻影读的区别是什么?
- 改变一个varchar列的最大长度?
- 外键约束:何时使用ON UPDATE和ON DELETE
- ORA-00054:资源繁忙，指定NOWAIT或超时获取
