有朋友向我反映，在读完下面两篇文章后对层次查询产生了浓厚的兴趣，问到还有哪些伪列可以使用.通过这个小文儿再给大家介绍一个层次查询中可能会用到的一个伪列——CONNECT_BY_ISCYCLE。
【层次查询】Hierarchical Queries之LEVEL应用
http://space.itpub.net/519536/viewspace-623916
【层次查询】Hierarchical Queries之“树的遍历”
http://space.itpub.net/519536/viewspace-623809

CONNECT_BY_ISCYCLE伪列因何而生呢？
夸张点说，这个伪列功能是揪出那些“违反伦理道德”的人。
例如发现一个人既是另外一个人的孙子又是他的爸爸，这显然是不合伦理的，需要尽快发现并进行拨乱反正。
官方文档参考链接

OK，我们来看一下CONNECT_BY_ISCYCLE伪列的使用方法
1.再次回忆一下那棵令我们难以忘怀的关系“树”
      A
     / \
    B   C
   /   /
D   E
/ \
F   G

2.顺便再次回首一下刻画这棵树的T表数据
sec@ora10g> select * from t;

X                   Y          Z
---------- ---------- ----------
A                   1
B                   2          1
C                   3          1
D                   4          2
E                   5          3
F                   6          4
G                   7          4

7 rows selected.

3.上面的数据显示出的是一种和谐和自然，现在我们人为的打破这种和谐。
注意：“破坏”之前D只是A的孙子，“破坏”之后伦理关系将会错乱，D将既是A的孙子又是A的爸爸！
sec@ora10g> update t set z=4 where x = 'A';

1 row updated.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * From t;

X                   Y          Z
---------- ---------- ----------
A                   1          4
B                   2          1
C                   3          1
D                   4          2
E                   5          3
F                   6          4
G                   7          4

7 rows selected.

OK，此时数据已经表明，D确实已经是A的孙子又是A的爸爸，这是不允许的！
通过单纯的观察上面数据，不是很容易发现这个问题，此时CONNECT_BY_ISCYCLE伪列便派上用场了。

4.在存在错乱数据情况下，普通的层次查询是行不通的。
sec@ora10g> select x, y, z from t start with x = 'A' connect by prior y=z;
ERROR:
ORA-01436: CONNECT BY loop in user data

no rows selected

报错的本质原因：自关联的数据之间出现了一个“环”，这里的环便是“A-->B-->D-->A”。

5.使用CONNECT_BY_ISCYCLE伪列确定问题的出处
注意，使用CONNECT_BY_ISCYCLE伪列的时候需要结合NOCYCLE关键字一起使用。
sec@ora10g> select x, y, z, CONNECT_BY_ISCYCLE from t start with x = 'A' connect by NOCYCLE prior y=z;

X                   Y          Z CONNECT_BY_ISCYCLE
---------- ---------- ---------- ------------------
A                   1          4                  0
B                   2          1                  0
D                   4          2                  1
F                   6          4                  0
G                   7          4                  0
C                   3          1                  0
E                   5          3                  0

7 rows selected.

此时仅需要找出CONNECT_BY_ISCYCLE值是“1”的记录便可知道问题的出处。

6.小结
CONNECT_BY_ISCYCLE伪列在辅助查找自关联表中违反规则的数据方面有着不可替代的地位。
预告：下一篇文章将会谈到层次查询中除LEVEL和CONNECT_BY_ISCYCLE伪列外最后一个可用的伪列CONNECT_BY_ISLEAF，敬请期待。

Good luck.

secooler
09.12.30

-- The End --

ITPub博客

【层次查询】Hierarchical Queries之CONNECT_BY_ISCYCLE伪列

最新文章