条件表达式

 Oracle中条件表达式有两种写法

 

case表达式（sql99语法）

case  列名或表达式  when  条件1  then  操作1

　　　　　　　　条件2   then 操作2

　　　　　　　　……

　　　　　　end

 

 select ename,job,sal "涨前",
    case job when 'PRESIDENT' then sal+1000
             when 'MANAGER' then sal+800
             else  sal+500
    end "涨后"
 from emp;

运行结果：

ENAME      JOB             涨前       涨后
---------- --------- ---------- ----------
TOM.JK     PRESIDENT   10000.55   11000.55
SMITH      CLERK            800       1300
ALLEN      SALESMAN        1600       2100
WARD       SALESMAN        1250       1750
JONES      MANAGER         2975       3775
MARTIN     SALESMAN        1250       1750
BLAKE      MANAGER         2850       3650
CLARK      MANAGER         2450       3250
SCOTT      ANALYST         3000       3500
KING       PRESIDENT       5000       6000
TURNER     SALESMAN        1500       2000
ADAMS      CLERK           1100       1600
JAMES      CLERK            950       1450
FORD       ANALYST         3000       3500
MILLER     CLERK           1300       1800

 

decode表达式（oracle自己的语法）

decode(列，条件1，操作1，条件2，操作2,……条件n,操作n)

decode(列，条件1，操作1，条件2，操作2,……,条件n-1,操作n-1,操作n)

select ename,job,sal "涨前",
   decode(job,'PRESIDENT',sal+1000,
           'MANAGER',sal+800,
           sal+500) "涨后"
from emp

运行结果：

ENAME      JOB             涨前       涨后
---------- --------- ---------- ----------
TOM.JK     PRESIDENT   10000.55   11000.55
SMITH      CLERK            800       1300
ALLEN      SALESMAN        1600       2100
WARD       SALESMAN        1250       1750
JONES      MANAGER         2975       3775
MARTIN     SALESMAN        1250       1750
BLAKE      MANAGER         2850       3650
CLARK      MANAGER         2450       3250
SCOTT      ANALYST         3000       3500
KING       PRESIDENT       5000       6000
TURNER     SALESMAN        1500       2000
ADAMS      CLERK           1100       1600
JAMES      CLERK            950       1450
FORD       ANALYST         3000       3500
MILLER     CLERK           1300       1800