10g樹形查詢新特性CONNECT_BY_ISCYCLE的9i實現方式（二）

有了前面一篇的基礎，可以獲取到類似SYS_CONNECT_BY_PATH的ID字串，透過解析這個字串就可以從原表中來讀取對應的記錄。

在前不久剛剛寫過一篇文章，描述如何處理將固定字元分隔的字串轉化為表的形式：http://yangtingkun.itpub.net/post/468/455390

採用上面文章中介紹的方法，就可以將獲取的字串轉化為表的形式，不過由於這裡是分隔符是’/’不是’,’，因此需要將上面的過程進行一下修改：

SQL> CREATE OR REPLACE TYPE T_IN IS TABLE OF NUMBER;
  2  /

型別已建立。

SQL> CREATE OR REPLACE FUNCTION F_TO_T_IN (P_IN VARCHAR2) RETURN T_IN AS
  2   V_RETURN T_IN DEFAULT T_IN();
  3   V_IN VARCHAR2(32767);
  4   V_COUNT NUMBER DEFAULT 0;
  5  BEGIN
  6   V_IN := LTRIM(P_IN || '/', '/');
  7   WHILE(INSTR(V_IN, '/') > 0) LOOP
  8    V_RETURN.EXTEND;
  9    V_COUNT := V_COUNT + 1;
 10    V_RETURN(V_COUNT) := SUBSTR(V_IN, 1, INSTR(V_IN, '/') - 1);
 11    V_IN := SUBSTR(V_IN, INSTR(V_IN, '/') + 1);
 12   END LOOP;
 13   RETURN V_RETURN;
 14  END;
 15  /

函式已建立。

SQL> SELECT F_TO_T_IN('/0/1/2/3') FROM DUAL;

F_TO_T_IN('/0/1/2/3')
----------------------------------------------------------------------------
T_IN(0, 1, 2, 3)

SQL> SELECT * FROM TABLE(F_TO_T_IN('/0/1/2/3'));

COLUMN_VALUE
------------
           0
           1
           2
           3

下面只需要關聯原表就可以得到相應的記錄：

SQL> SELECT A.*
  2  FROM T_TREE A,
  3  TABLE(F_TO_T_IN(F_FIND_CHILD(0))) B
  4  WHERE A.ID = B.COLUMN_VALUE;

        ID  FATHER_ID NAME
---------- ---------- ------------------------------
         0          0 ROOT
         1          0 A
         2          1 BC
         5          2 HIJ
         3          1 DE
         4          7 FG
         4          1 FG
         6          4 KLM
         7          6 NOPQ

已選擇9行。

現在就自己實現了非迴圈方式CONNECT BY的記錄，不過和CONNECT BY NOCYCLE方式相比，上面的記錄還有點問題：

SQL> SELECT *
  2  FROM T_TREE
  3  START WITH ID = 0
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

        ID  FATHER_ID NAME
---------- ---------- -------------------------
         0          0 ROOT
         1          0 A
         2          1 BC
         5          2 HIJ
         3          1 DE
         4          1 FG
         6          4 KLM
         7          6 NOPQ

已選擇8行。

可以看到，自己實現的結果多了一條記錄，不過這並不是演算法有問題，而是由於當前表中ID並非主鍵，而存在重複的情況。下面將表進行一下修改，新增一個真正的物理主鍵：

SQL> ALTER TABLE T_TREE ADD (PK NUMBER);

表已更改。

SQL> UPDATE T_TREE SET PK = ROWNUM;

已更新9行。

SQL> ALTER TABLE T_TREE ADD CONSTRAINT PK_T_TREE PRIMARY KEY (PK);

表已更改。

新增這個主鍵之後，函式也需要相應的修改：

