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

最新推荐文章于 2023-10-11 15:47:20 发布

ckawt40802

最新推荐文章于 2023-10-11 15:47:20 发布

阅读量99

点赞数

文章标签：数据库

在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

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

上一篇文章中基本实现了CONNECT_BY_ISCYCLE的功能。注意前面几篇文章都是提到了“基本实现”，这是由于当前访问的表采用硬编码方式。这种方式能解决上面的问题，但是这种方式没有通用性，需要针对不同的查询，手工编写不同的函数来解决问题。

因此需要将实现方式改为动态SQL方式，这样不但保证了语句的通用性，而且可以实现不同方向的星型查询。

先构造测试的例子：

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的形式来实现前一篇SQL的功能：

SQL> CREATE OR REPLACE FUNCTION F_FIND_CHILD
2 (
3   P_VALUE VARCHAR2,
4   P_FATHER_COL VARCHAR2,
5   P_CHILD_COL VARCHAR2,
6   P_PK VARCHAR2,
7   P_TABLE_NAME VARCHAR2
8 ) RETURN VARCHAR2 AS
9   V_STR_ID VARCHAR2(32767);
10   V_STR_PK VARCHAR2(32767);
11
12   PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR2,
13    P_STR_ID IN OUT VARCHAR2,
14    P_STR_PK IN OUT VARCHAR2) AS
15    TYPE T_TAB_STR IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
16    V_TAB_PK T_TAB_STR;
17    V_TAB_CHILD T_TAB_STR;
18    V_TAB_FATHER T_TAB_STR;
19   BEGIN
20    EXECUTE IMMEDIATE 'SELECT ' || P_PK || ', ' || P_CHILD_COL || ', ' || P_FATHER_COL
21     || ' FROM ' || P_TABLE_NAME || ' WHERE ' || P_FATHER_COL || ' = ''' || P_FATHER
22     || ''' ORDER BY DECODE(' || P_FATHER_COL || ', ' || P_CHILD_COL || ', 0, 1)'
23     BULK COLLECT INTO V_TAB_PK, V_TAB_CHILD, V_TAB_FATHER;
24    FOR I IN 1..V_TAB_PK.COUNT LOOP
25     IF INSTR(P_STR_ID || '/', '/' || V_TAB_CHILD(I) || '/') > 0 THEN
26      P_STR_PK := P_STR_PK || '.1';
27     ELSE
28      P_STR_ID := P_STR_ID || '/' || V_TAB_CHILD(I);
29      P_STR_PK := P_STR_PK || '/'
30       || CASE WHEN V_TAB_CHILD(I) = V_TAB_FATHER(I) THEN '.1' END || V_TAB_PK(I);
31      P_GET_CHILD_STR(V_TAB_CHILD(I), P_STR_ID, P_STR_PK);
32     END IF;
33    END LOOP;
34   END;
35 BEGIN
36   EXECUTE IMMEDIATE 'SELECT ''/'' || ' || P_PK || ', ''/'' || ' || P_CHILD_COL
37    || ' FROM ' || P_TABLE_NAME || ' WHERE ' || P_PK || ' = :VALUE'
38    INTO V_STR_PK, V_STR_ID USING P_VALUE;
39    P_GET_CHILD_STR(LTRIM(V_STR_ID, '/'), V_STR_ID, V_STR_PK);
40   RETURN V_STR_PK;
41 END;
42 /

函数已创建。

下面检查一下这个函数是否等价于Oracle提供的CONNECT BY NOCYCLE模式下的CONNECT_BY_ISCYCLE：

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

        PK         ID FATHER_ID NAME                               CYCLED
---------- ---------- ---------- ------------------------------ ----------
         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

已选择7行。

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

        PK         ID FATHER_ID NAME                               CYCLED
---------- ---------- ---------- ------------------------------ ----------
         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

已选择7行。

采用了这种方式，对于叶节点到根节点的查询同样是支持的：

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

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

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

至此，终于实现了9i方式下CONNECT BY NOCYCLE查询方式下的CONNECT_BY_ISCYCLE伪列的功能。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-413532/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-413532/

ckawt40802

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
10g树形查询新特性CONNECT_BY_ISCYCLE的9i实现方式（四）

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

扫一扫