Oracle10g新特性—增強的CONNECTBY子句

　　為了更好的查詢一個樹狀結構的表在Oracle的PL/SQL中提供樂一個誘人的特性——CONNECT BY子句它大大的方便了我們查找樹狀表遍歷一棵樹尋找某個分支……但還是存在一些不足在Oracle G就對這個特性做了增強下面就舉例說明一下

　　CONNECT_BY_ROOT

　　一張表有多顆子樹（根節點為）現在我想知道每個節點屬於哪個子樹舉例鈴音目錄結構下有多個大分類中外名曲流行經典浪漫舞曲……每個大類下面又有多個子類子類下面還可以細分那現在想要知道每個子類分屬哪個大類或者要統計每個大類下面有多少個子類

　　看下面的例子DIRINDEX分別為的就是大分類其他編號的都是子類或孫子類

　　select dirindex fatherindex RPAD( *(LEVEL)) || dirname from t_tonedirlib
        start with fatherindex =
        connect by fatherindex = prior dirindex
        DIRINDEX FATHERINDEX DIRNAME

　　中文經典
        kkkkkkk

        sixx
        seven
        uiouoooo
        four
        流行風雲
        影視金曲
        aaa
        bbb
        ccc
        古典音樂
        小熊之家
        龍珠
        snoppy
        叮當
        龍貓
        叮當
        熱門流行
        有獎活動
        相約香格裡拉
        新浪彩鈴
        老歌回放
        老電影
        懷舊金曲
        rows selected

　　如何統計三個大類下有哪些子類有多少個子類？在i及以前要做這樣的統計十分麻煩現在G提供了一個新特性CONNECT_BY_ROOT他的作用就是使結果不是當前的節點ID而滿足查詢條件下的根節點的ID以上面為例我們需要得到以上結果只需要執行以下語句就可以搞定了

　　select CONNECT_BY_ROOT dirindex fatherindex RPAD( *(LEVEL)) || dirname from t_tonedirlib
        start with fatherindex =
        connect by fatherindex = prior dirindex
        CONNECT_BY_ROOTDIRINDEX FATHERINDEX RPAD(*(LEVEL))||DIRNAME

　　查出來的結果中CONNECT_BY_ROOTDIRINDEX就是各個子類（孫子類）所屬的大類編號如果需要統計就只要執行以下語句馬上可以統計出來了          select rootindex count(X) from
        (select CONNECT_BY_ROOT dirindex as rootindex
        from t_tonedirlib
        start with fatherindex =
        connect by fatherindex = prior dirindex) a
        group by arootindex
        ROOTINDEX COUNT(X)




        rows selected
        CONNECT_BY_ISLEAF

　　經常有DBA因為要查找樹狀表中的葉子節點而苦惱大部分DBA為了解決這個問題就給表增加了一個字段來描述這個節點是否為葉子節點但這樣做有很大的弊端需要通代碼邏輯來保證這個字段的正確性

　　Oracle G中提供了一個新特性——CONNECT_BY_ISLEAF——來解決這個問題了簡單點說這個屬性結果表明當前節點在滿足條件的查詢結果中是否為葉子節點不是是          select CONNECT_BY_ISLEAF dirindex fatherindex RPAD( *(LEVEL)) || dirname
        from t_tonedirlib
        start with fatherindex =
        connect by fatherindex = prior dirindex
        CONNECT_BY_ISLEAF DIRINDEX FATHERINDEX RPAD( *(LEVEL))||dirname

        中文經典
        kkkkkkk

        sixx
        seven
        uiouoooo
        four
        流行風雲
        影視金曲
        aaa
        bbb
        ccc
        古典音樂
        小熊之家
        龍珠
        snoppy
        叮當
        龍貓
        叮當
        熱門流行
        有獎活動
        相約香格裡拉
        新浪彩鈴
        老歌回放
        老電影
        懷舊金曲
        rows selected

　　一看結果清晰明了！

　　CONNECT_BY_ISCYCLE

　　我們的樹狀屬性一般都是在一條記錄中記錄一個當前節點的ID和這個節點的父ID來實現但是一旦數據中出現了循環記錄如兩個節點互為對方父節點系統就會報ORA錯誤          insert into t_tonedirlib(dirindex fatherindex dirname status) values ( );
        row inserted
        insert into t_tonedirlib(dirindex fatherindex dirname status) values ( );
        row inserted
        select dirindex fatherindex RPAD( *(LEVEL)) || dirname from t_tonedirlib
        start with fatherindex =
        connect by fatherindex = prior dirindex
        ORA: 用戶數據中的 CONNECT BY 循環

　　G中可以通過加上NOCYCLE關鍵字避免報錯並且通過CONNECT_BY_ISCYCLE屬性就知道哪些節點產生了循環          select CONNECT_BY_ISCYCLE dirindex fatherindex RPAD( *(LEVEL)) || dirname
        from t_tonedirlib
        start with fatherindex =
        connect by NOCYCLE fatherindex = prior dirindex
        CONNECT_BY_ISCYCLE DIRINDEX FATHERINDEX RPAD( *(LEVEL))||dirname



        rows selected

　　以上就是在G中增強的CONNECT BY了當然對於這些增強特性的作用肯定不止如上介紹的還需要更多高人去挖掘了

From:http://tw.wingwit.com/Article/program/Oracle/201311/18952.html