有朋友向我反映，在讀完下面兩篇文章後對層次查詢產生了濃厚的興趣，問到還有哪些偽列可以使用.透過這個小文兒再給大家介紹一個層次查詢中可能會用到的一個偽列——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--&gtB--&gtD--&gtA”。

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

【層次查詢】Hierarchical Queries之CONNECT_BY_ISCYCLE偽列

相關文章