10g樹形查詢新特性CONNECT_BY_ISCYCLE的9i實現方式（四）
在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);
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偽列的功能。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-413532/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 10g樹形查詢新特性CONNECT_BY_ISCYCLE的9i實現方式（三）
- 10g樹形查詢新特性CONNECT_BY_ISCYCLE的9i實現方式（二）
- 10g樹形查詢新特性CONNECT_BY_ISCYCLE的9i實現方式（一）
- 10g樹形查詢新特性CONNECT_BY_ROOT的9i實現方式
- 10g樹形查詢新特性CONNECT_BY_ISLEAF的9i實現方式
- LightDB 22.4 新特性之相容Oracle樹形查詢Oracle
- 樹形查詢
- 10g新特性——閃回版本查詢
- oracle樹形查詢Oracle
- 利用樹形查詢實現部分分析函式功能函式
- Oracle Database 10g新特性-閃回版本查詢OracleDatabase
- Elasticsearch 查詢in 和 not in 的實現方式Elasticsearch
- oracle樹形選單查詢Oracle
- Connect by實現樹查詢的妙用
- 樹形結構的儲存與查詢
- Oracle 樹形結構查詢的特殊用法Oracle
- 遞迴樹形查詢所有分類遞迴
- Mysql 實現樹狀遞迴查詢MySql遞迴
- NKMySQL 查詢樹結構方式gllMySql
- 二叉查詢樹的實現——C++C++
- 利用CGI方式實現Web查詢 (轉)Web
- java實現Hbase中的查詢（一）Filter方式JavaFilter
- 樹形查詢也瘋狂&優化措施優化
- Java實現遞迴查詢樹結構Java遞迴
- 二叉查詢樹概念及實現
- C#實現二叉查詢樹C#
- 聊聊mysql的樹形結構儲存及查詢MySql
- MySQL 實現樹形的遍歷MySql
- 轉:C++實現的變種二分查詢法(折半查詢)--二叉查詢樹C++
- JavaScript實現簡單二叉查詢樹JavaScript
- JVM系列(四)：java方法的查詢過程實現JVMJava
- 樹形結構的選單表設計與查詢
- 使用 Redis 解決“樹”形資料的複雜查詢Redis
- MySQL實現樹狀所有子節點查詢的方法MySql
- 實戰10g新特性之RMAN TSPITR特性
- 10g 新特性
- SSH整合實現分頁查詢(兩種方式)
- 那些我所不知道前端的實現方式與新特性前端