在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
2 (
3 PK NUMBER PRIMARY KEY,
4 ID NUMBER,
5 FATHER_ID NUMBER,
6 NAME VARCHAR2(30)
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
---------- ---------- ---------- ------------------------------
1 1 0 A
2 2 1 BC
3 3 1 DE
4 4 1 FG
5 5 2 HIJ
6 6 4 KLM
7 7 6 NOPQ
8 0 0 ROOT
9 4 7 FG

已选择9行。

SQL> SELECT A.*, CONNECT_BY_ISCYCLE CYCLED
2 FROM T_TREE A
3 START WITH PK = 8
4 CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

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

已选择8行。

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

PK ID FATHER_ID NAME CYCLED
---------- ---------- ---------- ------------------------------ ----------
4 4 1 FG 0
6 6 4 KLM 0
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 AS
2 V_STR_ID VARCHAR2(32767);
3 V_STR_PK VARCHAR2(32767);
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, FATHER_ID FROM T_TREE WHERE FATHER_ID = P_FATHER
10 ORDER BY DECODE(ID, FATHER_ID, 0, 1)) LOOP
11 IF INSTR(P_STR_ID || '/', '/' || I.ID || '/') > 0 THEN
12 P_STR_PK := P_STR_PK || '.1';
13 ELSE
14 P_STR_ID := P_STR_ID || '/' || I.ID;
15 P_STR_PK := P_STR_PK || '/' || CASE WHEN I.ID = I.FATHER_ID THEN '.1' END || I.PK;
16 P_GET_CHILD_STR(I.ID, P_STR_ID, P_STR_PK);
17 END IF;
18 END LOOP;
19 END;
20 BEGIN
21 FOR I IN (SELECT PK, ID, FATHER_ID FROM T_TREE WHERE PK = P_VALUE) LOOP
22 V_STR_ID := '/' || I.ID;
23 V_STR_PK := '/' || I.PK;
24 P_GET_CHILD_STR(I.ID, V_STR_ID, V_STR_PK);
25 END LOOP;
26 RETURN V_STR_PK;
27 END;
28 /

函数已创建。

SQL> SELECT F_FIND_CHILD(8) FROM DUAL;

F_FIND_CHILD(8)
--------------------------------------------------------------------------------
/8.1/1/2/5/4/6/7.1/3

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

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

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

已选择8行。

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

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

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