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

初步实现CONNECT BY NOCYCLE的功能。

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

有了前面一篇的基础，可以获取到类似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