Oracle DB NVL、NVL2、NULLIF、COALESCE函数
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, COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id), 'No commission and no manager') FROM employees; LAST_NAME EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR ------------------------- ----------- ---------------------------------------- OConnell 198 124 Gietz 206 205 King 100 No commission and no manager Bernstein 151 .25
在幻灯片中所示的示例中，如果manager_id值不为空，则会显示出来。如果manager_id值为空，则会显示commission_pct。如果manager_id和commission_pct值都为空，则会显示“No commission and no manager”。请注意，已应用TO_CHAR函数，所以所有表达式都具有相同的数据类型。
示例：
对于不领取佣金的雇员，你的组织想要将其薪金增加$2,000；对于领取佣金的雇员，在查询中应计算新的薪金，该薪金等于现有薪金加上佣金额。
hr@TEST0924> SELECT last_name, salary, commission_pct, COALESCE((salary+(commission_pct*salary)), salary+2000, salary) "New Salary" FROM employees; LAST_NAME SALARY COMMISSION_PCT New Salary ------------------------- ---------- -------------- ---------- OConnell 2600 4600 Bernstein 9500 .25 11875 Hall 9000 .25 11250
注：请检查输出。对于不领取佣金的雇员，New Salary 列显示增加了$2,000 后的薪金；对于领取佣金的雇员，New Salary 列显示计算的佣金额加上薪金。
