10g樹形查詢新特性

　　在g中Oracle提供了新的偽列CONNECT_BY_ISCYCLE通過這個偽列可以判斷是否在樹形查詢的過程中構成了循環這個偽列只是在CONNECT BY NOCYCLE方式下有效

　　這一篇描述一下解決問題的思路

　　CONNECT_BY_ISCYCLE的實現和前面兩篇文章中CONNECT_BY_ROOT和CONNECT_BY_ISLEAF的實現完全不同

　　因為要實現CONNECT_BY_ISCYCLE就必須先實現CONNECT BY NOCYCLE而在i中是沒有方法實現這個功能的

　　也就是說首先要實現自己的樹形查詢的功能而僅這第一點就是一個異常困難的問題何況後面還要實現NOCYCLE最後再加上一個ISCYCLE的判斷

　　所以總的來說這個功能的實現比前面兩個功能要復雜得多由於樹形查詢的LEVEL是不固定的所以采用鏈接的方式實現基本上是不現實的換句話說用純SQL的方式來實現樹形查詢的功能基本上不可行而為了解決這個功能只能通過PL/SQL配合SQL來實現

　　仍然是首先構造一個例子

　　SQL> CREATE TABLE T_TREE (ID NUMBER FATHER_ID NUMBER NAME VARCHAR());

　　表已創建

　　SQL> INSERT INTO T_TREE VALUES ( A);

　　已創建行

　　SQL> INSERT INTO T_TREE VALUES ( BC);

　　已創建行

　　SQL> INSERT INTO T_TREE VALUES ( DE);

　　已創建行

　　SQL> INSERT INTO T_TREE VALUES ( FG);

　　已創建行

　　SQL> INSERT INTO T_TREE VALUES ( HIJ);

　　已創建行

　　SQL> INSERT INTO T_TREE VALUES ( KLM);

　　已創建行

　　SQL> INSERT INTO T_TREE VALUES ( NOPQ);

　　已創建行

　　SQL> INSERT INTO T_TREE VALUES ( ROOT);

　　已創建行

　　SQL> INSERT INTO T_TREE VALUES ( FG);

　　已創建行

　　SQL> COMMIT;

　　提交完成

　　SQL> SELECT * FROM T_TREE;

　　ID FATHER_ID NAME

　　 HIJ

　　 KLM

　　 NOPQ

　　 ROOT

　　已選擇行

　　上面構造了兩種樹形查詢循環的情況一種是當前記錄的自循環另一種是樹形查詢的某個子節點是當前節點的祖先節點從而構成了循環在這個例子中記錄ID為和ID為且FATHER_ID等於的兩條記錄分別構成了上述的兩種循環的情況

　　下面就來看看CONNECT_BY_ISCYCLE和CONNECT BY NOCYCLE的功能

　　SQL> SELECT *

　　 FROM T_TREE

　　 START WITH ID =

　　 CONNECT BY PRIOR ID = FATHER_ID;

　　ERROR:

　　ORA: 用戶數據中的 CONNECT BY 循環

　　未選定行

　　SQL> SELECT *

　　 FROM T_TREE

　　 START WITH ID =

　　 CONNECT BY PRIOR ID = FATHER_ID;

　　ERROR:

　　ORA: 用戶數據中的 CONNECT BY 循環

　　未選定行

　　這就是不使用CONNECT BY NOCYCLE的情況查詢會報錯指出樹形查詢中出現循環在g中可以使用CONNECT BY NOCYCLE的方式來避免錯誤的產生

　　SQL> SELECT *

　　 FROM T_TREE

　　 START WITH ID =

　　 CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

　　ID FATHER_ID NAME

　　 ROOT

　　 HIJ

　　 KLM

　　 NOPQ

　　已選擇行

　　使用CONNECT BY NOCYCLEOracle自動避免循環的產生將不產生循環的數據查詢出來下面看看CONNECT_BY_ISCYCLE的功能

　　SQL> SELECT ID

　　 FATHER_ID

　　 NAME

　　 CONNECT_BY_ISCYCLE CYCLED

　　 FROM T_TREE

　　 START WITH ID =

　　 CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

　　ID FATHER_ID NAME CYCLED

　　 ROOT

　　 HIJ

　　 KLM

　　 NOPQ

　　已選擇行

　　可以看到CONNECT_BY_ISCYCLE偽列指出循環在樹形查詢中發生的位置

　　為了實現CONNECT_BY_ISCYCLE就必須先實現CONNECT BY NOCYCLE方式而這在i中是沒有現成的辦法的所以這裡嘗試使用PL/SQL來自己實現樹形查詢的功能

　　SQL> CREATE OR REPLACE FUNCTION F_FIND_CHILD(P_VALUE VARCHAR) RETURN VARCHAR AS

　　 V_STR VARCHAR() := / || P_VALUE;

　　 PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR P_STR IN OUT VARCHAR) AS

　　 BEGIN

　　 FOR I IN (SELECT ID FROM T_TREE WHERE FATHER_ID = P_FATHER AND FATHER_ID != ID) LOOP

　　 IF INSTR(P_STR || / / || IID || /) = THEN

　　 P_STR := P_STR || / || IID;

　　 P_GET_CHILD_STR(IID P_STR);

　　 END IF;

　　 END LOOP;

　　 END;

　　 BEGIN

　　 P_GET_CHILD_STR(P_VALUE V_STR);

　　 RETURN V_STR;

　　 END;

　　函數已創建

　　構造一個函數在函數中遞歸調用過程來實現樹形查詢的功能

　　下面看看調用這個函數的結果

　　SQL> SELECT F_FIND_CHILD() FROM DUAL;

　　F_FIND_CHILD()

　　////////

　　SQL> SELECT F_FIND_CHILD() FROM DUAL;

　　F_FIND_CHILD()

　　SQL> SELECT F_FIND_CHILD() FROM DUAL;

　　F_FIND_CHILD()

　　///

　　雖然目前存在的問題還有很多但是已經基本上實現了一個最簡單的NOCYCLE的SYS_CONNECT_BY_PATH的功能

　　有了這個函數作為基礎就可以逐步的實現最終的目標了

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