oracle sql语句中使用if逻辑
l在 SQL 语句中使用IF-THEN-ELSE 逻辑
l
l使用两种方法:
•CASE 表达式：SQL99的语法，类似Basic，比较繁琐
•DECODE 函数：Oracle自己的语法，类似Java，比较简介
1 SQL> select ename,job, sal, case job when 'PRESIDENT' then 1.1*sal 2 2 when 'MANAGER' then 1.2*sal 3 3 when 'CLERK' then 1.3*sal 4 4 else 1.4*sal 5 5 end 涨后薪水 6 6 from emp 7 7 ; 8 9 ENAME JOB SAL 涨后薪水 10 ---------- --------- --------- ---------- 11 SMITH CLERK 800.00 1040 12 ALLEN SALESMAN 1600.00 2240 13 WARD SALESMAN 1250.00 1750 14 JONES MANAGER 2975.00 3570 15 MARTIN SALESMAN 1250.00 1750 16 BLAKE MANAGER 2850.00 3420 17 CLARK MANAGER 2450.00 2940 18 KING PRESIDENT 5000.00 5500 19 TURNER SALESMAN 1500.00 2100 20 JAMES CLERK 950.00 1235 21 FORD ANALYST 3000.00 4200 22 MILLER CLERK 1400.00 1820 23 jack_1234 2000.00 2800 24 25 13 rows selected 26 27 SQL> select ename,job, sal,decode(job,'PRESIDENT',1.1*sal, 28 2 'MANAGER',1.2*sal, 29 3 'CLERK',1.3*sal, 30 4 1.4*sal)涨后薪水 31 5 from emp; 32 33 ENAME JOB SAL 涨后薪水 34 ---------- --------- --------- ---------- 35 SMITH CLERK 800.00 1040 36 ALLEN SALESMAN 1600.00 2240 37 WARD SALESMAN 1250.00 1750 38 JONES MANAGER 2975.00 3570 39 MARTIN SALESMAN 1250.00 1750 40 BLAKE MANAGER 2850.00 3420 41 CLARK MANAGER 2450.00 2940 42 KING PRESIDENT 5000.00 5500 43 TURNER SALESMAN 1500.00 2100 44 JAMES CLERK 950.00 1235 45 FORD ANALYST 3000.00 4200 46 MILLER CLERK 1400.00 1820 47 jack_1234 2000.00 2800 48 49 13 rows selected
