10g树形查询新特性CONNECT_BY_ISCYCLE的9i实现方式（三）

在10g中Oracle提供了新的伪列：CONNECT_BY_ISCYCLE，通过这个伪列，可以判断是否在树形查询的过程中构成了循环，这个伪列只是在CONNECT BY NOCYCLE方式下有效。

初步实现CONNECT_BY_ISCYCLE的功能。

10g树形查询新特性CONNECT_BY_ISCYCLE的9i实现方式（一）：http://yangtingkun.itpub.net/post/468/466977

10g树形查询新特性CONNECT_BY_ISCYCLE的9i实现方式（二）：http://yangtingkun.itpub.net/post/468/467154

上一篇文章中描述了如何在9i中实现CONNECT BY NOCYCLE，在这个基础上，终于可以实现这个系列文章的最终目标：CONNECT_BY_ISCYCLE。

首先还是通过例子看看CONNECT_BY_ISCYCLE的功能：

SQL> CREATE TABLE T_TREE
mL0H2@F ? q4V0  2  (
;J4x Xi+niS0  3   PK NUMBER PRIMARY KEY,
Q!l-L b` O+i"G:W&Oi0  4   ID NUMBER,
TrHr B!~$zQc0  5   FATHER_ID NUMBER,
x*Mqo/|0  6   NAME VARCHAR2(30)ITPUB个人空间 Wn'}{#|x
  7  );

表已创建。

SQL> INSERT INTO T_TREE VALUES (1, 1, 0, 'A');

已创建1行。

SQL> INSERT INTO T_TREE VALUES (2, 2, 1, 'BC');

已创建1行。

SQL> INSERT INTO T_TREE VALUES (3, 3, 1, 'DE');

已创建1行。

SQL> INSERT INTO T_TREE VALUES (4, 4, 1, 'FG');

已创建1行。

SQL> INSERT INTO T_TREE VALUES (5, 5, 2, 'HIJ');

已创建1行。

SQL> INSERT INTO T_TREE VALUES (6, 6, 4, 'KLM');

已创建1行。

SQL> INSERT INTO T_TREE VALUES (7, 7, 6, 'NOPQ');

已创建1行。

SQL> INSERT INTO T_TREE VALUES (8, 0, 0, 'ROOT');

已创建1行。

SQL> INSERT INTO T_TREE VALUES (9, 4, 7, 'FG');

已创建1行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM T_TREE;

        PK         ID  FATHER_ID NAME

[b`1J6@4^%TZ;~;S;B0---------- ---------- ---------- ------------------------------ITPUB个人空间w3^V~'mwP
         1          1         0 A
;rI:GE.Bx&n"G0         2          2          1 BCITPUB个人空间 y%Y&l3~:U;us&R@q5YJ2d
         3          3          1 DE
z3m*~G
ot0         4          4          1 FG
.MCN bj)E{t dBw0         5          5          2 HIJITPUB个人空间b%Jm A,lu
         6          6          4 KLM
kl| p%H0         7          7          6 NOPQ
`%E*p"tTGr [+{1X ae0         8          0          0 ROOT
)Tlps:}U0         9          4          7 FG

已选择9行。

SQL> SELECT A.*, CONNECT_BY_ISCYCLE CYCLEDITPUB个人空间L!Bl
`j!|DmJ&viJ@
  2  FROM T_TREE A
*\wSq'A5^
p0  3  START WITH PK = 8ITPUB个人空间!lso!g2|y.^:S}0o
  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

        PK         ID  FATHER_ID NAME                               CYCLED
idGe-XG
C*?Y&kL0---------- ---------- ---------- ------------------------------ ----------ITPUB个人空间h]G-r"b
         8          0          0 ROOT                                    1ITPUB个人空间F"{,mKFY8q
         1          1         0 A                                       0
