Oracle 与Null值相关的 Function

与NULL值相关的几个通用函数：

1、LNNVL（condition）（取反）

作用：用于某个语句的where子句中的条件，如果条件为True就返回False；如果条件为Unknown或者False就返回True。该函数不能用于复合条件如AND,OR,BETWEEN中。

示例：select * from A where innvl(year is not null); //取A表中year为空的记录

select * from A where innvl(year != 12345); //取A表中year为12345或者null

应用：pyfa_kcxx和kkjh_kcxx表的xnxq或者kkxq不相同（不相等且不为空）时的条件：

lnnvl(pyfa_kcxx.xnxq = kkjh_kcxx.xnxq) and (pyfa_kcxx.xnxq is not null or kkjh_kcxx.xnxq is not null))

or (lnnvl(pyfa_kcxx.kkxq = kkjh_kcxx.kkxq) and (pyfa_kcxx.kkxq is not null or kkjh_kcxx.kkxq is not null))

 

2、NVL (original,ifnull)

作用：两个参数的数据类型必须兼容，判断original是否为空，如果为空，则返回ifnull值，否则返回original。

备注：当两个参数数据类型不同时，oracle会将两个参数进行隐式转换，如果不能隐式转换刚会报错，隐式转换规则如下：

        1.如果参数1为字符型，则把参数2转换为参数1的类型，返回值为VARCHAR2

        2.如果参数1为数值型，则判断两个参数的最高数值优先级（如双精实数比单精实数优先级高），然后转换成高优先级的数值，返回该类型的值.

3、NVL2 (original,ifnotnull,ifnull)

作用：判断original是否为空，如果为空，则返回ifnull值，否则返回ifnotnull值。

备注：ifnotnull和ifnull类型不同的话，ifnull会转换为ifnotnull的类型，转换不了会报错。

 

4、NULLIF (expr1,expr2)

作用:如果expr1和expr2相等则返回null,否则返回expr1

 

5、COALESCE (expr1,expr2,expr3,...exprm)

作用：返回表达式中第一个非空表达式，如果都是空则返回null。

备注：所有表达式必须时相同类型，或者可以隐式转换为相同的类型，否则报错。

 

Oracle隐式转换带来的问题：

NAME和VARCHAR2之间可以进行隐式转换，其中VARCHAR2->NUMBER不会导致索引失效，NUMBER->VARCHAR2会让索引失效；

VARCHAR2和NVARCHAR2数据类型不一致导致的隐式数据类型转换，会导致索引失效。

因此这种隐式转换，是需要注意避免。

 

Oracle显示数据类型转换：