oracle中的判断值为null的函数集合

NVL函数标准格式：NVL(expr1, expr2)

NVL2函数标准格式：NVL2(expr1, expr2, expr3)

很明显可以看出NVL和NVL2函数之间接收的参数的个数不同。

NVL函数使用：

在使用上,NVL函数允许您在查询结果中使用字符串替换null（返回为空白）。如果expr1是null，那么NVL将返回expr2。如果expr1不是空的，那么NVL将返回expr1。

expr1和expr2的参数可以有任何数据类型。如果它们的数据类型是不同的，那么Oracle数据库就会隐式地将其中一个转换为另一个。如果它们不能隐式地转换，那么数据库返回一个错误。

隐式转换的实现如下：
1.如果expr1是字符数据，那么Oracle数据库将expr2转换为expr1的数据类型，然后进行比较，并在expr1的字符集中返回VARCHAR2。
2.如果expr1是数字，那么Oracle数据库将确定哪个参数具有最高的数字优先级，隐式地将另一个参数转换为该数据。

举例如下：

下面的例子返回一个员工姓名和佣金的列表，如果员工没有得到佣金，就用“不适用”代替：

SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable') commission
  FROM employees
  WHERE last_name LIKE 'B%'
  ORDER BY last_name;
 
LAST_NAME                 COMMISSION
------------------------- ----------------------------------------
Baer                      Not Applicable
Baida                     Not Applicable
Banda                     .1
Bates                     .15
Bell                      Not Applicable
Bernstein                 .25
Bissot                    Not Applicable
Bloom                     .2
Bull                      Not Applicable

NVL2函数使用：

NVL2让您根据一个指定的表达式是否为null或not null来确定查询返回的值。如果expr1不是空的，那么NVL2就会返回expr2。如果expr1是null，那么NVL2就会返回expr3。

expr1的参数可以有任何数据类型。expr2和expr3的参数可以有任何数据类型，除了LONG。

如果expr2和expr3的数据类型是不同的，那么Oracle数据库就会隐式地将其中一个转换为另一个。如果它们不能隐式地转换，那么数据库返回一个错误。如果expr2是字符或数字数据，那么隐式转换的实现如下：

1.如果expr2是字符数据，那么Oracle数据库将expr3转换为expr2的数据类型，然后返回一个值，除非expr3是一个空常量。在这种情况下，数据类型转换是不必要的，数据库在expr2的字符集中返回VARCHAR2

2.如果expr2是数字数据，那么Oracle数据库将确定哪个参数具有最高的数字优先级，隐式地将另一个参数转换为该数据类型，并返回该数据类型。

举例如下：下面的例子显示了一些员工的收入是由工资+奖金，还是仅仅是工资，这取决于佣金的员工是否为零。

SELECT last_name, salary,
       NVL2(commission_pct, salary + (salary * commission_pct), salary) income
  FROM employees
  WHERE last_name like 'B%'
  ORDER BY last_name;

LAST_NAME                     SALARY     INCOME
------------------------- ---------- ----------
Baer                           10000      10000
Baida                           2900       2900
Banda                           6200       6820
Bates                           7300       8395
Bell                            4000       4000
Bernstein                       9500      11875
Bissot                          3300       3300
Bloom                          10000      12000
Bull                            4100       4100

以下是与其类似的函数：

COALESCE函数，格式：COALESCE(expr [, expr ]...)

使用：

COALESCE函数返回表达式列表中的第一个非空expr。您必须指定至少两个表达式。如果expr的所有发生都被计算为null，那么该函数将返回null。

Oracle数据库使用了短路的方式进行执行，当第一个不为null的表达式出现时立即返回，而不会查看之后的表达式是否为null，当

所有表达式均是null时，返回第一个不为null的表达式。
如果所有的expr都是数字数据类型，或者任何可以隐式转换为数字数据类型的非数字数据类型，那么Oracle数据库将以最高的数字优先级来确定参数，隐式地将剩余的参数转换为那个数据类型，并返回那个数据类型

COALESCE函数是NVL函数的泛化。可以把COALESCE函数当做多种情况下的case函数，例如：

COALESCE(expr1, expr2)
等价于：
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

COALESCE(expr1, expr2, ..., exprn)
等价于：
CASE WHEN expr1 IS NOT NULL THEN expr1 
   ELSE COALESCE (expr2, ..., exprn) END

举例如下：商场清仓大甩卖，使用oe.product_information表，它给所有有标价的产品提供10%的折扣。如果没有标价，那么销售价格就是最低价格。如果没有最低价格，那么销售价格是“5”：

SELECT product_id, list_price, min_price,
       COALESCE(0.9*list_price, min_price, 5) "Sale"
  FROM product_information
  WHERE supplier_id = 102050
  ORDER BY product_id;

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      1769         48                  43.2
      1770                    73         73
      2378        305        247      274.5
      2382        850        731        765
      3355                                5

文档链接：https://docs.oracle.com/database/121/SQLRF/functions131.htm#SQLRF00684