oracle中null值相关函数汇总

╰+哭是因爲堅強的太久メ 2021-09-27 07:40 403阅读 0赞

### nvl ###

![这里写图片描述][70]  
最常用的函数。它接收两个参数。返回第一个非空值。若两个参数都为空，返回null。

例：

select nvl(null, 9695) from dual;  --9695
    select nvl(123, null) from dual;   --123
    select nvl(123, 9695) from dual;   --123
    select nvl(null, null) from dual;  --null

### coalesce ###

![这里写图片描述][70 1]  
与nvl非常相似的功能,同样是返回第一个不为空的参数。不过它可以接受更多的参数

select coalesce(1, 3, 4, 5, 6, 7) from dual;

coalesce与nvl很像，官方文档中也这样说

> This function is a generalization of the NVL function.

不过需要注意的是，coalesce对参数类型的要求更严格。它无法向nvl一样最大限度的自动转换数据类型。

select nvl(1, '2') from dual;     --没问题
    select coalesce(1, '2') from dual; --报错

### nvl2 ###

![这里写图片描述][70 2]

> NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

就是个三目运算符啦。如果第一个参数非空，返回第二个参数，否则返回第三个参数。

### nullif ###

![这里写图片描述][70 3]

> NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.

如果两个参数相同，返回null，否则返回第一个参数。  
意外的很有用的一个函数，具体见这篇博客 [oracle 使用nullif解决除数为零的问题][oracle _nullif]

### lnnvl ###

![这里写图片描述][70 4]

> LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used in the WHERE clause of a query, or as the WHEN condition in a searched CASE expression. It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE. LNNVL can be used anywhere a scalar expression can appear, even in contexts where the IS \[NOT\] NULL, AND, or OR conditions are not valid but would otherwise be required to account for potential nulls.  
> Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions. In such cases, output from EXPLAIN PLAN shows this operation in the plan table output. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.

简单来说的话，就是个取反的操作。比如`1 > 2` 返回true、`1 = 1` 返回false。  
咋一看挺没用的，但是别忘了在oracle中与null的比较全都返回false的特性。

with tab1 as (
    select 1 id from dual union all select 3 from dual union all select null from dual ) select * from tab1 where id < 2 ;
    --当我想获取id小于2的值时，直接id < 2即可
    --但是当我想获取id < 2且id为空的值时，则可以这么写
    with tab1 as (
    select 1 id from dual union all select 3 from dual union all select null from dual ) select * from tab1 where lnnvl(id > 2) ;

也就是说，在功能上`lnnvl(id > 2)` 与`id < 2 or id is null` 是等效的。  
不过，文档中有一句话非常令人在意

> Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions. In such cases, output from EXPLAIN PLAN shows this operation in the plan table output.

难道说lnnvl更有效率优势？看来要找个时间研究下。

### nanvl ###

![这里写图片描述][70 5]

> The NANVL function is useful only for floating-point numbers of type BINARY\_FLOAT or BINARY\_DOUBLE. It instructs Oracle Database to return an alternative value n1 if the input value n2 is NaN (not a number). If n2 is not NaN, then Oracle returns n2.  
> This function takes as arguments any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

只用于`BINARY_FLOAT or BINARY_DOUBLE` 的函数。这个真没用过。  
我执行了官方文档中的示例代码，但是一直报错，求大神来解答。

CREATE TABLE float_point_demo (dec_num NUMBER(10,2), bin_double BINARY_DOUBLE, bin_float BINARY_FLOAT);
    
    INSERT INTO float_point_demo VALUES (0,'NaN','NaN');
    
    SELECT bin_float, NANVL(bin_float,0) FROM float_point_demo;

数据库版本  
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi  
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production