在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
例子：
- SELECT (CASE WHEN cust_credit_limit BETWEEN 0 AND 3999 THEN ' 0 - 3999'
- WHEN cust_credit_limit BETWEEN 4000 AND 7999 THEN ' 4000 - 7999'
- WHEN cust_credit_limit BETWEEN 8000 AND 11999 THEN ' 8000 - 11999'
- WHEN cust_credit_limit BETWEEN 12000 AND 16000 THEN '12000 - 16000' END)
- AS BUCKET, COUNT(*) AS Count_in_Group
- FROM customers WHERE cust_city = 'Marshal' GROUP BY
- (CASE WHEN cust_credit_limit BETWEEN 0 AND 3999 THEN ' 0 - 3999'
- WHEN cust_credit_limit BETWEEN 4000 AND 7999 THEN ' 4000 - 7999'
- WHEN cust_credit_limit BETWEEN 8000 AND 11999 THEN ' 8000 - 11999'
- WHEN cust_credit_limit BETWEEN 12000 AND 16000 THEN '12000 - 16000' END);
-
- BUCKET COUNT_IN_GROUP
- ------------- --------------
- 0 - 3999 8
- 4000 - 7999 7
- 8000 - 11999 7
- 12000 - 16000 1
用decode可以違反第3NF（行不可再分，列不可再分，列不可重復）：列重復
- hr@ORCL> select * from a;
-
- ID NAME
- ---------- ----------
- 1 a
- 2 b
- 3 c
- 1 a
-
- hr@ORCL> select sum(decode(id,1,1,0)) think,
- 2 sum(decode(id,2,2,0)) water,
- 3 sum(decode(id,3,3,0)) linshuibin
- 4 from a;
-
- THINK WATER LINSHUIBIN
- ---------- ---------- ----------
- 2 2 3
一個字段，decode函數可以完全改寫簡單case；
多個字段，需要復雜的case，方可。
語法：
DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1時，DECODE函數的結果返then1,...,如果不等于任何一個if值，則返回else?？梢杂煤瘮祷虮磉_式來替代value，if，then，else從而作出一些更有用的比較。
來看看具體的運用：
1 假設我們想給百度職員加工資，其標準是：工資在8000元以下的將加20％；工資在8000元以上的加15％
則：
select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary) "revised_salary" from employee
2 表table_subject，有subject_name列。要求按照：語、數、外的順序進行排序
則：
select * from table_subject order by decode(subject_name, '語文', 1, '數學', 2, , '外語',3)
decode和簡單case的性能比較
Oracle的DECODE函數功能很強，靈活運用的話可以避免多次掃描，從而提高查詢的性能。而CASE是9i以后提供的語法，這個語法更加的靈活，提供了IF THEN ELSE的功能。
對于很多情況，DECODE和CASE都能解決問題，個人更傾向于使用DECODE，一方面是從8i保留下來的習慣，另一方面是DECODE的語法更加的簡潔，代碼量要小一些。
不過今天在看Oracle9i的數據倉庫手冊時發現，Oracle在文檔中提到CASE語句的效率會更高一些，尤其是CASE表達式 WHEN 常量 THEN的語法，效率要比CASE WHEN表達式 THEN的語法更高一些。對于后面這種說法倒是沒有太多的疑問，對于CASE比DECODE效率高這種說法倒是第一次看到，印象中DECODE效率很高，應該不會比CASE的效率差。
到底效率如何，還是要具體的實例來說：
SQL> CREATE TABLE T AS
2 SELECT A.*
3 FROM DBA_OBJECTS A, DBA_MVIEWS;
Table created.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
6075760
下面檢查DECODE和兩種CASE語句的效率：
SQL> SET ARRAY 1000
SQL> SET TIMING ON
SQL> SET AUTOT TRACE
SQL> SELECT DECODE(OWNER, 'SYSTEM', 'SYSTEM', 'SYS', 'SYSTEM', 'USER')
2 FROM T;
6075760 rows selected.
Elapsed: 00:00:07.24
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288564 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed
SQL> SELECT CASE OWNER WHEN 'SYSTEM' THEN 'SYSTEM'
2 WHEN 'SYS' THEN 'SYSTEM'
3 ELSE 'USER' END
4 FROM T;
6075760 rows selected.
Elapsed: 00:00:07.22
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288578 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed
SQL> SELECT CASE WHEN OWNER = 'SYSTEM' THEN 'SYSTEM'
2 WHEN OWNER = 'SYS' THEN 'SYSTEM'
3 ELSE 'USER' END
4 FROM T;
6075760 rows selected.
Elapsed: 00:00:07.23
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288585 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed