Oracle DB NVL、NVL2、NULLIF、COALESCE函数
下列函数可用于任何数据类型，且适合使用空值的场合：
NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2, ..., exprn)
下列函数可用于任何数据类型，且适合表达式列表中使用空值的场合。
NVL函数
将空值转换为实际值：
可以使用的数据类型为日期、字符和数字。
数据类型必须匹配：
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN-97')
– NVL(job_id,'NoJob Yet')
要将空值转换为实际值，请使用NVL函数。
语法
NVL (expr1, expr2)
在该语法中：
expr1是可能包含空值的源值或表达式
expr2是用于转换空值的目标值
可以使用NVL函数转换任意数据类型，但是返回值始终与expr1具有相同的数据类型。
各种数据类型的NVL转换
使用NVL函数
hr@TEST0924> SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees; LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL ------------------------- ---------- --------------------- ---------- OConnell 2600 0 31200 Khoo 3100 0 37200 Vargas 2500 0 30000 Russell 14000 .4 235200 Bernstein 9500 .25 142500
要计算所有雇员的年度报酬，需要将月薪乘以12，然后再加上佣金百分比：
hr@TEST0924> SELECT last_name, salary, commission_pct, (salary*12) + (salary*12*commission_pct) AN_SAL FROM employees; LAST_NAME SALARY COMMISSION_PCT AN_SAL ------------------------- ---------- -------------- ---------- OConnell 2600 Vargas 2500 Russell 14000 .4 235200 Taylor 3200
请注意，仅为那些领取佣金的雇员计算了年度报酬。如果表达式中的任何列值为空，则结果也为空。要计算所有雇员的年度报酬，必须将空值转换为数字，然后才能应用算术运算符。在示例中，NVL函数用于将空值转换为零。
使用NVL2函数
hr@TEST0924> SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') income FROM employees WHERE department_id IN (50, 80); LAST_NAME SALARY COMMISSION_PCT INCOME ------------------------- ---------- -------------- -------- OConnell 2600 SAL Russell 14000 .4 SAL+COMM Bernstein 9500 .25 SAL+COMM
NVL2函数首先会检查第一个表达式。如果第一个表达式不为空，则NVL2函数会返回第二个表达式。如果第一个表达式为空，则返回第三个表达式。
语法
NVL2(expr1, expr2, expr3)
在该语法中：
expr1是可能包含空值的源值或表达式
expr2是expr1不为空时返回的值
expr3是expr1为空时返回的值
在示例中，检查的是COMMISSION_PCT列。如果检测到值，则返回文本文字值SAL+COMM。如果COMMISSION_PCT列包含空值，则返回文本文字值SAL。
注：参数expr1可以是任意数据类型。参数expr2和expr3可以是除LONG之外的任意数据类型。
使用NULLIF函数
hr@TEST0924> SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2",NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees; FIRST_NAME expr1 LAST_NAME expr2 RESULT -------------------- ---------- ------------------------- ---------- ---------- Nanette 7 Cambrault 9 7 John 4 Chen 4
NULLIF函数用于对两个表达式进行比较。
语法
NULLIF (expr1, expr2)
在该语法中：
NULLIF用于对expr1和expr2进行比较。如果它们相等，则函数返回空值。如果不相等，则函数返回expr1。但不能为expr1指定文字值NULL。
在示例中，对EMPLOYEES表中名字的长度和EMPLOYEES表中姓氏的长度进行了比较。如果姓氏和名字的长度相等，则显示空值。如果姓氏和名字的长度不相等，
则显示名字的长度。
注：NULLIF函数在逻辑上等效于下面的CASE表达式。CASE表达式将在后续进行介绍：
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
使用COALESCE函数
COALESCE函数优于NVL函数之处在于COALESCE函数可以接受多个备选值。
如果第一个表达式不为空，则COALESCE函数返回该表达式；否则，它将对其余的表达式执行COALESCE运算。
COALESCE函数用于返回列表中的第一个非空表达式。
语法
COALESCE (expr1, expr2, ... exprn)
在该语法中：
expr1返回此表达式（如果它不为空）
expr2返回此表达式（如果第一个表达式为空，而此表达式不为空）
exprn返回此表达式（如果前面的表达式都为空）
请注意，所有表达式都必须具有相同的数据类型。
使用COALESCE函数
hr@TEST0924> SELECT last_name,employee_id, COA