oracle中的條件語句

demon09發表於2018-05-07
Oracle
oracle中case when then及decode用法
一.case … when … then 語法： 
– 寫法一： 
case（條件） 
when 值1 then 返回值1 
when 值2 then 返回值2 
else 預設值 

– 寫法二： 
case when 條件1 then 返回值1 
when 條件2 then 返回值2 
else 預設值 
end;

案例1:
-- 如果部門編號為10的，顯示為dept10
-- 如果部門編號為20的，顯示為dept20
-- 如果部門編號為30的，顯示為dept30
-- 否則顯示為other
-- 這一列查詢的結果，列名顯示為 department

使用
寫法一:
select ename,
       sal,
       case deptno
         when 10 then
          `dept10`
         when 20 then
          `dept20`
         when 30 then
          `dept30`
         else
          `other`
       end department
  from emp

寫法二:
select ename,
       sal,
       case
         when deptno = 10 then
          `dept10`
         when deptno = 20 then
          `dept20`
         when deptno = 30 then
          `dept30`
         else
          `other`
       end department
  from emp

在這個例子中條件都是等值，結果是一樣的。
如果是不等值的或是有多個表示式，就只能用第二種了，比如:
select ename,sal,
case when deptno= 10 or deptno = 20 or deptno = 30 then `dept`||deptno end dept
from emp;

select ename,sal,
case when deptno<=20 then `dept`||deptno end dept
from emp;

select ename,
       sal,
       case
         when sal > 0 and sal <= 1500 then
          `level1`
         when sal > 1500 and sal <= 2500 then
          `level2`
         when sal > 2500 and sal <= 4500 then
          `level3`
         else
          `level4`
       end sal_level
  from emp
 order by sal desc;

二.decode函式： 
decode（條件，值1，返回值1，值2，返回值2，…….，預設值）
使用decode函式來實現案例1:
select ename,
       sal,
       decode(deptno, 10, `dept10`, 20, `dept20`, 30, `dept30`, `other`) department
  from emp

查出來的結果和上面用case when一樣，但這句看起來簡潔得多了

decode()函式的延伸用法：
1.與sign函式聯用比較大小：
--get arg1與arg2的較小值 
語法：select decode(sign(arg1-arg2),-1,arg1,arg2) from dual;  
例項：select decode(sign(3-5),1,3,5) from dual --5 
注：sign()函式根據某個值是0、正數還是負數，分別返回0、1、-1

一個Update語句：把表中某個欄位的值進行更改，這條語句是把值為“1”的都改成“8”，“0”改成“9”
update tablename set 欄位名= decode(欄位名,1,8,0,9)  where 欄位名 in (1, 0);
三、DECODE 與CASE WHEN 的比較
      1.DECODE 只有Oracle 才有，其它資料庫不支援;
      2.CASE WHEN的用法， Oracle、SQL Server、 MySQL 都支援;
      3.DECODE 只能用做相等判斷,但是可以配合sign函式進行大於，小於，等於的判斷,CASE when可用於=,>=,<,<=,<>,is null,is not null 等的判斷;
      4.DECODE 使用其來比較簡潔，CASE 雖然複雜但更為靈活;
      5.另外，在decode中，null和null是相等的，但在case when中，只能用is null來判斷
示例如下：
  emp表中有一列comm，如果這列為null,則顯示為0，否則，顯示為原值：
--decode可以顯示我們要求的結果
SQL> select ename,decode(comm,null,0,comm) comma from emp;
-- null沒有轉成0，仍然是null,不是我們要求的結果
SQL> select ename,(case comm when null then 0 else comm end) comm from emp;
--這樣才可以成功將null顯示為0
SQL> select ename,(case when comm is null then 0 else comm end) comm from emp;

 

相關文章