l在 SQL 语句中使用IF-THEN-ELSE 逻辑

l

l使用两种方法:

•CASE 表达式：SQL99的语法，类似Basic，比较繁琐

•DECODE 函数：Oracle自己的语法，类似Java，比较简介

 SQL> select ename,job, sal, case job when 'PRESIDENT' then 1.1*sal

   2                               when 'MANAGER' then 1.2*sal

   3                                when 'CLERK' then 1.3*sal

   4                                 else 1.4*sal

   5                                end 涨后薪水

   6  from emp

   7  ;

 ENAME      JOB             SAL       涨后薪水

 ---------- --------- --------- ----------

 SMITH      CLERK        800.00       1040

 ALLEN      SALESMAN    1600.00       2240

 WARD       SALESMAN    1250.00       1750

 JONES      MANAGER     2975.00       3570

 MARTIN     SALESMAN    1250.00       1750

 BLAKE      MANAGER     2850.00       3420

 CLARK      MANAGER     2450.00       2940

 KING       PRESIDENT   5000.00       5500

 TURNER     SALESMAN    1500.00       2100

 JAMES      CLERK        950.00       1235

 FORD       ANALYST     3000.00       4200

 MILLER     CLERK       1400.00       1820

 jack_1234              2000.00       2800

 13 rows selected

 SQL> select ename,job, sal,decode(job,'PRESIDENT',1.1*sal,

   2                                   'MANAGER',1.2*sal,

   3                                   'CLERK',1.3*sal,

   4                                           1.4*sal)涨后薪水

   5  from emp;

 ENAME      JOB             SAL       涨后薪水

 ---------- --------- --------- ----------

 SMITH      CLERK        800.00       1040

 ALLEN      SALESMAN    1600.00       2240

 WARD       SALESMAN    1250.00       1750

 JONES      MANAGER     2975.00       3570

 MARTIN     SALESMAN    1250.00       1750

 BLAKE      MANAGER     2850.00       3420

 CLARK      MANAGER     2450.00       2940

 KING       PRESIDENT   5000.00       5500

 TURNER     SALESMAN    1500.00       2100

 JAMES      CLERK        950.00       1235

 FORD       ANALYST     3000.00       4200

 MILLER     CLERK       1400.00       1820

 jack_1234              2000.00       2800

 13 rows selected