10g树形查询特性CONNECT_BY_ISCYCLE的9i实现方式二
作者: 杨廷琨, 出处:blog, 责任编辑: 王晓晨,
2008-07-25 00:00
采用将固定字符分隔的字符串转化为表的形式的方法，就可以将获取的字符串转化为表的形式。
现在初步实现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 |
- 本文关键词：
- Oracle 10g
- 查询
