亚洲一级片内射网站在线观看,亚洲а∨精品天堂在线,亚洲?v女人的天堂在线观看

在oracle世界，你可以使用：

1）case表達(dá)式或者

2）decode函數(shù)

來(lái)實(shí)現(xiàn)邏輯判斷。Oracle的DECODE函數(shù)功能很強(qiáng)，靈活運(yùn)用的話可以避免多次掃描，從而提高查詢的性能。而CASE是9i以后提供的語(yǔ)法，這個(gè)語(yǔ)法更加的靈活，提供了IF THEN ELSE的功能。

case表達(dá)式

case表達(dá)式，可分兩種，簡(jiǎn)單和搜索，簡(jiǎn)單case后接表達(dá)式，如：

對(duì)于簡(jiǎn)單的case需要幾點(diǎn)注意：

1）尋找when的優(yōu)先級(jí)：從上到下

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

3）不能把return_expr和else_expr指定為null，而且，expr、comparison_expr和return_expr的數(shù)據(jù)類型必須相同。

搜索case：

CASE WHEN condition THEN return_expr

[WHEN condition THEN return_expr]

...

ELSE else_expr

END

例子：

[sql] view plain copy

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（行不可再分，列不可再分，列不可重復(fù)）：列重復(fù)

[sql] view plain copy

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

一個(gè)字段，decode函數(shù)可以完全改寫簡(jiǎn)單case；

多個(gè)字段，需要復(fù)雜的case，方可。

語(yǔ)法：
DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1時(shí)，DECODE函數(shù)的結(jié)果返then1,...,如果不等于任何一個(gè)if值，則返回else。可以用函數(shù)或表達(dá)式來(lái)替代value，if，then，else從而作出一些更有用的比較。

    來(lái)看看具體的運(yùn)用：
    1 假設(shè)我們想給百度職員加工資，其標(biāo)準(zhǔn)是：工資在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列。要求按照：語(yǔ)、數(shù)、外的順序進(jìn)行排序
則：

select * from table_subject order by decode(subject_name, '語(yǔ)文', 1, '數(shù)學(xué)', 2, , '外語(yǔ)',3)

decode和簡(jiǎn)單case的性能比較

Oracle的DECODE函數(shù)功能很強(qiáng)，靈活運(yùn)用的話可以避免多次掃描，從而提高查詢的性能。而CASE是9i以后提供的語(yǔ)法，這個(gè)語(yǔ)法更加的靈活，提供了IF THEN ELSE的功能。

對(duì)于很多情況，DECODE和CASE都能解決問題，個(gè)人更傾向于使用DECODE，一方面是從8i保留下來(lái)的習(xí)慣，另一方面是DECODE的語(yǔ)法更加的簡(jiǎn)潔，代碼量要小一些。

不過(guò)今天在看Oracle9i的數(shù)據(jù)倉(cāng)庫(kù)手冊(cè)時(shí)發(fā)現(xiàn)，Oracle在文檔中提到CASE語(yǔ)句的效率會(huì)更高一些，尤其是CASE表達(dá)式 WHEN 常量 THEN的語(yǔ)法，效率要比CASE WHEN表達(dá)式 THEN的語(yǔ)法更高一些。對(duì)于后面這種說(shuō)法倒是沒有太多的疑問，對(duì)于CASE比DECODE效率高這種說(shuō)法倒是第一次看到，印象中DECODE效率很高，應(yīng)該不會(huì)比CASE的效率差。

到底效率如何，還是要具體的實(shí)例來(lái)說(shuō)：

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語(yǔ)句的效率：

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

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

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

發(fā)表于 2012-08-01 22:09 手心上的冰山閱讀(22427) 評(píng)論(1) 編輯收藏

oracle函數(shù)之case和decode的用法區(qū)別及性能比較

導(dǎo)航

統(tǒng)計(jì)

常用鏈接

留言簿

隨筆檔案

搜索

最新評(píng)論

閱讀排行榜

評(píng)論排行榜