|
在g中Oracle提供了新的伪列CONNECT_BY_ISCYCLE通过这个伪列可以判断是否在树形查询的过程中构成了循环这个伪列只是在CONNECT BY NOCYCLE方式下有效 这一篇描述一下解决问题的思路 CONNECT_BY_ISCYCLE的实现和前面两篇文章中CONNECT_BY_ROOT和CONNECT_BY_ISLEAF的实现完全不同 因为要实现CONNECT_BY_ISCYCLE就必须先实现CONNECT BY NOCYCLE而在i中是没有方法实现这个功能的 也就是说首先要实现自己的树形查询的功能而仅这第一点就是一个异常困难的问题何况后面还要实现NOCYCLE最后再加上一个ISCYCLE的判断 所以总的来说这个功能的实现比前面两个功能要复杂得多由于树形查询的LEVEL是不固定的所以采用链接的方式实现基本上是不现实的换句话说用纯SQL的方式来实现树形查询的功能基本上不可行而为了解决这个功能只能通过PL/SQL配合SQL来实现 仍然是首先构造一个例子 SQL> CREATE TABLE T_TREE (ID NUMBER FATHER_ID NUMBER NAME VARCHAR()); 表已创建 SQL> INSERT INTO T_TREE VALUES ( A); 已创建 行 SQL> INSERT INTO T_TREE VALUES ( BC); 已创建 行 SQL> INSERT INTO T_TREE VALUES ( DE); 已创建 行 SQL> INSERT INTO T_TREE VALUES ( FG); 已创建 行 SQL> INSERT INTO T_TREE VALUES ( HIJ); 已创建 行 SQL> INSERT INTO T_TREE VALUES ( KLM); 已创建 行 SQL> INSERT INTO T_TREE VALUES ( NOPQ); 已创建 行 SQL> INSERT INTO T_TREE VALUES ( ROOT); 已创建 行 SQL> INSERT INTO T_TREE VALUES ( FG); 已创建 行 SQL> COMMIT; 提交完成 SQL> SELECT * FROM T_TREE; ID FATHER_ID NAME A BC DE FG HIJ KLM NOPQ ROOT FG 已选择行 上面构造了两种树形查询循环的情况一种是当前记录的自循环另一种是树形查询的某个子节点是当前节点的祖先节点从而构成了循环在这个例子中记录ID为和ID为且FATHER_ID等于的两条记录分别构成了上述的两种循环的情况 下面就来看看CONNECT_BY_ISCYCLE和CONNECT BY NOCYCLE的功能 SQL> SELECT * FROM T_TREE START WITH ID = CONNECT BY PRIOR ID = FATHER_ID; ERROR: ORA: 用户数据中的 CONNECT BY 循环 未选定行 SQL> SELECT * FROM T_TREE START WITH ID = CONNECT BY PRIOR ID = FATHER_ID; ERROR: ORA: 用户数据中的 CONNECT BY 循环 未选定行 这就是不使用CONNECT BY NOCYCLE的情况查询会报错指出树形查询中出现循环在g中可以使用CONNECT BY NOCYCLE的方式来避免错误的产生 SQL> SELECT * FROM T_TREE START WITH ID = CONNECT BY NOCYCLE PRIOR ID = FATHER_ID; ID FATHER_ID NAME ROOT A BC HIJ DE FG KLM NOPQ 已选择行 使用CONNECT BY NOCYCLEOracle自动避免循环的产生将不产生循环的数据查询出来下面看看CONNECT_BY_ISCYCLE的功能 SQL> SELECT ID FATHER_ID NAME CONNECT_BY_ISCYCLE CYCLED FROM T_TREE START WITH ID = CONNECT BY NOCYCLE PRIOR ID = FATHER_ID; ID FATHER_ID NAME CYCLED ROOT A BC HIJ DE FG KLM NOPQ 已选择行 可以看到CONNECT_BY_ISCYCLE伪列指出循环在树形查询中发生的位置 为了实现CONNECT_BY_ISCYCLE就必须先实现CONNECT BY NOCYCLE方式而这在i中是没有现成的办法的所以这里尝试使用PL/SQL来自己实现树形查询的功能 SQL> CREATE OR REPLACE FUNCTION F_FIND_CHILD(P_VALUE VARCHAR) RETURN VARCHAR AS V_STR VARCHAR() := / || P_VALUE;
PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR P_STR IN OUT VARCHAR) AS BEGIN FOR I IN (SELECT ID FROM T_TREE WHERE FATHER_ID = P_FATHER AND FATHER_ID != ID) LOOP IF INSTR(P_STR || / / || IID || /) = THEN P_STR := P_STR || / || IID; P_GET_CHILD_STR(IID P_STR); END IF; END LOOP; END; BEGIN P_GET_CHILD_STR(P_VALUE V_STR); RETURN V_STR; END; / 函数已创建 构造一个函数在函数中递归调用过程来实现树形查询的功能 下面看看调用这个函数的结果 SQL> SELECT F_FIND_CHILD() FROM DUAL; F_FIND_CHILD()
//////// SQL> SELECT F_FIND_CHILD() FROM DUAL; F_FIND_CHILD()
// SQL> SELECT F_FIND_CHILD() FROM DUAL; F_FIND_CHILD()
/// 虽然目前存在的问题还有很多但是已经基本上实现了一个最简单的NOCYCLE的SYS_CONNECT_BY_PATH的功能 有了这个函数作为基础就可以逐步的实现最终的目标了 |
