case...end与decode 这两种语句都是实现(if--then--else) 的分支效果。decode只是将case简化了而已。case较繁琐,个人感觉还是decode 好用啊!下面我就两者的区别进行测试。
case:
第一种写法:
select empno,ename,job,sal old_sal,
2 case job
3 when 'ClERK' then sal+1
4 when 'SALESMAN' then sal+2
5 when 'MANAGER' then sal+3
6 else sal
7 end new_sal
8 from emp
9* order by job;
EMPNO ENAME JOB OLD_SAL NEW_SAL
---------- ---------- --------- ---------- ----------
7788 SCOTT ANALYST 3000 3000
7902 FORD ANALYST 3000 3000
7934 MILLER CLERK 1300 1300
7900 JAMES CLERK 950 950
7369 SMITH CLERK 800 800
7876 ADAMS CLERK 1100 1100
7698 BLAKE MANAGER 2850 2853
7566 JONES MANAGER 2975 2978
7782 CLARK MANAGER 2450 2453
7839 KING PRESIDENT 5000 5000
7844 TURNER SALESMAN 1500 1502
7654 MARTIN SALESMAN 1250 1252
7521 WARD SALESMAN 1250 1252
7499 ALLEN SALESMAN 1600 1602
14 rows selected.
第二种写法:
SQL> select empno,ename,job,sal old_sal,
2 case when job='CLERK' then sal+1
3 when job='SALESMAN' then sal+2
4 when job='MANAGER' then sal+3
5 else sal
6 end new_sal
7 from emp
8 order by job;
EMPNO ENAME JOB OLD_SAL NEW_SAL
---------- ---------- --------- ---------- ----------
7788 SCOTT ANALYST 3000 3000
7902 FORD ANALYST 3000 3000
7934 MILLER CLERK 1300 1301
7900 JAMES CLERK 950 951
7369 SMITH CLERK 800 801
7876 ADAMS CLERK 1100 1101
7698 BLAKE MANAGER 2850 2853
7566 JONES MANAGER 2975 2978
7782 CLARK MANAGER 2450 2453
7839 KING PRESIDENT 5000 5000
7844 TURNER SALESMAN 1500 1502
7654 MARTIN SALESMAN 1250 1252
7521 WARD SALESMAN 1250 1252
7499 ALLEN SALESMAN 1600 1602
14 rows selected.
第二种写法的好处是可以在when语句中添加复合条件。
decode:
SQL> select empno,ename,job,sal old_sal,
2 decode(job,'CLERK',sal+1,
3 'SALESMAN',sal+2,
4 'MANAGER',sal+3,
5 sal) new_sal
6 from emp
7 order by job;
EMPNO ENAME JOB OLD_SAL NEW_SAL
---------- ---------- --------- ---------- ----------
7788 SCOTT ANALYST 3000 3000
7902 FORD ANALYST 3000 3000
7934 MILLER CLERK 1300 1301
7900 JAMES CLERK 950 951
7369 SMITH CLERK 800 801
7876 ADAMS CLERK 1100 1101
7698 BLAKE MANAGER 2850 2853
7566 JONES MANAGER 2975 2978
7782 CLARK MANAGER 2450 2453
7839 KING PRESIDENT 5000 5000
7844 TURNER SALESMAN 1500 1502
7654 MARTIN SALESMAN 1250 1252
7521 WARD SALESMAN 1250 1252
7499 ALLEN SALESMAN 1600 1602
14 rows selected.
如果不写else语句(decode中的(...sal))系统会返回空值。
SQL> select empno,ename,job,sal old_sal,
2 decode(job,'CLERK',sal+1,
3 'SALESMAN',sal+2,
4 'MANAGER',sal+3) new_sal
5 from emp
6 order by job;
EMPNO ENAME JOB OLD_SAL NEW_SAL
---------- ---------- --------- ---------- ----------
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300 1301
7900 JAMES CLERK 950 951
7369 SMITH CLERK 800 801
7876 ADAMS CLERK 1100 1101
7698 BLAKE MANAGER 2850 2853
7566 JONES MANAGER 2975 2978
7782 CLARK MANAGER 2450 2453
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500 1502
7654 MARTIN SALESMAN 1250 1252
7521 WARD SALESMAN 1250 1252
7499 ALLEN SALESMAN 1600 1602
14 rows selected.