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;ITPUB个人空间2CI(W@ u6d [!|ok
  2  /

类型已创建。

SQL> CREATE OR REPLACE FUNCTION F_TO_T_IN (P_IN VARCHAR2) RETURN T_IN ASITPUB个人空间RPJ&T+T;W@&F
  2   V_RETURN T_IN DEFAULT T_IN();ITPUB个人空间+Cs$C#g8b7j{;Q
  3   V_IN VARCHAR2(32767);
?Fm$y4`jG'X9x-Y'a0  4   V_COUNT NUMBER DEFAULT 0;ITPUB个人空间 ~sA,Wqf5n3o
  5  BEGIN
5qM,@B8W"R0  6   V_IN := LTRIM(P_IN || '/', '/');ITPUB个人空间n3WV1C%x3MM
  7   WHILE(INSTR(V_IN, '/') > 0) LOOP
ZC I$U,B1Q/C0  8    V_RETURN.EXTEND;
A#NjF L&Ix0  9    V_COUNT := V_COUNT + 1;ITPUB个人空间!ZvJ@2D2P/J
 10    V_RETURN(V_COUNT) := SUBSTR(V_IN, 1, INSTR(V_IN, '/') - 1);
7C.yNn+Q{0 11    V_IN := SUBSTR(V_IN, INSTR(V_IN, '/') + 1);ITPUB个人空间1h,d
]X)i`;R1V`2T
 12   END LOOP;
D"j:V8wjy|0 13   RETURN V_RETURN;ITPUB个人空间Q O,j7s2ke2R Q0H
 14  END;ITPUB个人空间s IJ T!~"BbG
 15  /

函数已创建。

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

F_TO_T_IN('/0/1/2/3')ITPUB个人空间L|ys Z+Dfl
----------------------------------------------------------------------------ITPUB个人空间;O${+d%Q&IT*Y
T_IN(0, 1, 2, 3)

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

COLUMN_VALUEITPUB个人空间8L4R'O-D$y#[HpX+SfT
------------
0I4|hW5JAo0           0ITPUB个人空间xd(oU:[;SP I8Kv
           1
|(Px
H5i v^0           2
r1v,W~.m)bE \}0           3

下面只需要关联原表就可以得到相应的记录：

SQL> SELECT A.*
c W,j5KsP0  2  FROM T_TREE A,ITPUB个人空间;^2k+r7vo+ld`
  3  TABLE(F_TO_T_IN(F_FIND_CHILD(0))) B
f#Dsp`mf0  4  WHERE A.ID = B.COLUMN_VALUE;

        ID  FATHER_ID NAME
Q%aoH9~ G^N(U8^0---------- ---------- ------------------------------ITPUB个人空间 x"Q/@sq7y-hz
         0          0 ROOTITPUB个人空间'^4m#O0Y7Ly
         1         0 AITPUB个人空间2H?5n7Uhd/\0DaE
         2          1 BC
[
fX)F8FO0         5          2 HIJ
.| WD[
|+K$y [^s0         3          1 DE
vj)iQ+@L s"R0         4          7 FG
m,Dq_7Q9fS!ys.Z"i||8S0         4          1 FG
KRH?f7{0         6          4 KLM
-XJ]7Ns$P1`0         7          6 NOPQ

已选择9行。

现在就自己实现了非循环方式CONNECT BY的记录，不过和CONNECT BY NOCYCLE方式相比，上面的记录还有点问题：

SQL> SELECT *
3BU,~;wu,}0  2  FROM T_TREE
!S#qiwAc+o$~0  3  START WITH ID = 0ITPUB个人空间5khHy ULaS]
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

        ID  FATHER_ID NAMEITPUB个人空间0E-WCA3P
[I`
---------- ---------- -------------------------
[1ihp;p1L0         0          0 ROOTITPUB个人空间Bw$zf(V
         1         0 A
6@7[4h&e J;U(teW0         2          1 BCITPUB个人空间H&i@{^rt
         5          2 HIJITPUB个人空间1UZ2b&xA,n tU
         3          1 DEITPUB个人空间){ kw_uq
         4          1 FGITPUB个人空间P { q%s~(CrM}2^
         6          4 KLM
5BiM4`)Ao#wynF0         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 ASITPUB个人空间m["L1w~ ~
  2   V_STR_ID VARCHAR2(32767);
f%X:yx1W;aZ0  3   V_STR_PK VARCHAR2(32767) := '/' || P_VALUE;
4f)C-rr!@}0  4 ITPUB个人空间.faM\Mc5]&A)Eqk
  5   PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR2,ITPUB个人空间
}t8R(R |7b3] w
  6    P_STR_ID IN OUT VARCHAR2,
)f&f6F5C4Kv0  7    P_STR_PK IN OUT VARCHAR2) AS
K'SH5c n#]0  8   BEGINITPUB个人空间*}"o!u.d.FK,FNs
  9    FOR I IN (SELECT PK, ID FROM T_TREE WHERE FATHER_ID = P_FATHER) LOOPITPUB个人空间] J/aSf"s
 10     IF INSTR(P_STR_ID || '/', '/' || I.ID || '/') = 0 THENITPUB个人空间G'XK^5b
 11      P_STR_ID  := P_STR_ID || '/' || I.ID;
y:D9Ybg8F)AM0 12      P_STR_PK  := P_STR_PK || '/' || I.PK;

`7I9D1G#GB0 13      P_GET_CHILD_STR(I.ID, P_STR_ID, P_STR_PK);ITPUB个人空间E,f[w*n~h1R
 14     END IF;ITPUB个人空间
\!o0M ?_ z)H;f
 15    END LOOP;
1` {;tnW ~pw0 16   END;
P&rH!G!HL@|m_0 17  BEGINITPUB个人空间;AWH6h#})K#W.j-`
 18   FOR I IN (SELECT PK, ID FROM T_TREE WHERE PK = P_VALUE) LOOPITPUB个人空间:na#G6?L"qa
 19    V_STR_ID := '/' || I.ID;ITPUB个人空间5tW+e,r!c"f$y!T
 20    P_GET_CHILD_STR(I.ID, V_STR_ID, V_STR_PK);
s u'x)dHF ~0 21   END LOOP;ITPUB个人空间[h:tTh#\
 22   RETURN V_STR_PK;
tm ||-d*z0 23  END;
q/EP"X%Df0Z$C@0 24  /

函数已创建。

SQL> SELECT F_FIND_CHILD(1) FROM DUAL;

F_FIND_CHILD(1)
K3J'G1g K @9t Q0--------------------------------------------------------------------------------
%wap3f?{*OAY0/1/2/5/3/4/6/7

SQL> SELECT A.*ITPUB个人空间| uM6ZK%B
  2  FROM T_TREE A,ITPUB个人空间4I(a$p"hq:S:P
  3  TABLE(F_TO_T_IN(F_FIND_CHILD(1))) B
iw3~,KnceF8f0  4  WHERE A.PK = B.COLUMN_VALUE;

        PK         ID  FATHER_ID NAMEITPUB个人空间Le4G@*y_I2^}L|
---------- ---------- ---------- ------------------------------ITPUB个人空间vLIV-E
         1          1         0 A
.ma(F4lG4LJ0         2          2          1 BC
@~P0Ee0         5          5          2 HIJ
e"TY9X;z B7|g0         3          3          1 DEITPUB个人空间iu"~0{%R;q r1m0M!z
         4          4          1 FG
5|b&_uz Z0Z4m;l?0         6          6          4 KLM
0e!tI!D r+Z:y0         7          7          6 NOPQ

已选择7行。

现在初步实现9i中的CONNECT BY NOCYCLE树形查询的方式，测试一下不同情况下10g的CONNECT BY NOCYCLE和这里给出的方法是否等价：

SQL> SELECT *
f,yqA?_tr)n8fA0  2  FROM T_TREE
5r&g+D bP4c5Ua0  3  START WITH PK = 2ITPUB个人空间SG0X | i8@
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

       ID  FATHER_ID NAME                                   PK
,P$t
?{P1s0-------------------- ------------------------------ ----------
3@1Nj!X&} [l0         2          1 BC                                      2ITPUB个人空间 r4Jn8\IS@#g'B&e
         5          2 HIJ                                     5

SQL> SELECT A.*ITPUB个人空间D{Bf+M!c{
  2  FROM T_TREE A,
"|by4Mll.I]4C0  3  TABLE(F_TO_T_IN(F_FIND_CHILD(2))) B
w{ ?1l|t7wv0  4  WHERE A.PK = B.COLUMN_VALUE;

       ID  FATHER_ID NAME                                   PKITPUB个人空间Q*HCB R'NUGF!v
-------------------- ------------------------------ ----------ITPUB个人空间!W7@4U JO
         2          1 BC                                      2ITPUB个人空间C,VDy#|SX(I
         5          2 HIJ                                     5

SQL> SELECT *ITPUB个人空间e}6})VB
  2  FROM T_TREE
)~*Rz$k"P$M1`X0  3  START WITH PK = 4
2y2xR%bT.S%v0  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

       ID  FATHER_ID NAME                                   PK
$^#I Ks:z[{ J0-------------------- ------------------------------ ----------ITPUB个人空间VA
TdI*~6iO;r
         4          1 FG                                      4
Cc2dO}$j/Of+v0         6          4 KLM                                     6
i7|#L\2f~0         7          6 NOPQ                                    7

SQL> SELECT A.*ITPUB个人空间+_+jO@6l,jQo
  2  FROM T_TREE A,
Z;VRx:Y aV0  3  TABLE(F_TO_T_IN(F_FIND_CHILD(4))) B
i5t`1a7R,_3H'cP-z0  4  WHERE A.PK = B.COLUMN_VALUE;

       ID  FATHER_ID NAME                                   PK
$a%V~wTL.GKh
^{0-------------------- ------------------------------ ----------ITPUB个人空间\u[:R*W.g d
         4          1 FG                                      4ITPUB个人空间w,A a4Xn|`
         6          4 KLM                                     6
;^ec,o r0         7          6 NOPQ                                    7

SQL> SELECT *
9E:z,_:LjC
_[J-H2[0  2  FROM T_TREEITPUB个人空间6~A4w2}9\FH&x
  3  START WITH PK = 9ITPUB个人空间b!@*mb3NJ
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

       ID  FATHER_ID NAME                                   PK
-t'frxg/_C0-------------------- ------------------------------ ----------ITPUB个人空间G"L$c7CiQl
         4          7 FG                                      9ITPUB个人空间$rA6G ]%M S4h;y
         6          4 KLM                                     6
$Z;I V-I-J!T%a]0         7          6 NOPQ                                    7

SQL> SELECT A.*
XQ6Y!L;n/Z0  2  FROM T_TREE A,ITPUB个人空间1YZG%W:l&I
  3  TABLE(F_TO_T_IN(F_FIND_CHILD(9))) BITPUB个人空间+EK^ye"G+`!c^
  4  WHERE A.PK = B.COLUMN_VALUE;

       ID  FATHER_ID NAME                                   PKITPUB个人空间NYO2p3P
-------------------- ------------------------------ ----------ITPUB个人空间ZeGN/kx8d&Q
         4          7 FG                                      9ITPUB个人空间V!@a%G2kDA-r
         6          4 KLM                                     6ITPUB个人空间wD"|k7r
n
         7          6 NOPQ                                    7