1.第一种用法：可以称为简单变量；
SELECT ename,
(CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
WHEN 40 THEN 'OPERATIONS'
ELSE 'Unassigned'
END ) as Department
FROM emp;
ENAME DEPARTMENT
---------- ----------
SMITH RESEARCH
ALLEN Unassigned
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
2.第二种用法：可以称为条件表达式；
SELECT ename, sal, deptno,
CASE
WHEN sal <= 500 then 0
WHEN sal > 500 and sal<1500 then 100
WHEN sal >= 1500 and sal < 2500 and deptno=10 then 200
WHEN sal > 1500 and sal < 2500 and deptno=20 then 500
WHEN sal >= 2500 then 300
ELSE 0
END "bonus"
FROM emp;
ENAME SAL DEPTNO bonus
---------- ---------- ---------- ----------
SMITH 800 20 100
ALLEN 1600 90 0
WARD 1250 30 100
JONES 2975 20 300
MARTIN 1250 30 100
BLAKE 2850 30 300
CLARK 2450 10 200
3.呵呵，在某些情况下，其实也可以使用decode（）函数，它和CASE用法可以互换。
比如：select decode( x , 1 , 'x is 1 ’, 2 , 'x is 2 ’, 'others’) from dual
当x等于1时，则返回 'x is 1’。
当x等于2时，则返回 'x is 2’。
否则，返回'others’。
再比如：想判断a=0时，显示false，否则显示true.
可以这样来写：decode(a,0,false,true)
