建站
咨詢
售后
建站咨詢
新聞中心
一)準(zhǔn)備測(cè)試數(shù)據(jù)
閑話少說，直入正題。建立一張簡(jiǎn)單的職工表 t_hierarchical:
- Emp 職工編號(hào)
- Mgr 職工的直接上司(Mgr本身也是職工)
- Emp_name 職工姓名
插入一些測(cè)試數(shù)據(jù)，除了大老板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用于指定 父-子 記錄的關(guān)系(PRIOR我們?cè)谙吕薪忉?，更直觀一些)。舉emp 2為例，他隸屬于emp 1，如果我們以emp 1為根節(jié)點(diǎn)，顯然LEVEL=2;以emp 2自身為根節(jié)點(diǎn)，則LEVEL=1，這就是為什么上述查詢結(jié)果中出現(xiàn)共色標(biāo)識(shí)部分那兩行記錄，其它的類推。
三)START WITH
通常我們需要更直觀、更具有實(shí)用性的結(jié)果，這需要用到結(jié)構(gòu)化查詢中的START WITH子句，用于指定根節(jié)點(diǎn)：
- 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
這里我們指定了根節(jié)點(diǎn)是emp 1，這樣的結(jié)果直觀了許多，例如，以emp 1為根節(jié)點(diǎn)，那么emp 3位于第三級(jí)(emp 1—emp 2—emp 3)，這里補(bǔ)充一下 PRIOR 關(guān)鍵字的說明，個(gè)人觀點(diǎn)：“PRIOR emp=mgr”表示前一條記錄的emp編號(hào) = 當(dāng)前記錄的mgr編號(hào)，從查詢結(jié)果中可以看出這一點(diǎn)。同時(shí)，從查詢結(jié)果中還能發(fā)現(xiàn)明顯的 遞歸 痕跡，參見不同顏色標(biāo)識(shí)的數(shù)字。
四)SYS_CONNECT_BY_PATH()
不得不介紹一下非常牛波依的SYS_CONNECT_BY_PATH()函數(shù)，我們可以得到層次結(jié)構(gòu)或者說樹狀結(jié)構(gòu)的 路徑， 參見如下：
- 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(葉子)，即沒有任何節(jié)點(diǎn)隸屬于該節(jié)點(diǎn)：
- 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子句
下面再說說，關(guān)于引入結(jié)構(gòu)化查詢后，SQL語句的執(zhí)行順序問題，根據(jù)Oracle文檔，先后是：
1)JOIN，無論用的是JOIN ON的寫法，還是在WHERE中做的關(guān)聯(lián)
2)CONNECT BY
3)其它的WHERE條件
看一個(gè)例子，假設(shè)上面的各位職工，需要保存一些注釋信息，同時(shí)這些信息根據(jù)中文、英文分成兩個(gè)不同版本，我們可以簡(jiǎn)單設(shè)計(jì)一下這個(gè)注釋表：
- |-Emp 職工編號(hào)
- |-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
現(xiàn)在需要在原有的職工結(jié)構(gòu)化查詢中包括每個(gè)職工的中文注釋信息，我們看看下面的查詢：
- 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
再看這個(gè)查詢，看起來與前者是一樣的：
- 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
第二個(gè)是我們期望的結(jié)果，第二個(gè)則相去甚遠(yuǎn)。追究原因，是因?yàn)榍耙粋€(gè)例子中第二個(gè)條件 td.lang=’chinese’不被認(rèn)為是JOIN條件，所以在CONNECT BY之后執(zhí)行；后一個(gè)例子中由于顯式地把第二個(gè)條件寫在了JOIN ON子句中，所以它在CONNECT BY之前執(zhí)行。
由于缺少第二個(gè)條件的JOIN（即本節(jié)***例）會(huì)導(dǎo)致每個(gè)的職工出現(xiàn)兩次，換一個(gè)數(shù)據(jù)少一點(diǎn)的例子，看看CONNECT BY遇到這樣的重復(fù)數(shù)據(jù)的時(shí)候是怎么處理的。
- 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
出現(xiàn)重復(fù)行，顯然不是我們期望的結(jié)果。
七)CONNECT BY LEVEL
下面我再來看看一個(gè)特殊的用法 CONNECT BY LEVEL，這是一個(gè)理解起來令人頭痛，但同時(shí)在某些情境下又是非常有用的：
- select LEVEL from dual CONNECT BY LEVEL<=6;
- 1
- 2
- 3
- 4
- 5
- 6
如果你以前從未使用過，但是不幸你猜中了結(jié)果，我深表佩服，我至今沒有想通，事實(shí)上，它甚至不太符合結(jié)構(gòu)化查詢CONNECT BY的語法，因?yàn)楦鶕?jù)Oracle文檔，CONNECT BY條件中至少有一個(gè)表達(dá)式要使用PRIOR關(guān)鍵字。 以至于有人覺得CONNECT BY LEVEL是一個(gè)BUG，懷疑Oracle可能在后續(xù)的版本中加以糾正。
無論如何，CONNECT BY LEVEL在Oracle 10g/11g中運(yùn)行良好，如果你不想費(fèi)勁想通這其中的原由，可以簡(jiǎn)單地把想認(rèn)為是構(gòu)造了一個(gè)循環(huán)，因此如果你寫成CONNECT BY 1=1，則會(huì)輸出1到無窮大的數(shù)。
文章標(biāo)題：Oracle Handbook系列之結(jié)構(gòu)化查詢
鏈接URL：http://www.dlmjj.cn/article/cocceho.html
