oracle数据库null函数,[数据库]Oracle 11g Release 1 (11.1) NULL 相关函数

[数据库]Oracle 11g Release 1 (11.1) NULL 相关函数

0 2012-08-05 00:00:04

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>

转载请保留本文网址：http://www.shaoqun.com/a/37670.html

*特别声明：以上内容来自于网络收集，著作权属原作者所有，如有侵权，请联系我们：admin@shaoqun.com。

oracle

0