oracle 多层with,Oracle11gr2新增递归WITH语句（三）

在11.2中，WITH语句得到进一步的增强，可以支持递归的调用。

这一篇简单介绍WITH语句的递归调用的CYCLE语句。

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行。