Conditional Expressions
- Prvoide the use of the IF-THEN-ELSE logic within a SQL statement
- Use two methods
- -CASE expression(SQL标准)
- -DECODE function(专属Oracle)
CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1 [ WHEN comparison_expr2 THEN return_expr2 WHEN comparison_expr3 THEN return_expr3 ... WHEN comparison_exprn THEN return_exprn ELSE else_expr ] END
Using the CASE Expression
Demo-01:CASE
View Code
SQL> SELECT last_name,job_id,salary,CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees; LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- OConnell SH_CLERK 2600 2600 Grant SH_CLERK 2600 2600 Whalen AD_ASST 4400 4400 Hartstein MK_MAN 13000 13000 Fay MK_REP 6000 6000 Mavris HR_REP 6500 6500 Baer PR_REP 10000 10000 Higgins AC_MGR 12008 12008 Gietz AC_ACCOUNT 8300 8300 King AD_PRES 24000 24000 Kochhar AD_VP 17000 17000 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- De Haan AD_VP 17000 17000 Hunold IT_PROG 9000 9900 Ernst IT_PROG 6000 6600 Austin IT_PROG 4800 5280 Pataballa IT_PROG 4800 5280 Lorentz IT_PROG 4200 4620 Greenberg FI_MGR 12008 12008 Faviet FI_ACCOUNT 9000 9000 Chen FI_ACCOUNT 8200 8200 Sciarra FI_ACCOUNT 7700 7700 Urman FI_ACCOUNT 7800 7800 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Popp FI_ACCOUNT 6900 6900 Raphaely PU_MAN 11000 11000 Khoo PU_CLERK 3100 3100 Baida PU_CLERK 2900 2900 Tobias PU_CLERK 2800 2800 Himuro PU_CLERK 2600 2600 Colmenares PU_CLERK 2500 2500 Weiss ST_MAN 8000 8000 Fripp ST_MAN 8200 8200 Kaufling ST_MAN 7900 7900 Vollman ST_MAN 6500 6500 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Mourgos ST_MAN 5800 5800 Nayer ST_CLERK 3200 3680 Mikkilineni ST_CLERK 2700 3105 Landry ST_CLERK 2400 2760 Markle ST_CLERK 2200 2530 Bissot ST_CLERK 3300 3795 Atkinson ST_CLERK 2800 3220 Marlow ST_CLERK 2500 2875 Olson ST_CLERK 2100 2415 Mallin ST_CLERK 3300 3795 Rogers ST_CLERK 2900 3335 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Gee ST_CLERK 2400 2760 Philtanker ST_CLERK 2200 2530 Ladwig ST_CLERK 3600 4140 Stiles ST_CLERK 3200 3680 Seo ST_CLERK 2700 3105 Patel ST_CLERK 2500 2875 Rajs ST_CLERK 3500 4025 Davies ST_CLERK 3100 3565 Matos ST_CLERK 2600 2990 Vargas ST_CLERK 2500 2875 Russell SA_MAN 14000 14000 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Partners SA_MAN 13500 13500 Errazuriz SA_MAN 12000 12000 Cambrault SA_MAN 11000 11000 Zlotkey SA_MAN 10500 10500 Tucker SA_REP 10000 12000 Bernstein SA_REP 9500 11400 Hall SA_REP 9000 10800 Olsen SA_REP 8000 9600 Cambrault SA_REP 7500 9000 Tuvault SA_REP 7000 8400 King SA_REP 10000 12000 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Sully SA_REP 9500 11400 McEwen SA_REP 9000 10800 Smith SA_REP 8000 9600 Doran SA_REP 7500 9000 Sewall SA_REP 7000 8400 Vishney SA_REP 10500 12600 Greene SA_REP 9500 11400 Marvins SA_REP 7200 8640 Lee SA_REP 6800 8160 Ande SA_REP 6400 7680 Banda SA_REP 6200 7440 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Ozer SA_REP 11500 13800 Bloom SA_REP 10000 12000 Fox SA_REP 9600 11520 Smith SA_REP 7400 8880 Bates SA_REP 7300 8760 Kumar SA_REP 6100 7320 Abel SA_REP 11000 13200 Hutton SA_REP 8800 10560 Taylor SA_REP 8600 10320 Livingston SA_REP 8400 10080 Grant SA_REP 7000 8400 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Johnson SA_REP 6200 7440 Taylor SH_CLERK 3200 3200 Fleaur SH_CLERK 3100 3100 Sullivan SH_CLERK 2500 2500 Geoni SH_CLERK 2800 2800 Sarchand SH_CLERK 4200 4200 Bull SH_CLERK 4100 4100 Dellinger SH_CLERK 3400 3400 Cabrio SH_CLERK 3000 3000 Chung SH_CLERK 3800 3800 Dilly SH_CLERK 3600 3600 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Gates SH_CLERK 2900 2900 Perkins SH_CLERK 2500 2500 Bell SH_CLERK 4000 4000 Everett SH_CLERK 3900 3900 McCain SH_CLERK 3200 3200 Jones SH_CLERK 2800 2800 Walsh SH_CLERK 3100 3100 Feeney SH_CLERK 3000 3000 107 rows selected.
DECODE Function
Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THEN-ELSE statement:
DECODE(col|expression,search1,result1[,serach2,result2,...,][,default])
Demo-01:DECODE
View Code
SQL> SELECT last_name,job_id,salary,DECODE(job_id,'IT_PROG',1.10*salary,'ST_CLERK',1.15*salary,'SA_REP',1.20*salary,salary) "REVISED_SALARY" FROM employees; LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- OConnell SH_CLERK 2600 2600 Grant SH_CLERK 2600 2600 Whalen AD_ASST 4400 4400 Hartstein MK_MAN 13000 13000 Fay MK_REP 6000 6000 Mavris HR_REP 6500 6500 Baer PR_REP 10000 10000 Higgins AC_MGR 12008 12008 Gietz AC_ACCOUNT 8300 8300 King AD_PRES 24000 24000 Kochhar AD_VP 17000 17000 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- De Haan AD_VP 17000 17000 Hunold IT_PROG 9000 9900 Ernst IT_PROG 6000 6600 Austin IT_PROG 4800 5280 Pataballa IT_PROG 4800 5280 Lorentz IT_PROG 4200 4620 Greenberg FI_MGR 12008 12008 Faviet FI_ACCOUNT 9000 9000 Chen FI_ACCOUNT 8200 8200 Sciarra FI_ACCOUNT 7700 7700 Urman FI_ACCOUNT 7800 7800 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Popp FI_ACCOUNT 6900 6900 Raphaely PU_MAN 11000 11000 Khoo PU_CLERK 3100 3100 Baida PU_CLERK 2900 2900 Tobias PU_CLERK 2800 2800 Himuro PU_CLERK 2600 2600 Colmenares PU_CLERK 2500 2500 Weiss ST_MAN 8000 8000 Fripp ST_MAN 8200 8200 Kaufling ST_MAN 7900 7900 Vollman ST_MAN 6500 6500 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Mourgos ST_MAN 5800 5800 Nayer ST_CLERK 3200 3680 Mikkilineni ST_CLERK 2700 3105 Landry ST_CLERK 2400 2760 Markle ST_CLERK 2200 2530 Bissot ST_CLERK 3300 3795 Atkinson ST_CLERK 2800 3220 Marlow ST_CLERK 2500 2875 Olson ST_CLERK 2100 2415 Mallin ST_CLERK 3300 3795 Rogers ST_CLERK 2900 3335 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Gee ST_CLERK 2400 2760 Philtanker ST_CLERK 2200 2530 Ladwig ST_CLERK 3600 4140 Stiles ST_CLERK 3200 3680 Seo ST_CLERK 2700 3105 Patel ST_CLERK 2500 2875 Rajs ST_CLERK 3500 4025 Davies ST_CLERK 3100 3565 Matos ST_CLERK 2600 2990 Vargas ST_CLERK 2500 2875 Russell SA_MAN 14000 14000 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Partners SA_MAN 13500 13500 Errazuriz SA_MAN 12000 12000 Cambrault SA_MAN 11000 11000 Zlotkey SA_MAN 10500 10500 Tucker SA_REP 10000 12000 Bernstein SA_REP 9500 11400 Hall SA_REP 9000 10800 Olsen SA_REP 8000 9600 Cambrault SA_REP 7500 9000 Tuvault SA_REP 7000 8400 King SA_REP 10000 12000 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Sully SA_REP 9500 11400 McEwen SA_REP 9000 10800 Smith SA_REP 8000 9600 Doran SA_REP 7500 9000 Sewall SA_REP 7000 8400 Vishney SA_REP 10500 12600 Greene SA_REP 9500 11400 Marvins SA_REP 7200 8640 Lee SA_REP 6800 8160 Ande SA_REP 6400 7680 Banda SA_REP 6200 7440 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Ozer SA_REP 11500 13800 Bloom SA_REP 10000 12000 Fox SA_REP 9600 11520 Smith SA_REP 7400 8880 Bates SA_REP 7300 8760 Kumar SA_REP 6100 7320 Abel SA_REP 11000 13200 Hutton SA_REP 8800 10560 Taylor SA_REP 8600 10320 Livingston SA_REP 8400 10080 Grant SA_REP 7000 8400 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Johnson SA_REP 6200 7440 Taylor SH_CLERK 3200 3200 Fleaur SH_CLERK 3100 3100 Sullivan SH_CLERK 2500 2500 Geoni SH_CLERK 2800 2800 Sarchand SH_CLERK 4200 4200 Bull SH_CLERK 4100 4100 Dellinger SH_CLERK 3400 3400 Cabrio SH_CLERK 3000 3000 Chung SH_CLERK 3800 3800 Dilly SH_CLERK 3600 3600 LAST_NAME JOB_ID SALARY REVISED_SALARY ------------------------- ---------- ---------- -------------- Gates SH_CLERK 2900 2900 Perkins SH_CLERK 2500 2500 Bell SH_CLERK 4000 4000 Everett SH_CLERK 3900 3900 McCain SH_CLERK 3200 3200 Jones SH_CLERK 2800 2800 Walsh SH_CLERK 3100 3100 Feeney SH_CLERK 3000 3000 107 rows selected.
Using the DECODE function
Display the applicable tax rate for each employee in department 80:
View Code
SQL> SELECT last_name,salary,DECODE(TRUNC(salary/2000,0),0,0.00,1,0.09,2,0.20,3,0.30,4,0.40,5,0.42,6,0.44,0.45)TAX_RATE FROM employees WHERE department_id = 80; LAST_NAME SALARY TAX_RATE ------------------------- ---------- ---------- Russell 14000 .45 Partners 13500 .44 Errazuriz 12000 .44 Cambrault 11000 .42 Zlotkey 10500 .42 Tucker 10000 .42 Bernstein 9500 .4 Hall 9000 .4 Olsen 8000 .4 Cambrault 7500 .3 Tuvault 7000 .3 LAST_NAME SALARY TAX_RATE ------------------------- ---------- ---------- King 10000 .42 Sully 9500 .4 McEwen 9000 .4 Smith 8000 .4 Doran 7500 .3 Sewall 7000 .3 Vishney 10500 .42 Greene 9500 .4 Marvins 7200 .3 Lee 6800 .3 Ande 6400 .3 LAST_NAME SALARY TAX_RATE ------------------------- ---------- ---------- Banda 6200 .3 Ozer 11500 .42 Bloom 10000 .42 Fox 9600 .4 Smith 7400 .3 Bates 7300 .3 Kumar 6100 .3 Abel 11000 .42 Hutton 8800 .4 Taylor 8600 .4 Livingston 8400 .4 LAST_NAME SALARY TAX_RATE ------------------------- ---------- ---------- Johnson 6200 .3 34 rows selected.
Summary
In this lesson,you should have learned how to:
- After data format for display using functions
- Convert column data types using functions
- Use NVL functions
- Use IF-THEN-ELSE logic and other conditional expressions in a SELECT statement.