Oracle函數之case和decode的用法區別及效能比較

在Oracle世界，你可以使用：

1）case運算式      或者

2）decode函數

來實現邏輯判斷。Oracle的DECODE函數功能很強，靈活運用的話可以避免多次掃描，從而提高查詢的效能。而CASE是9i以後提供的文法，這個文法更加的靈活，提供了IF THEN ELSE的功能。

case運算式

case運算式，可分兩種，簡單和搜尋，簡單case後接運算式，如：

對於簡單的case需要幾點注意：

1）尋找when的優先順序：從上到下

2）再多的when，也只有一個出口，即其中有一個滿足了expr就馬上退出case

3）不能把return_expr和else_expr指定為null，而且，expr、comparison_expr和return_expr的資料類型必須相同。

搜尋case：

CASE WHEN condition THEN return_expr

[WHEN condition THEN return_expr]

...

ELSE else_expr

END

例子：

1. SELECT (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN  ' 0 - 3999'  
2.    WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999'  
3.    WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999'  
4.    WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END)  
5.   AS BUCKET, COUNT(*) AS Count_in_Group  
6. FROM customers WHERE cust_city = 'Marshal' GROUP BY  
7.  (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN ' 0 - 3999'  
8.  WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999'  
9.  WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999'  
10.  WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END);  
11.   
12. BUCKET        COUNT_IN_GROUP  
13. ------------- --------------   
14.  0 - 3999                  8  
15.  4000 - 7999               7  
16.  8000 - 11999              7  
17. 12000 - 16000              1  

用decode可以違反第3NF（行不可再分，列不可再分，列不可重複）：列重複

1. hr@ORCL> select * from a;  
2.   
3.         ID NAME  
4. ---------- ----------   
5.          1 a  
6.          2 b  
7.          3 c  
8.          1 a  
9.   
10. hr@ORCL> select sum(decode(id,1,1,0)) think,  
11.   2             sum(decode(id,2,2,0)) water,  
12.   3             sum(decode(id,3,3,0)) linshuibin  
13.   4        from a;  
14.   
15.      THINK      WATER LINSHUIBIN  
16. ---------- ---------- ----------   
17.          2          2          3  

1 2 3 下一頁