oracle的case when写法学习

case when 语句可以在SQL语句中实现IF..ELSE
语法上有两种操作：
语法一：
CASE EXPRESSION WHEN EXPRESSION1 THEN VAL1
                WHEN EXPRESSION2 THEN VAL2
                WHEN EXPRESSION3 THEN VAL3
                ..
                WHEN EXPRESSIONN THEN VALN
    END ;
语法二：
CASE WHEN EXPRESSION1 THEN VAL1
                WHEN EXPRESSION2 THEN VAL2
                WHEN EXPRESSION3 THEN VAL3
                ..
                WHEN EXPRESSIONN THEN VALN
    END ;
例：常见的一种面试题：在SCOTT模式下的EMP表中，将DEPT＝10的员工工资变为原来的0.8倍，
DEPT=20的员工工资变为原来的1.2倍,DEPT=300的员工工资变为原来的1.5倍
语法一：
SQL> SELECT ENAME, JOB, HIREDATE, deptno,sal,
2         CASE DEPTNO WHEN 10 THEN SAL * 0.8
3              WHEN 20 THEN sal*1.2
4              WHEN 30 THEN sal*1.5
5         END
6   FROM emp ;

ENAME      JOB       HIREDATE    DEPTNO       SAL CASEDEPTNOWHEN10THENSAL*0.8WHE
---------- --------- ----------- ------ --------- ------------------------------
SMITH      CLERK     1980-12-17      20    800.00                            960
ALLEN      SALESMAN 1981-02-20      30   1600.00                           2400
WARD       SALESMAN 1981-02-22      30   1250.00                           1875
JONES      MANAGER   1981-04-02      20   2975.00                           3570
MARTIN     SALESMAN 1981-09-28      30   1250.00                           1875
BLAKE      MANAGER   1981-05-01      30   2850.00                           4275
CLARK      MANAGER   1981-06-09      10   2450.00                           1960
SCOTT      ANALYST   1987-04-19      20   3000.00                           3600
KING       PRESIDENT 1981-11-17      10   5000.00                           4000
TURNER     SALESMAN 1981-09-08      30   1500.00                           2250
ADAMS      CLERK     1987-05-23      20   1100.00                           1320
JAMES      CLERK     1981-12-03      30    950.00                           1425
FORD       ANALYST   1981-12-03      20   3000.00                           3600
MILLER     CLERK     1982-01-23      10   1300.00                           1040
语法二：
SQL> SELECT ENAME, JOB, HIREDATE, deptno,sal,
2         CASE WHEN DEPTNO = 10 THEN SAL * 0.8
3              WHEN deptno =20 THEN sal*1.2
4              WHEN deptno =30 THEN sal*1.5
5         END
6   FROM emp ;

ENAME      JOB       HIREDATE    DEPTNO       SAL CASEWHENDEPTNO=10THENSAL*0.8WH
---------- --------- ----------- ------ --------- ------------------------------
SMITH      CLERK     1980-12-17      20    800.00                            960
ALLEN      SALESMAN 1981-02-20      30   1600.00                           2400
WARD       SALESMAN 1981-02-22      30   1250.00                           1875
JONES      MANAGER   1981-04-02      20   2975.00                           3570
MARTIN     SALESMAN 1981-09-28      30   1250.00                           1875
BLAKE      MANAGER   1981-05-01      30   2850.00                           4275
CLARK      MANAGER   1981-06-09      10   2450.00                           1960
SCOTT      ANALYST   1987-04-19      20   3000.00                           3600
KING       PRESIDENT 1981-11-17      10   5000.00                           4000
TURNER     SALESMAN 1981-09-08      30   1500.00                           2250
ADAMS      CLERK     1987-05-23      20   1100.00                           1320
JAMES      CLERK     1981-12-03      30    950.00                           1425
FORD       ANALYST   1981-12-03      20   3000.00                           3600
MILLER     CLERK     1982-01-23      10   1300.00                           1040