oracle的sql用with递归分析（实例讲解）
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行。