Oracle 11g Release 1 (11.1) 单行函数——NULL 相关函数
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions001.htm#autoId14
本文内容
- COALESCE
- LNNVL
- NULLIF
- NVL
- NVL2
NULL 相关的函数方便处理 null。
COALESCE(expr1, expr2, ..., exprn)
COALESCE 返回在表达式列表中第一个非 null 的 expr。必须指定至少两个表达式。若所有 expr 为 null，则函数返回 null。
Oracle 数据库使用 short-circuit evaluation。数据库计算每个 expr 的值，并确定是否为 NULL，而不是计算所有 expr 后，再确定是否为 NULL。
若所有 expr 出现数字类型，或可以隐式转换成数字类型的任何非数字类型，则 Oracle 数据库用最高数字优先（highest numeric precedence）来确定参数，隐式把剩余参数转换成数字类型，并返回。
该函数是 NVL 函数的一般形式。
可以使用 COALESCE 实现 CASE 表达式。例如，
COALESCE (expr1, expr2)
等价于：
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
以此类推，
COALESCE (expr1, expr2, ..., exprn), for n>=3
等价于：
CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE COALESCE (expr2, ..., exprn) END
示例 1：演示使用 Oracle OE.product_information 表组织清仓甩卖。打折 10%。若 list_price 列上没有，则实际价格为最低价格 min_price。若没有最低价格，则实际价格为 5。
SQL> SELECT product_id,
2 list_price,
3 min_price,
4 COALESCE(0.9 * list_price, min_price, 5) "Sale"
5 FROM product_information
6 WHERE supplier_id = 102050
7 ORDER BY product_id, list_price, min_price, "Sale";
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
SQL>
LNNVL(condition)
当条件中一个或两个操作数为空时，LNNVL 提供了一个简洁的方式评估条件。该函数只能用在 WHERE 查询子句。它需要一个条件作为参数，若条件为 FALSE 或 UNKNOWN，则返回 TRUE；若条件为 TRUE，则返回 FALSE。LNNVL 可用在任何一个标量表达式，但在 IS [NOT] NULL、AND、OR 环境是无效的，否则必须考虑潜在的 null。
有时，Oracle 数据库内部使用 LNNVL 函数，把 NOT IN 条件重写为 NOT EXISTS 条件。这种情况，执行计划的输出显示了计划表输出中的这个操作。条件可以计算任何标值，但不能包含 AND、OR 或 BETWEEN 的复合条件。
下表显示给定 a=2，b 为 null，LNNVL 返回值。
| 条件 | 条件为 TRUE？ | LNNVL 返回值 |
| a = 1 | FALSE | TRUE |
| a = 2 | TRUE | FALSE |
| a IS NULL | FALSE | TRUE |
| b = 1 | UNKNOWN | TRUE |
| b IS NULL | TRUE | FALSE |
| a = b | UNKNOWN | TRUE |
示例 2：演示假设你想知道提成率小于 20% 的员工数量，包括没有收到提成的员工。下面查询只返回收到小于 20% 提成的员工数量。
SQL> SELECT COUNT(*) FROM employees WHERE commission_pct < .2;
COUNT(*)
----------
11
SQL>
若包含没有收到提成的 72 个员工,可以使用 LNNVL 函数：
SQL> SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2);
COUNT(*)
----------
83
SQL>
NULLIF(expr1,expr2)
NULLIF 比较 expr1 和 expr2。若它们相等，则函数返回 null。若不等，则返回 expr1。不能为 expr1 指定 NULL 的字面义。
若这两个参数是数字类型，则 Oracle 数据库用最高数字优先来确定参数，隐式把其他参数转换成该类型，并返回该类型。若参数不是数字型，则它们必须是相同类型，否则，Oracle 返回一个错误。
NULLIF 函数逻辑上等价于如下 CASE 表达式：
CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END
示例 3：演示从 Oracle HR.employees 表选择自从员工被雇佣以来，是否改变过工作。
SQL> SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
2 FROM employees e, job_history j
3 WHERE e.employee_id = j.employee_id
4 ORDER BY last_name, "Old Job ID";
LAST_NAME Old Job ID
------------------------- ----------
De Haan AD_VP
Hartstein MK_MAN
Kaufling ST_MAN
Kochhar AD_VP
Kochhar AD_VP
Raphaely PU_MAN
Taylor SA_REP
Taylor
Whalen AD_ASST
Whalen
10 rows selected
SQL>
NVL(expr1,expr2)
NVL 可以用查询结果中的字符串替换 null。若 expr1 为 null，则 NVL 返回 expr2。若 expr1 不为 null，则返回 expr1。
参数 expr1 和 expr2 可以是任何类型。若它们的类型不同，则 Oracle 数据库隐式把它们转换。若它们不能隐式转换，则 Oracle 返回一个错误。隐式转换按如下方式完成：The implicit conversion is implemented as follows:
- 若 expr1 是字符，则 Oracle 在比较它们前，把 expr2 转换成 expr1 的类型，并按 expr1 的字符集返回 VARCHAR2。
- 若 expr1 是数字，则 Oracle 用最高数字优先来确定，隐式转换，并返回该类型。
示例 4：演示员工姓名和提成，并用 "Not Applicable" 替换没有收到提成的员工。
SQL> SELECT last_name,
2 NVL(TO_CHAR(commission_pct), 'Not Applicable') "COMMISSION"
3 FROM employees
4 WHERE last_name LIKE 'B%'
5 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
9 rows selected
SQL>
NVL2(expr1,expr2,expr3)
NVL2 函数根据表达式是否为 null 确定值。若 expr1 为非 null，则 NVL2 返回 expr2。若 expr1 为 null，则返回 expr3。
参数 expr1 可以是任何类型。参数 expr2 和 expr3 可以是除 LONG 外的任何类型。
若 expr2 和 expr3 类型不同：
- 若 expr2 是字符数据，则 Oracle 数据库在比较前把 expr3 转换成 expr2 的类型，除非 expr3 是 null 常量。此时，不需要数据类型转换。Oracle 返回 expr2 字符集的 VARCHAR2 值。
- 若 expr2 是数字，则 Oracle 用最高数字优先（highest numeric precedence）来确定，隐式转换，并返回该类型。
示例 5：演示部分员工的收入，收入由工资加提成组成，这依赖于 commission_pct 列是否为 null。
SQL> SELECT last_name,
2 salary,
3 NVL2(commission_pct, salary + (salary * commission_pct), salary) income
4 FROM employees
5 WHERE last_name like 'B%'
6 ORDER BY last_name;
LAST_NAME SALARY INCOME
------------------------- ---------- ----------
Baer 10000.00 10000
Baida 2900.00 2900
Banda 6200.00 6820
Bates 7300.00 8395
Bell 4000.00 4000
Bernstein 9500.00 11875
Bissot 3300.00 3300
Bloom 10000.00 12000
Bull 4100.00 4100
9 rows selected
SQL>
