Oracle11gr2新增遞迴WITH語句（三）
在11.2中，WITH語句得到進一步的增強，可以支援遞迴的呼叫。
這一篇簡單介紹WITH語句的遞迴呼叫的CYCLE語句。
Oracle11gr2新增遞迴WITH語句（一）：http://yangtingkun.itpub.net/post/468/496038
Oracle11gr2新增遞迴WITH語句（二）：http://yangtingkun.itpub.net/post/468/496164
Oracle提供的WITH遞迴併不僅僅是樹形查詢的一個重複，事實上利用遞迴WITH語句可以提供更加靈活的樹形查詢功能。
下面就介紹CYCLE語句，這個語句不僅可以實現樹形查詢的CONNECT_BY_ISCYCLE和CONNEC BY NOCYCLE的功能，還提供了更加強大的功能。
繼續上一篇文章中的例子。
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> CREATE TABLE T_TREE (
2 ID NUMBER PRIMARY KEY,
3 FATHER_ID NUMBER,
4 NAME VARCHAR2(30));
表已建立。
SQL> INSERT INTO T_TREE VALUES (1, 0, 'A');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (2, 1, 'BC');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (3, 1, 'DE');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (4, 1, 'FG');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (5, 2, 'HIJ');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (6, 4, 'KLM');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (7, 6, 'NOPQ');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (8, 5, 'RSTU');
已建立 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT * FROM T_TREE;
ID FATHER_ID NAME
---------- ---------- ------------------------------
1 0 A
2 1 BC
3 1 DE
4 1 FG
5 2 HIJ
6 4 KLM
7 6 NOPQ
8 5 RSTU
已選擇8行。
修改記錄1使其的FATHER_ID指向自己，然後執行遞迴WITH查詢：
SQL> UPDATE T_TREE SET FATHER_ID = 1 WHERE ID = 1;
已更新 1 行。
SQL> SELECT * FROM T_TREE;
ID FATHER_ID NAME
---------- ---------- ------------------------------
1 1 A
2 1 BC
3 1 DE
4 1 FG
5 2 HIJ
6 4 KLM
7 6 NOPQ
8 5 RSTU
已選擇8行。
SQL> WITH A (ID, FATHER_ID, NAME) AS
2 (SELECT ID, FATHER_ID, NAME
3 FROM T_TREE
4 WHERE ID = 1
5 UNION ALL
6 SELECT B.ID, B.FATHER_ID, B.NAME
7 FROM A, T_TREE B
8 WHERE A.ID = B.FATHER_ID)
9 SELECT * FROM A;
ERROR:
ORA-32044: 執行遞迴 WITH 查詢時檢測到迴圈
未選定行
沒有使用CYCLE語句的遞迴WITH查詢報錯，下面利用CYCLE避免迴圈導致的錯誤：
SQL> WITH A (ID, FATHER_ID, NAME) AS
2 (SELECT ID, FATHER_ID, NAME
3 FROM T_TREE
4 WHERE ID = 1
5 UNION ALL
6 SELECT B.ID, B.FATHER_ID, B.NAME
7 FROM A, T_TREE B
8 WHERE A.ID = B.FATHER_ID)
9 CYCLE ID SET DUP_ID TO 'Y' DEFAULT 'N'
10 SELECT * FROM A;
ID FATHER_ID NAME D
---------- ---------- ------------------------------ -
1 1 A N
1 1 A Y
2 1 BC N
3 1 DE N
4 1 FG N
5 2 HIJ N
6 4 KLM N
7 6 NOPQ N
8 5 RSTU N
已選擇9行。
需要注意的是，這個結果和使用CONNECT BY NOCYCLE得到的結果並不一致，ID為1的記錄會出現兩次：
SQL> SELECT A.*, CONNECT_BY_ISCYCLE FLAG
2 FROM T_TREE A
3 START WITH ID = 1
4 CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;
ID FATHER_ID NAME FLAG
---------- ---------- ------------------------------ ----------
1 1 A 1
2 1 BC 0
5 2 HIJ 0
8 5 RSTU 0
3 1 DE 0
4 1 FG 0
6 4 KLM 0
7 6 NOPQ 0
已選擇8行。
不過這並不會造成任何的影響，透過過濾結果集，可以很容易的獲得NOCYCLE得到的結果。
除了ID和FATHER_ID相同的情況，如果樹形查詢構成一個環路，同樣可以用CYCLE語句解決：
SQL> UPDATE T_TREE SET FATHER_ID = 7 WHERE ID = 1;
已更新 1 行。
SQL> WITH A (ID, FATHER_ID, NAME) AS
2 (SELECT ID, FATHER_ID, NAME
3 FROM T_TREE
4 WHERE ID = 1
5 UNION ALL
6 SELECT B.ID, B.FATHER_ID, B.NAME
7 FROM A, T_TREE B
8 WHERE A.ID = B.FATHER_ID)
9 CYCLE ID SET DUP_ID TO 'Y' DEFAULT 'N'
10 SELECT * FROM A;
ID FATHER_ID NAME D
---------- ---------- ------------------------------ -
1 7 A N
2 1 BC N
3 1 DE N
4 1 FG N
5 2 HIJ N
6 4 KLM N
7 6 NOPQ N
8 5 RSTU N
1 7 A Y
已選擇9行。
而且CYCLE語句不僅僅可以判斷當前CONNECT BY是否出現迴圈，還可以判斷同一個分支上的任意一列是否出現過重複資料：
SQL> UPDATE T_TREE SET FATHER_ID = 0 WHERE ID = 1;
已更新 1 行。
SQL> INSERT INTO T_TREE VALUES (9, 2 ,'BC');
已建立 1 行。
SQL> SELECT * FROM T_TREE;
ID FATHER_ID NAME
---------- ---------- ------------------------------
9 2 BC
1 0 A
2 1 BC
3 1 DE
4 1 FG
5 2 HIJ
6 4 KLM
7 6 NOPQ
8 5 RSTU
已選擇9行。
SQL> WITH A (ID, FATHER_ID, NAME) AS
2 (SELECT ID, FATHER_ID, NAME
3 FROM T_TREE
4 WHERE ID = 1
5 UNION ALL
6 SELECT B.ID, B.FATHER_ID, B.NAME
7 FROM A, T_TREE B
8 WHERE A.ID = B.FATHER_ID)
9 CYCLE NAME SET DUP_NAME TO 'Y' DEFAULT 'N'
10 SELECT * FROM A;
ID FATHER_ID NAME D
---------- ---------- ------------------------------ -
1 0 A N
2 1 BC N
3 1 DE N
4 1 FG N
9 2 BC Y
5 2 HIJ N
6 4 KLM N
7 6 NOPQ N
8 5 RSTU N
已選擇9行。
SQL> UPDATE T_TREE SET FATHER_ID = 5 WHERE ID = 9;
已更新 1 行。
SQL> WITH A (ID, FATHER_ID, NAME) AS
2 (SELECT ID, FATHER_ID, NAME
3 FROM T_TREE
4 WHERE ID = 1
5 UNION ALL
6 SELECT B.ID, B.FATHER_ID, B.NAME
7 FROM A, T_TREE B
8 WHERE A.ID = B.FATHER_ID)
9 CYCLE NAME SET DUP_NAME TO 'Y' DEFAULT 'N'
10 SELECT * FROM A;
ID FATHER_ID NAME D
---------- ---------- ------------------------------ -
1 0 A N
2 1 BC N
3 1 DE N
4 1 FG N
5 2 HIJ N
6 4 KLM N
9 5 BC Y
7 6 NOPQ N
8 5 RSTU N
已選擇9行。
這裡判斷的是NAME列，只要在樹的同一個樹杈上NAME列出現重複，就會導致CYCLE的標識列為Y。
如果出現重複的列處於兩個不同的樹杈上，則不會導致CYCLE為Y：
SQL> UPDATE T_TREE SET NAME = 'DE' WHERE ID = 9;
已更新 1 行。
SQL> WITH A (ID, FATHER_ID, NAME) AS
2 (SELECT ID, FATHER_ID, NAME
3 FROM T_TREE
4 WHERE ID = 1
5 UNION ALL
6 SELECT B.ID, B.FATHER_ID, B.NAME
7 FROM A, T_TREE B
8 WHERE A.ID = B.FATHER_ID)
9 CYCLE NAME SET DUP_NAME TO 'Y' DEFAULT 'N'
10 SELECT * FROM A;
ID FATHER_ID NAME D
---------- ---------- ------------------------------ -
1 0 A N
2 1 BC N
3 1 DE N
4 1 FG N
5 2 HIJ N
6 4 KLM N
9 5 DE N
7 6 NOPQ N
8 5 RSTU N
已選擇9行。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-625814/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle11gr2新增遞迴WITH語句（二）Oracle遞迴
- Oracle11gr2新增遞迴WITH語句（一）Oracle遞迴
- MySQL 三種新增語句MySql
- Oracle11gr2物化檢視日誌新增PURGE語句Oracle
- Oracle11gr2物化檢視日誌新增COMMIT SCN語句OracleMIT
- Python學習筆記（三）——條件語句、迴圈語句Python筆記
- Matlab的if語句switch語句for迴圈while迴圈語句練習MatlabWhile
- Oracle11gr2新增版本功能（三）Oracle
- c語言_遞迴C語言遞迴
- PLSQL Language Referenc-PL/SQL控制語句-迴圈語句-基本迴圈(EXIT語句)SQL
- JavaScript 流程控制語句詳解：if語句、switch語句、while迴圈、for迴圈等JavaScriptWhile
- TypeScript 迴圈語句TypeScript
- JavaScript for 迴圈語句JavaScript
- MySQL迴圈語句MySql
- Oracle迴圈語句Oracle
- PLSQL Language Referenc-PL/SQL控制語句-迴圈語句-FOR迴圈SQL
- 實驗三：分別用for、while和do-while迴圈語句以及遞迴方法計算n！，並輸出算式While遞迴
- 前端 JavaScript 中的三種 for 迴圈語句總結前端JavaScript
- PLSQL Language Referenc-PL/SQL控制語句-迴圈語句-WHILE迴圈SQLWhile
- JavaScript跳出for迴圈語句JavaScript
- java 迴圈語句(轉)Java
- PLSQL Language Referenc-PL/SQL控制語句-迴圈語句-FOR迴圈-FOR迴圈中的索引SQL索引
- Oracle11gr2的PLSQL優化NULL語句OracleSQL優化Null
- Oracle11gr2 審計語句增強（二）Oracle
- Oracle11gr2 審計語句增強（一）Oracle
- Python-條件語句和迴圈語句Python
- Oracle儲存過程乾貨（三）：PLSQL迴圈語句Oracle儲存過程SQL
- MySQL 四種新增語句MySql
- c語言中，while(1)語句使用break語句跳出迴圈C語言While
- 三、遞迴樹分析法遞迴
- Oracle11gr2新增ALTER DATABASE LINK語法OracleDatabase
- 前端筆記之JavaScript（三）關於條件判斷語句、迴圈語句那點事前端筆記JavaScript
- PLSQL Language Referenc-PL/SQL控制語句-迴圈語句-FOR迴圈-下限和上限SQL
- Java基礎 迴圈語句 for while do.....while語句JavaWhile
- Java 可以採用什麼語句跳出迴圈語句Java
- C語言goto語句以及用goto語句構成迴圈C語言Go
- Python的迴圈語句Python
- 流程控制、 迴圈語句