Oracle Handbook系列之結構化查詢

作者：側身向南邊 2011-08-24 13:34:34

這是這個系列的開篇之作，主要從結構化查詢角度出發講述Oracle的使用，本系列的其它文章會陸續錄入，敬請期待...

一)準備測試數據

閑話少說，直入正題。建立一張簡單的職工表 t_hierarchical:

Emp 職工編號
Mgr 職工的直接上司(Mgr本身也是職工)
Emp_name 職工姓名

插入一些測試數據，除了大老板AA，其它的職工都各有自己的Manager。

select emp, mgr, emp_name from t_hierarchical t;

1            AA 
2     1     BB 
3     2     CC 
4     3     DD 
5     2     EE 
6     3     FF

二)CONNECT BY

select emp, mgr, LEVEL from t_hierarchical t 
CONNECT BY PRIOR emp=mgr 
order by emp; 
 
1           1 
2     1     2 
2     1     1 
3     2     1 
3     2     3 
3     2     2 
4     3     4 
4     3     1 
4     3     2 
4     3     3 
5     2     3 
5     2     2 
5     2     1 
6     3     2 
6     3     3 
6     3     4 
6     3     1

解釋一下，CONNECT BY用于指定父-子記錄的關系(PRIOR我們在下例中解釋，更直觀一些)。舉emp 2為例，他隸屬于emp 1，如果我們以emp 1為根節點，顯然LEVEL=2;以emp 2自身為根節點，則LEVEL=1，這就是為什么上述查詢結果中出現共色標識部分那兩行記錄，其它的類推。

三)START WITH

通常我們需要更直觀、更具有實用性的結果，這需要用到結構化查詢中的START WITH子句，用于指定根節點：

select emp, mgr, LEVEL from t_hierarchical t 
START WITH emp=1 
CONNECT BY PRIOR emp=mgr; 
 
1           1 
2     1     2 
3     2     3 
4     3     4 
6     3     4 
5     2     3

這里我們指定了根節點是emp 1，這樣的結果直觀了許多，例如，以emp 1為根節點，那么emp 3位于第三級(emp 1—emp 2—emp 3)，這里補充一下 PRIOR 關鍵字的說明，個人觀點：“PRIOR emp=mgr”表示前一條記錄的emp編號 = 當前記錄的mgr編號，從查詢結果中可以看出這一點。同時，從查詢結果中還能發現明顯的遞歸痕跡，參見不同顏色標識的數字。

四)SYS_CONNECT_BY_PATH()

不得不介紹一下非常牛波依的SYS_CONNECT_BY_PATH()函數，我們可以得到層次結構或者說樹狀結構的路徑，參見如下：

select emp, mgr, LEVEL, SYS_CONNECT_BY_PATH(emp,'/') path from t_hierarchical t 
START WITH emp=1 
CONNECT BY PRIOR emp=mgr; 
 
1            1     /1 
2     1     2     /1/2 
3     2     3     /1/2/3 
4     3     4     /1/2/3/4 
6     3     4     /1/2/3/6 
5     2     3     /1/2/5

五)CONNECT_BY_ISLEAF

非常好用的CONNECT_BY_ISLEAF虛列。何謂LEAF(葉子)，即沒有任何節點隸屬于該節點：

select emp, mgr, LEVEL, SYS_CONNECT_BY_PATH(emp,'/') path from t_hierarchical t 
where CONNECT_BY_ISLEAF=1 
START WITH emp=1 
CONNECT BY PRIOR emp=mgr; 
 
4     3     4     /1/2/3/4 
6     3     4     /1/2/3/6 
5     2     3     /1/2/5

#p#

六)CONNECT BY與WHERE子句

下面再說說，關于引入結構化查詢后，SQL語句的執行順序問題，根據Oracle文檔，先后是：

1)JOIN，無論用的是JOIN ON的寫法，還是在WHERE中做的關聯

2)CONNECT BY

3)其它的WHERE條件

看一個例子，假設上面的各位職工，需要保存一些注釋信息，同時這些信息根據中文、英文分成兩個不同版本，我們可以簡單設計一下這個注釋表：

|-Emp 職工編號 
|-Lang 語言（中文或英文） 
|-Emp_desc 職工的具體描述 
 
select emp, lang, emp_desc from t_desc; 
 
1     chinese 這是注釋 
1     english   this is comment 
2     chinese 這是注釋 
2     english   this is comment 
3     chinese 這是注釋 
3     english   this is comment 
4     chinese 這是注釋 
4     english   this is comment 
5     chinese 這是注釋 
5     english   this is comment 
6     chinese 這是注釋 
6     english   this is comment

