9i10g:10g树形查询特性CONNECT_BY_ISCYCLE的9i实现方式四

on 2008-7-31 in 数据库 | 0 Comment
　　CONNECT_BY_ISCYCLE功能的实现，需要将实现方式改为动态SQL方式，这样不但保证了语句的通用性，而且可以实现不同方向的星型查询。

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

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

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

     上一篇文章中基本实现了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);
　　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

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

Tags:  connect 9i10g

延伸阅读

最新评论

发表评论