CASE 表达式
CASE expr
WHEN comparison_expr1 THEN return_expr1
WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr
END
用中文来说,case 、when 、else,
即 如果 条件1成立 ,那么执行条件1,条件都不符合则执行else ,例如例,如果job_id='IT_PROG',那么将薪资*1.10
例:
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 hr.employees;
//从emp表中返回 列名"REVISED_SALARY",如果JOB_ID='ST_CLERK',则返回的值=1.15*salary,如果JOB_ID='SA_REP' ,则返回的值=1.20*salary,否则直接返回salary的值
SYS@ocp> SELECT last_name, job_id, salary,
2 CASE job_id
3 WHEN 'IT_PROG' THEN 1.10*salary
4 WHEN 'ST_CLERK' THEN 1.15*salary
5 WHEN 'SA_REP' THEN 1.20*salary
6 ELSE salary
7 END
8 "REVISED_SALARY"
9 FROM hr.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
De Haan AD_VP 17000 17000
Hunold IT_PROG 9000 9900
Ernst IT_PROG 6000 6600
Kaufling ST_MAN 7900 7900
Vollman ST_MAN 6500 6500
Mourgos ST_MAN 5800 5800
Landry ST_CLERK 2400 2760
Markle ST_CLERK 2200 2530
Bissot ST_CLERK 3300 3795
Atkinson ST_CLERK 2800 3220
Tucker SA_REP 10000 12000
Bernstein SA_REP 9500 11400
Hall SA_REP 9000 10800
Olsen SA_REP 8000 9600