4v!ni{S*tY C+z#z0         2          2          1 BC                                      0
.T+C m[N*f3}0         5          5          2 HIJ                                     0ITPUB个人空间b
K%r
t(LxVU&oT j
         3          3          1 DE                                      0ITPUB个人空间4@ m3N'^0CK
         4          4          1 FG                                      0
op'QFn B2A|0         6          6          4 KLM                                     0
_enU0{9v5L0         7          7          6 NOPQ                                    1

已选择8行。

SQL> SELECT A.*, CONNECT_BY_ISCYCLE CYCLED
P |,BZ7V)S0|}0  2  FROM T_TREE AITPUB个人空间v7Y.tSI.c
  3  START WITH PK = 4
qNZI[7NU0  4  CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

        PK         ID  FATHER_ID NAME                               CYCLEDITPUB个人空间-h n8`!S)A(Zg4V$c
---------- ---------- ---------- ------------------------------ ----------
{jAhki0         4          4          1 FG                                      0
'm YU7]N_a
i0         6          6          4 KLM                                     0ITPUB个人空间O_a;O j$\/j9S'E
         7          7          6 NOPQ                                    1

为了实现这个功能，还是只能通过PL/SQL函数想办法，因为只有在这个函数中，才能获得哪条记录出现循环。

实现的方法有很多种，比如通过单独的函数来实现，或者将前面的T_IN改为一个RECORD，使用单独的列来存放是否循环标识，不过这些方法都比较麻烦。最简单的方法莫过于在当前的函数返回值上做文章。

这里可以做的文章很多，比如当前的例子，由于PK都是整数，所以在出现循环的PK处添加了小数部分。

SQL> CREATE OR REPLACE FUNCTION F_FIND_CHILD(P_VALUE VARCHAR2) RETURN VARCHAR2 ASITPUB个人空间)L!]MC6k!C3s:Gj,Wq
  2   V_STR_ID VARCHAR2(32767);
!{2o+O s zfA0  3   V_STR_PK VARCHAR2(32767);
DF:pTQ#Ub9L|$b0  4  
k#P%CVDC,J0  5   PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR2,
$l~-~u:qW\8xl0  6    P_STR_ID IN OUT VARCHAR2,ITPUB个人空间2c.H Db M|~h"O
  7    P_STR_PK IN OUT VARCHAR2) ASITPUB个人空间5G#F+o
~+JYrP
  8   BEGIN
b"s T~`0  9    FOR I IN (SELECT PK, ID, FATHER_ID FROM T_TREE WHERE FATHER_ID = P_FATHER
,~
ErLc"r0 10     ORDER BY DECODE(ID, FATHER_ID, 0, 1)) LOOPITPUB个人空间DNu9\j
 11     IF INSTR(P_STR_ID || '/', '/' || I.ID || '/') > 0 THEN
%Rt1L3yT0 12      P_STR_PK := P_STR_PK || '.1';
um4u~2\6l8]k%Q\0 13     ELSE
(SsQ4r+yx0 14      P_STR_ID  := P_STR_ID || '/' || I.ID;ITPUB个人空间9Y1p(e4[W0X!l
 15      P_STR_PK  := P_STR_PK || '/' || CASE WHEN I.ID = I.FATHER_ID THEN '.1' END || I.PK;
kWl&K$vcoI0 16      P_GET_CHILD_STR(I.ID, P_STR_ID, P_STR_PK);
/K4YF dPt
R(EM0 17     END IF;
8q)^e'}h Z(Myf*I0 18    END LOOP;ITPUB个人空间 x4|&c4APQ
 19   END;ITPUB个人空间J
[ww+`j m7p
 20  BEGINITPUB个人空间;Kes,G"a?/u ?/R
\
 21   FOR I IN (SELECT PK, ID, FATHER_ID FROM T_TREE WHERE PK = P_VALUE) LOOPITPUB个人空间I1N;v7g'n:B
 22    V_STR_ID := '/' || I.ID;
t$J {uSf)CTI0 23    V_STR_PK := '/' || I.PK;ITPUB个人空间1c.z!d4m'q^k[XE
 24    P_GET_CHILD_STR(I.ID, V_STR_ID, V_STR_PK); ITPUB个人空间"H.u q!b,K3jDc
 25   END LOOP;ITPUB个人空间 @7S,n(]$T
 26   RETURN V_STR_PK;
6Y5or%]6Jo1L0 27  END;
p i.xOA1T?r u0 28  /

函数已创建。

SQL> SELECT F_FIND_CHILD(8) FROM DUAL;

F_FIND_CHILD(8)
;e~j5_'C:S#\4^!~8X0--------------------------------------------------------------------------------ITPUB个人空间D'[(OEsE5f:fZ
/8.1/1/2/5/4/6/7.1/3

下面只需要修改一下查询的SQL语句：

SQL> SELECT A.*, DECODE(B.COLUMN_VALUE, A.PK, 0, 1) CYCLEDITPUB个人空间V3ETbF"i
  2  FROM T_TREE A,
P ?}0M%S9g!|8U0  3  TABLE(F_TO_T_IN(F_FIND_CHILD(8))) BITPUB个人空间{bF nRg3]A{
  4  WHERE A.PK = TRUNC(B.COLUMN_VALUE);

        PK         ID  FATHER_ID NAME                               CYCLED
}4{h0g4|9bQ0---------- ---------- ---------- ------------------------------ ----------
(E,p+[]:~8u0         8          0          0 ROOT                                    1ITPUB个人空间^Rhh#x$V KFb
         1          1         0 A                                       0ITPUB个人空间p%g7W Txh s-?
         2          2          1 BC                                      0ITPUB个人空间ab1Q x/B3NV8Kq
         5          5          2 HIJ                                     0
P"J8S'KV0         4          4          1 FG                                      0
7vjgvk9N0         6          6          4 KLM                                     0ITPUB个人空间v(K ve0c8e'O
         7          7          6 NOPQ                                    1ITPUB个人空间;~-Jf},g-gz Qt
         3          3          1 DE                                      0

已选择8行。

SQL> SELECT A.*, DECODE(B.COLUMN_VALUE, A.PK, 0, 1) CYCLED
m_(v$Q;p
f l1Ph j;w0  2  FROM T_TREE A,
'gjFf.~D
^0  3  TABLE(F_TO_T_IN(F_FIND_CHILD(4))) BITPUB个人空间gOH^^.GK-S
  4  WHERE A.PK = TRUNC(B.COLUMN_VALUE);

        PK         ID  FATHER_ID NAME                               CYCLEDITPUB个人空间;xO~3][
---------- ---------- ---------- ------------------------------ ----------ITPUB个人空间3U0f'X9_8Q5VI
         4          4          1 FG                                      0
qHR`1c/CFC-G0         6          6          4 KLM                                     0ITPUB个人空间6@lQ9u2MH vF5s
         7          7          6 NOPQ                                    1

类似的技巧很多，比如将循环PK的返回值设为负数，或者在PK后面添加一个固定的标识等等。