oracle常用函數-decode，oracle-decode

oracle常用函數-decode，oracle-decode

       Decode函數在實際開發中非常的有用，而且功能比較強大，與其他函數結合，能讓很多的工作變得簡單；

DECODE(欄位,條件1,結果值1, 條件2,結果值2, …預設值);  

該函數的含義如下：

IF 條件=值1 THEN

RETURN(翻譯值1)

ELSIF 條件=值2 THEN

RETURN(翻譯值2)

......

ELSIF 條件=值n THEN

RETURN(翻譯值n)

ELSE

RETURN(預設值)

END IF

 

比較常見的一些用法：

1、比較大小

select decode(sign(變數1-變數2),-1,變數1,變數2) from dual; --取較小值

sign()函數根據某個值是0、正數還是負數，分別返回0、1、-1

例如：

select decode(sign(10-20),-1,10,20) from dual;

則sign(變數1-變數2)返回-1，decode解碼結果為“變數1”，達到了取較小值的目的。

 

2、產生固定位元的序號

我們看我們的水電煤等各種單子，經常有類似這種 2013000000000004這種固定長度的序列，我們可以結合Lpad函數，使主鍵的值自動加1並在前面補0；

select '2013' || LPAD(decode(count(ID),0,1,max(to_number(ID)+1)),12,'0')記錄編號 from FFM83

FFM83 是一張做測試的表，ID是它的流水ID，有3條資料；

注意：這種寫法雖然簡單，但是如果使用者多，在高並發情況下，可能會產生同樣的序號；

如果在高並發的情況下，可以參考下面這種寫法：

select '2013' || LPAD(SEQ_ FFM83.NEXTVAL,12,'0')記錄編號 from FFM83

SEQ_ FFM83是和FFM83表對應的一個SEQUENCE

 

3、簡化多個條件的查詢

比如我要查詢男、女生的數量分別是多少?

通常我們這麼寫:

select count(*) from 表 where性別＝男；

select count(*) from 表 where性別＝女；

這樣至少要查詢2次，如果有更加多的選擇的話，那麼資料庫的訪問量會更加多；

用decode呢，只需要一句話

select sum(decode(sex,'男',1,0)),sum(decode(sex,'女',1,0)) from ffm83

ffm83 表中有一個sex欄位，直接放了漢字的男女屬性；

 

4、在Order by中使用Decode對指定欄位進行排序

按照電腦，數學專業進行排序

例：

select * from ffm83 order by decode(subject, '電腦', 1, '數學', 2,10);

運行結果為:

女    1     電腦    2013/1/4

男    2    數學       2013/3/13

女    3    數學       2013/3/18

注意：在大資料量的排序中，盡量不要使用這種方式，資料庫開銷非常大；

 

5、將豎錶轉橫表

我們要統計2013年上半年每個月有多少人，最好直接拉出來一張類似報表的樣子。如果用常規的方法，會比較麻煩，用DECODE就會相對簡單

SELECT SUM(DECODE(TO_CHAR(DT, 'MM'), '01', 1,0)) AS "1月" 

     , SUM(DECODE(TO_CHAR(DT, 'MM'),'02', 1,0)) AS "2月" 

     , SUM(DECODE(TO_CHAR(DT, 'MM'),'03', 1,0)) AS "3月" 

     , SUM(DECODE(TO_CHAR(DT, 'MM'),'04', 1,0)) AS "4月" 

     , SUM(DECODE(TO_CHAR(DT, 'MM'),'05', 1,0)) AS "5月" 

     , SUM(DECODE(TO_CHAR(DT, 'MM'),'06', 1,0)) AS "6月" 

  FROM FFM83;

運行結果如下：

1      0     2     0     0     0

 

備忘：

表FF83的結構：

create table FFM83

(

  SEX     VARCHAR2(2),

  ID      INTEGER,

  SUBJECT VARCHAR2(32),

  DT      DATE

);

表中的資料為：

女    1     電腦    2013/1/4

男    2    數學       2013/3/13

女    3    數學       2013/3/18