nvl（A，B）当A为空时就返回B；
nvl2(A,B,C)当A不为空返回B，A为空时返回C
nullif（A,B）当A和B相等时返回空，不等时返回A
coalese（A,B,C,D）返回参数中第一个非空的
1.使用nvl函数
SQL> SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NV 2 L(commission_pct, 0)) AN_SAL
FROM employees;
3
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
------------------------- ---------- --------------------- ----------
OConnell 2600 0 31200
Grant 2600 0 31200
Whalen 4400 0 52800
Hartstein 13000 0 156000
Fay 6000 0 72000
Mavris 6500 0 78000
Baer 10000 0 120000
Higgins 12008 0 144096
Gietz 8300 0 99600
King 24000 0 288000
Kochhar 17000 0 204000
2.使用nvl2函数
SQL> SELECT last_name, salary, commission_pct,
NVL2(commission_pct,
2 3 'SAL+COMM', 'SAL') income
FROM employees WHERE department_id IN (50, 80);
4
LAST_NAME SALARY COMMISSION_PCT INCOME
------------------------- ---------- -------------- --------
OConnell 2600 SAL
Grant 2600 SAL
Weiss 8000 SAL
Fripp 8200 SAL
Kaufling 7900 SAL
Vollman 6500 SAL
Mourgos 5800 SAL
Nayer 3200 SAL
Mikkilineni 2700 SAL
Landry 2400 SAL
Markle 2200 SAL
3.使用nullif函数
SQL> SELECT first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_n 2 ame) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employees;
3 4
FIRST_NAME expr1 LAST_NAME expr2 RESULT
-------------------- ---------- ------------------------- ---------- ----------
Ellen 5 Abel 4 5
Sundar 6 Ande 4 6
Mozhe 5 Atkinson 8 5
David 5 Austin 6 5
Hermann 7 Baer 4 7
Shelli 6 Baida 5 6
Amit 4 Banda 5 4
Elizabeth 9 Bates 5 9
Sarah 5 Bell 4 5
David 5 Bernstein 9 5
Laura 5 Bissot 6 5
4.使用 COALESCE 函数
SQL> SELECT last_name, employee_id,
COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_ 2 id),
'No commission and no manager')
FROM employees;
3 4
LAST_NAME EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
OConnell 198 124
Grant 199 124
Whalen 200 101
Hartstein 201 100
Fay 202 201
Mavris 203 101
Baer 204 101
Higgins 205 101
Gietz 206 205
King 100 No commission and no manager
Kochhar 101 100