現在需要在原有的職工結構化查詢中包括每個職工的中文注釋信息，我們看看下面的查詢：

select t.emp, t.mgr, td.emp_desc, LEVEL 
from t_hierarchical t, t_desc td 
where t.emp=td.emp and td.lang='chinese' 
START WITH t.emp=1 
CONNECT BY PRIOR t.emp=t.mgr; 
 
1            chinese 這是注釋 1 
2     1     chinese 這是注釋 2 
3     2     chinese 這是注釋 3 
4     3     chinese 這是注釋 4 
6     3     chinese 這是注釋 4 
4     3     chinese 這是注釋 4 
6     3     chinese 這是注釋 4 
5     2     chinese 這是注釋 3 
3     2     chinese 這是注釋 3 
4     3     chinese 這是注釋 4 
6     3     chinese 這是注釋 4 
4     3     chinese 這是注釋 4 
6     3     chinese 這是注釋 4 
5     2     chinese 這是注釋 3 
2     1     chinese 這是注釋 2 
3     2     chinese 這是注釋 3 
4     3     chinese 這是注釋 4 
6     3     chinese 這是注釋 4 
4     3     chinese 這是注釋 4 
6     3     chinese 這是注釋 4 
5     2     chinese 這是注釋 3 
3     2     chinese 這是注釋 3 
4     3     chinese 這是注釋 4 
6     3     chinese 這是注釋 4 
4     3     chinese 這是注釋 4 
6     3     chinese 這是注釋 4 
5     2     chinese 這是注釋 3

再看這個查詢，看起來與前者是一樣的：

select t.emp, t.mgr, td.emp_desc, LEVEL 
from t_hierarchical t join t_desc td 
on (t.emp=td.emp and td.lang='chinese') 
START WITH t.emp=1 
CONNECT BY PRIOR t.emp=t.mgr; 
 
1            這是注釋 1 
2     1     這是注釋 2 
3     2     這是注釋 3 
4     3     這是注釋 4 
6     3     這是注釋 4 
5     2     這是注釋 3

第二個是我們期望的結果，第二個則相去甚遠。追究原因，是因為前一個例子中第二個條件 td.lang=’chinese’不被認為是JOIN條件，所以在CONNECT BY之后執行；后一個例子中由于顯式地把第二個條件寫在了JOIN ON子句中，所以它在CONNECT BY之前執行。

由于缺少第二個條件的JOIN（即本節***例）會導致每個的職工出現兩次，換一個數據少一點的例子，看看CONNECT BY遇到這樣的重復數據的時候是怎么處理的。

select emp, mgr, lang from t2; 
 
1            chinese 
1            english 
2     1     chinese 
2     1     english

CONNECT BY之后：

select emp, mgr, lang from t2 
start with emp=1 
connect by prior emp=mgr; 
 
1            chinese 
2     1     chinese 
2     1     english 
1            english 
2     1     chinese 
2     1     english

lang=’chinese’過濾之后：

1            chinese 
2     1     chinese 
2     1     chinese

出現重復行，顯然不是我們期望的結果。

七)CONNECT BY LEVEL

下面我再來看看一個特殊的用法 CONNECT BY LEVEL，這是一個理解起來令人頭痛，但同時在某些情境下又是非常有用的：

select LEVEL from dual CONNECT BY LEVEL<=6; 
 
1 
2 
3 
4 
5 
6

如果你以前從未使用過，但是不幸你猜中了結果，我深表佩服，我至今沒有想通，事實上，它甚至不太符合結構化查詢CONNECT BY的語法，因為根據Oracle文檔，CONNECT BY條件中至少有一個表達式要使用PRIOR關鍵字。以至于有人覺得CONNECT BY LEVEL是一個BUG，懷疑Oracle可能在后續的版本中加以糾正。

無論如何，CONNECT BY LEVEL在Oracle 10g/11g中運行良好，如果你不想費勁想通這其中的原由，可以簡單地把想認為是構造了一個循環，因此如果你寫成CONNECT BY 1=1，則會輸出1到無窮大的數。

原文鏈接：http://www.cnblogs.com/KissKnife/archive/2011/02/25/1964816.html

【編輯推薦】

SQL Server 2008 R2 SP1正式版發布
Facebook對MySQL依賴的后果將是“比死還糟”
土法炮制數據庫家譜網幫你查祖宗
大數據時代已來臨，你準備好了嗎？

責任編輯：艾婧來源：側身向南邊的博客

Oracle