oracle条件表达式,oracle札记（十三）条件表达式
oracle笔记(十三)条件表达式
条件表达式 ：IF-THEN-ELSE逻辑
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHENcomparison_expr2THENreturn_expr2
WHENcomparison_exprnTHENreturn_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)+5000endas "工资" from emp;
//员工的工资
SQL> select ename ,job,
2case job when 'MANAGER' then nvl(sal,0)+5000+nvl(comm,0)
3else nvl(sal,0)+nvl(comm,0)
4end
5from 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,
4nvl(sal,0)+nvl(comm,0)) as "工资"
5from 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;
ENAMEJOBCASEJOBWHEN'MANAGER'THENNVL(SA
---------- --------- ------------------------------
SMITHCLERK1300
ALLENSALESMAN2900
WARDSALESMAN2750
JONESMANAGER7975
MARTINSALESMAN3650
BLAKEMANAGER7850
CLARKMANAGER7450
SCOTTANALYST3000
KINGPRESIDENT5000
TURNERSALESMAN2500
ADAMSCLERK1600
JAMESCLERK1450
FORDANALYST3000
MILLERCLERK1800
kou%kou%0
xiao%lin0
xiao%lin0
17 rows selected
SQL> select ename,job,decode(
2job,'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))
5as "工资" from emp;
ENAMEJOB工资
---------- --------- ----------
SMITHCLERK1300
ALLENSALESMAN2900
WARDSALESMAN2750
JONESMANAGER7975
MARTINSALESMAN3650
BLAKEMANAGER7850
CLARKMANAGER7450
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN2500
ADAMSCLERK1600
JAMESCLERK1450
FORDANALYST
MILLERCLERK1800
kou%kou%
xiao%lin
xiao%lin
17 rows selected
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！