| 為了更好的查詢一個樹狀結(jié)構(gòu)的表，在Oracle的PL/SQL中提供樂一個誘人的特性——CONNECT BY子句。它大大的方便了我們查找樹狀表：遍歷一棵樹、尋找某個分支……，但還是存在一些不足。在Oracle 10G，就對這個特性做了增強。下面就舉例說明一下： CONNECT_BY_ROOT 一張表，有多顆子樹（根節(jié)點為0），現(xiàn)在我想知道每個節(jié)點屬于哪個子樹。舉例：鈴音目錄結(jié)構(gòu)下有多個大分類：中外名曲、流行經(jīng)典、浪漫舞曲……，每個大類下面又有多個子類，子類下面還可以細分。那現(xiàn)在想要知道每個子類分屬哪個大類，或者要統(tǒng)計每個大類下面有多少個子類。 看下面的例子，DIRINDEX分別為1、2、3的就是大分類，其他編號的都是子類或?qū)O子類： select dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib start with fatherindex = 0 connect by fatherindex = prior dirindex DIRINDEX FATHERINDEX DIRNAME --------------------- ------------------------------------ 1 0 中文經(jīng)典 52 1 kkkkkkk 70 52 222 58 52 sixx 59 52 seven 69 52 uiouoooo 55 52 four 7 1 流行風云 8 1 影視金曲 1111 8 aaa 1112 8 bbb 1113 8 ccc 9 1 古典音樂 81 1 小熊之家 104 81 龍珠 105 81 snoppy 101 81 叮當1 102 81 龍貓 103 81 叮當2 2 0 熱門流行 31 2 有獎活動 32 2 相約香格里拉 50 2 新浪彩鈴 3 0 老歌回放 333 3 老電影 335 3 懷舊金曲 26 rows selected 如何統(tǒng)計1、2、3三個大類下有哪些子類，有多少個子類？在9i及以前要做這樣的統(tǒng)計十分麻煩?，F(xiàn)在10G提供了一個新特性：CONNECT_BY_ROOT， 他的作用就是使結(jié)果不是當前的節(jié)點ID，而滿足查詢條件下的根節(jié)點的ID。以上面為例，我們需要得到以上結(jié)果只需要執(zhí)行以下語句就可以搞定了： select CONNECT_BY_ROOT dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib start with fatherindex = 0 connect by fatherindex = prior dirindex CONNECT_BY_ROOTDIRINDEX FATHERINDEX RPAD('',2*(LEVEL-1))||DIRNAME ----------------------- ------------- ----------------------------- 1 0 中文經(jīng)典 1 1 kkkkkkk 1 52 222 1 52 sixx 1 52 seven 1 52 uiouoooo 1 52 four 1 1 流行風云 1 1 影視金曲 1 8 aaa 1 8 bbb 1 8 ccc 1 1 古典音樂 1 1 小熊之家 1 81 龍珠 1 81 snoppy 1 81 叮當1 1 81 龍貓 1 81 叮當2 2 0 熱門流行 2 2 有獎活動 2 2 相約香格里拉 2 2 新浪彩鈴 3 0 老歌回放 3 3 老電影 3 3 懷舊金曲 26 rows selected 查出來的結(jié)果中，CONNECT_BY_ROOTDIRINDEX就是各個子類（孫子類）所屬的大類編號，如果需要統(tǒng)計，就只要執(zhí)行以下語句馬上可以統(tǒng)計出來了： select rootindex, count('X') from (select CONNECT_BY_ROOT dirindex as rootindex from t_tonedirlib start with fatherindex = 0 connect by fatherindex = prior dirindex) a group by a.rootindex ROOTINDEX COUNT('X') --------- ---------- 1 19 2 4 3 3 3 rows selected CONNECT_BY_ISLEAF 經(jīng)常有DBA因為要查找樹狀表中的葉子節(jié)點而苦惱。大部分DBA為了解決這個問題就給表增加了一個字段來描述這 個節(jié)點是否為葉子節(jié)點。但這樣做有很大的弊端：需要通代碼邏輯來保證這個字段的正確性。 Oracle 10G中提供了一個新特性——CONNECT_BY_ISLEAF——來解決這個問題了。簡單點說，這個屬性結(jié)果表明當前節(jié)點在滿足條件的查詢結(jié)果中是否為葉子節(jié)點， 0不是，1是： select CONNECT_BY_ISLEAF, dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib start with fatherindex = 0 connect by fatherindex = prior dirindex CONNECT_BY_ISLEAF DIRINDEX FATHERINDEX RPAD(' ',2*(LEVEL-1))||dirname ----------------- ---------------- --------------------------------- 0 1 0 中文經(jīng)典 0 52 1 kkkkkkk 1 70 52 222 1 58 52 sixx 1 59 52 seven 1 69 52 uiouoooo 1 55 52 four 1 7 1 流行風云 0 8 1 影視金曲 1 1111 8 aaa 1 1112 8 bbb 1 1113 8 ccc 1 9 1 古典音樂 0 81 1 小熊之家 1 104 81 龍珠 1 105 81 snoppy 1 101 81 叮當1 1 102 81 龍貓 1 103 81 叮當2 0 2 0 熱門流行 1 31 2 有獎活動 1 32 2 相約香格里拉 1 50 2 新浪彩鈴 0 3 0 老歌回放 1 333 3 老電影 1 335 3 懷舊金曲 26 rows selected 一看結(jié)果，清晰明了！ CONNECT_BY_ISCYCLE 我們的樹狀屬性一般都是在一條記錄中記錄一個當前節(jié)點的ID和這個節(jié)點的父ID來實現(xiàn)。但是，一旦數(shù)據(jù)中出現(xiàn)了循環(huán)記錄，如兩個節(jié)點互為對方父節(jié)點，系統(tǒng)就會報ORA-01436錯誤： insert into t_tonedirlib(dirindex, fatherindex, dirname, status) values (666, 667, '123', 5); 1 row inserted insert into t_tonedirlib(dirindex, fatherindex, dirname, status) values (667, 666, '456', 5); 1 row inserted select dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib start with fatherindex = 666 connect by fatherindex = prior dirindex ORA-01436: 用戶數(shù)據(jù)中的 CONNECT BY 循環(huán) 10G中，可以通過加上NOCYCLE關(guān)鍵字避免報錯。并且通過CONNECT_BY_ISCYCLE屬性就知道哪些節(jié)點產(chǎn)生了循環(huán)： select CONNECT_BY_ISCYCLE, dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib start with fatherindex = 666 connect by NOCYCLE fatherindex = prior dirindex CONNECT_BY_ISCYCLE DIRINDEX FATHERINDEX RPAD(' ',2*(LEVEL-1))||dirname ----------------- ---------------- --------------------------------- 0 667 666 456 1 666 667 123 2 rows selected 以上就是在10G中增強的CONNECT BY了。當然對于這些增強特性的作用肯定不止如上介紹的，還需要更多高人去挖掘了。 | 