SQL> CREATE OR REPLACE FUNCTION F_FIND_CHILD(P_VALUE VARCHAR2) RETURN VARCHAR2 AS
  2   V_STR_ID VARCHAR2(32767);
  3   V_STR_PK VARCHAR2(32767) := '/' || P_VALUE;
  4 
  5   PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR2,
  6    P_STR_ID IN OUT VARCHAR2,
  7    P_STR_PK IN OUT VARCHAR2) AS
  8   BEGIN
  9    FOR I IN (SELECT PK, ID FROM T_TREE WHERE FATHER_ID = P_FATHER) LOOP
 10     IF INSTR(P_STR_ID || '/', '/' || I.ID || '/') = 0 THEN
 11      P_STR_ID  := P_STR_ID || '/' || I.ID;
 12      P_STR_PK  := P_STR_PK || '/' || I.PK;
 13      P_GET_CHILD_STR(I.ID, P_STR_ID, P_STR_PK);
 14     END IF;
 15    END LOOP;
 16   END;
 17  BEGIN
 18   FOR I IN (SELECT PK, ID FROM T_TREE WHERE PK = P_VALUE) LOOP
 19    V_STR_ID := '/' || I.ID;
 20    P_GET_CHILD_STR(I.ID, V_STR_ID, V_STR_PK);
 21   END LOOP;
 22   RETURN V_STR_PK;
 23  END;
 24  /

函式已建立。

SQL> SELECT F_FIND_CHILD(1) FROM DUAL;

F_FIND_CHILD(1)
--------------------------------------------------------------------------------
/1/2/5/3/4/6/7

SQL> SELECT A.*
  2  FROM T_TREE A,
  3  TABLE(F_TO_T_IN(F_FIND_CHILD(1))) B
  4  WHERE A.PK = B.COLUMN_VALUE;

        PK         ID  FATHER_ID NAME
---------- ---------- ---------- ------------------------------
         1          1          0 A
         2          2          1 BC
         5          5          2 HIJ
         3          3          1 DE
         4          4          1 FG
         6          6          4 KLM
         7          7          6 NOPQ

已選擇7行。

現在初步實現9i中的CONNECT BY NOCYCLE樹形查詢的方式，測試一下不同情況下10g的CONNECT BY NOCYCLE和這裡給出的方法是否等價：

SQL> SELECT *
  2  FROM T_TREE
  3  START WITH PK = 2
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

        ID  FATHER_ID NAME                                   PK
---------- ---------- ------------------------------ ----------
         2          1 BC                                      2
         5          2 HIJ                                     5

SQL> SELECT A.*
  2  FROM T_TREE A,
  3  TABLE(F_TO_T_IN(F_FIND_CHILD(2))) B
  4  WHERE A.PK = B.COLUMN_VALUE;

        ID  FATHER_ID NAME                                   PK
---------- ---------- ------------------------------ ----------
         2          1 BC                                      2
         5          2 HIJ                                     5

SQL> SELECT *
  2  FROM T_TREE
  3  START WITH PK = 4
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

        ID  FATHER_ID NAME                                   PK
---------- ---------- ------------------------------ ----------
         4          1 FG                                      4
         6          4 KLM                                     6
         7          6 NOPQ                                    7

SQL> SELECT A.*
  2  FROM T_TREE A,
  3  TABLE(F_TO_T_IN(F_FIND_CHILD(4))) B
  4  WHERE A.PK = B.COLUMN_VALUE;

        ID  FATHER_ID NAME                                   PK
---------- ---------- ------------------------------ ----------
         4          1 FG                                      4
         6          4 KLM                                     6
         7          6 NOPQ                                    7

SQL> SELECT *
  2  FROM T_TREE
  3  START WITH PK = 9
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

        ID  FATHER_ID NAME                                   PK
---------- ---------- ------------------------------ ----------
         4          7 FG                                      9
         6          4 KLM                                     6
         7          6 NOPQ                                    7

SQL> SELECT A.*
  2  FROM T_TREE A,
  3  TABLE(F_TO_T_IN(F_FIND_CHILD(9))) B
  4  WHERE A.PK = B.COLUMN_VALUE;

        ID  FATHER_ID NAME                                   PK
---------- ---------- ------------------------------ ----------
         4          7 FG                                      9
         6          4 KLM                                     6
         7          6 NOPQ                                    7