Oracle 中 流程式控制制函數 decode用法

標籤：方法   圖結構   reference   介紹   會話   number   sign   strong   邏輯   

函數介紹　　

        DECODE函數是ORACLE PL/SQL的功能強大的函數之一，目前還只有ORACLE公司的SQL提供了此函數，其它資料庫廠商的SQL實現還沒有此功能。

        DECODE有什麼用途呢？先構造一個例子，假設我們想給智星職員加工資，其標準是：工資在8000元以下的加20％；工資在8000元或以上的加15％。

        通常的做法是，先選出記錄中的工資欄位值? select salary into var-salary from employee，然後對變數var-salary用if-then-else或choose case之類的流量控制語句進行判斷。

        如果用DECODE函數，那麼我們就可以把這些流量控制語句省略，通過SQL語句就可以直接完成。

        如下：select decode(sign(salary - 8000),>=0,salary*1.15,<0,salary*1.2,salary) from employee 是不是很簡潔？ DECODE的文法：

                  DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，

                 表示如果value等於if1時，DECODE函數的結果返回then1,...,如果不等於任何一個if值，則返回else。初看一下，DECODE 只能做等於測試，但剛才也看到了，我們通過一些函數或計算替代value，是可以使DECODE函數具備大於、小於或等於功能。

關於DECODE

　　DECODE是Oracle公司獨家提供的功能，它是一個功能很強的函數。它雖然不是SQL的標準，但對於效能非常有用。到目前，其他的資料庫供應商還不能提供類似DECODE的功能，甚至有的資料庫的供應商批評Oracle的SQL不標準。實際上，這種批評有些片面或不夠水平。就象有些馬車製造商抱怨亨利。福特的“馬車”不標準一樣。

1、 DECODE 中的if-then-else邏輯

　　在邏輯編程中，經常用到If – Then –Else 進行邏輯判斷。在DECODE的文法中，實際上就是這樣的邏輯處理過程。它的文法如下：

　　DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )

　　其中，Value 代表某個表的任何類型的任意列或一個通過計算所得的任何結果。需要注意的是，這裡的if、then及else 都可以是函數或計算運算式。

        當每個value值被測試，如果value的值為if1，Decode 函數的結果是then1；如果value等於if2，Decode函數結果是then2；等等。事實上，可以給出多個if/then 配對。如果value結果不等於給出的任何配對時，Decode 結果就返回else 。

2、 DECODE 的簡單例子

　　Oracle系統中就有許多資料字典是使用decode 思想設計的，比如記錄會話資訊的V$SESSION資料字典視圖就是這樣。

       我們從《Oracle8i/9i Reference》資料中瞭解到，當使用者登入成功後在V$SESSION中就有該使用者的相應記錄，但使用者所進行的命令操作在該視圖中只記錄命令的代碼（0—沒有任何操作，2—Insert…），而不是具體的命令關鍵字。

       因此，我們需要瞭解當前各個使用者的名字及他們所進行的操作時，要用下面命令才能得到詳細的結果：

　　select sid,serial#,username,

　　DECODE(command,

       0,’None’,

　　2,’Insert’,

　　3,’Select’,

　　6,’Update’,

　　7,’Delete’,

　　8,’Drop’,

　　‘Other’) cmmand

　　from v$session where username is not null;

3、 DECODE 的具體使用方法

      （1） 比較大小

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

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

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

      （2）表、視圖結構轉化

               a. 縱錶轉橫表

縱表結構: TEST_Z2H   FNAME       FTYPE             FVALUE   員工        zaocan              10  員工       zhongcan             20  員工        wancan              5  

轉換後的表結構:

  FNAME     ZAOCAN_VALUE          ZHONGCAN_VALUE       WANCAN_VALUE   

   員工           10                     20                    5  

　　 

1 --縱錶轉橫表SQL樣本:   2   SELECT FNAME,   3        SUM(DECODE(FTYPE,‘zaocan‘,FVALUE,0)) AS ZAOCAN_VALUE,   4        SUM(DECODE(FTYPE,‘zhongcan‘,FVALUE,0)) AS ZHONGCAN_VALUE,   5        SUM(DECODE(FTYPE,‘wancan‘,FVALUE,0)) AS WANCAN_VALUE   6   FROM TEST_Z2H   7   GROUP BY FNAME; 

        b. 橫表轉縱表

橫表結構: TEST_H2Z         ID      姓名     語文       數學        英語               1       張三     80         90         70                     2       李四     90         85         95                   3       王五     88         75         90                 轉換後的表結構:           ID      姓名     科目     成績           1       張三     語文     80           2       張三     數學     90           3       張三     英語     70           4       李四     語文     90           5       李四     數學     80             6       李四     英語     99           7       王五     語文     85           8       王五     數學     96           9       王五     英語     88   

1 --橫錶轉縱表SQL樣本:   2 SELECT   姓名,‘語文‘   AS     科目,語文   AS   成績   FROM   TEST_H2Z   UNION   ALL    3 SELECT   姓名,‘數學‘   AS     科目,數學   AS   成績   FROM   TEST_H2Z   UNION   ALL    4 SELECT   姓名,‘英語‘   AS     科目,英語   AS   成績   FROM   TEST_H2Z   5 ORDER BY 姓名,科目 DESC;  

 

Oracle 中 流程式控制制函數 decode用法