oracle笔记（十三）条件表达式
条件表达式 ：IF-THEN-ELSE 逻辑
CASE expr WHEN comparison_expr1 THEN return_expr1
???????? [WHEN comparison_expr2 THEN return_expr2
????????? WHEN comparison_exprn THEN return_exprn
????????? ELSE else_expr]
END
?
DECODE(col|expression, search1, result1
????? ?????????? ???[, search2, result2,...,]
????? ?????????? ???[, default])
?
//为职位是Manager的员工发放5000元的奖金
SQL> select ename,job,case job when 'MANAGER' then nvl(sal,0)+5000? end? as "工资" from emp;
//员工的工资
SQL> select ename ,job,
? 2? case job when 'MANAGER' then nvl(sal,0)+5000+nvl(comm,0)
? 3? else nvl(sal,0)+nvl(comm,0)
? 4? end
? 5? from emp;
//改写成 decode的写法
SQL> select ename,job
? 2? ,decode(job,'MANAGER', nvl(sal,0)+5000+nvl(comm,0),
? 3? 'CLERK',nvl(sal,0)+nvl(comm,0)+200,
? 4? nvl(sal,0)+nvl(comm,0)) as "工资"
?5? from emp;
//作业： 当员工为Manger 加5000员? 当员工为SALESMAN 加1000 当员工为 clerk加500
SQL> select ename,job,case job when 'MANAGER' then nvl(sal,0)+5000+nvl(comm,0)
when 'SALESMAN' then nvl(sal,0)+1000+nvl(comm,0)
?when 'CLERK'then nvl(sal,0)+500+nvl(comm,0)
else nvl(sal,0)+nvl(comm,0)
end from emp;
ENAME????? JOB?????? CASEJOBWHEN'MANAGER'THENNVL(SA
---------- --------- ------------------------------
SMITH????? CLERK?????????????????????????????? 1300
ALLEN????? SALESMAN??????????????????????????? 2900
WARD?????? SALESMAN???????? ???????????????????2750
JONES????? MANAGER???????????????????????????? 7975
MARTIN???? SALESMAN??????????????????????????? 3650
BLAKE????? MANAGER???????????????????????????? 7850
CLARK????? MANAGER???????????????????????????? 7450
SCOTT????? ANALYST????? ???????????????????????3000
KING?????? PRESIDENT?????????????????????????? 5000
TURNER???? SALESMAN??????????????????????????? 2500
ADAMS????? CLERK?????????????????????????????? 1600
JAMES????? CLERK?????????????????????????????? 1450
FORD?????? ANALYST? ???????????????????????????3000
MILLER???? CLERK?????????????????????????????? 1800
kou%kou%????????????????????????????????????????? 0
xiao%lin????????????????????????????????????????? 0
xiao%lin????????????????????????????????????????? 0
?
17 rows selected
SQL> select ename,job,decode(
? 2? job,'MANAGER',nvl(sal,0)+5000+nvl(comm,0),
? 3? 'SALESMAN',nvl(sal,0)+1000+nvl(comm,0),
? 4? 'CLERK',nvl(sal,0)+500+nvl(comm,0))
? 5? as "工资" from emp;
ENAME????? JOB?????????????? 工资
---------- --------- ----------
SMITH????? CLERK?????????? 1300
ALLEN????? SALESMAN??????? 2900
WARD?????? SALESMAN??????? 2750
JONES????? MANAGER???????? 7975
MARTIN???? SALESMAN??????? 3650
BLAKE????? MANAGER???????? 7850
CLARK????? MANAGER???????? 7450
SCOTT????? ANALYST? ?
KING?????? PRESIDENT
TURNER???? SALESMAN??????? 2500
ADAMS????? CLERK?????????? 1600
JAMES????? CLERK?????????? 1450
FORD?????? ANALYST??
MILLER???? CLERK?????????? 1800
kou%kou%????????????
xiao%lin????????????
xiao%lin????????????
?
17 rows selected
?
