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