分类： Oracle
2008-04-03 22:47:21
在上一篇文章中，我说明了如何分类层次化查询中的输入项。但是，要使一个层次化查询得以运行，就不能使数据返回到自身。也就是说，一个母行不能把自己或它的一个子行作为自己的母行。如果发生这种情况，Oracle将重复不断地执行这个链接，永远无法到达一个确定的终点。
幸运的是，Oracle对这种情况进行测试，并用以下错误信息中止查询：
ORA-01436: CONNECT BY loop in user data
过去，我们很难发现一个大型数据集中的所有行彼此引用，因为查询在发现第一个循环时就已经终止。在Oracle 10g中，SELECT语句的两个新语法元素为你提供了解决这类数据错误的工具：NOCYCLE和CONNECT_BY_ISCYCLE。
CONNECT BY子句中增加的NOCYCLE参数指出，即使探查到一个循环，Oracle无论如何也要返回一个行。这为查明哪个行出错提供了极有价值的线索（不用担心——Oracle会停止评估循环，转向下一行。）。如果当前的行包含一个循环，CONNECT_BY_ISCYCLE伪列将返回1；否则将返回0。组合使用这两个元素，就能查明需要修复的内容。
是和上一篇文章中相同的层次化查询，我们根据HR样本方案执行它。
这次我们在数据中介绍另一个循环，将员工Kochhar（101）改为向Urman（112）报告，Urman向Greenberg（108）报告，Greenberg则向Kochhar报告，从而建立一个循环。
UPDATE employees
SET manager_id = 112
WHERE employee_id = 101;
COMMIT;
如果我们重新运行上述查询，将得到ORA-01436错误。探查我们介绍的错误。我们没有使用CONNECT BY PRIOR，而是使用了CONNECT BY NOCYCLE PRIOR。即使探查到一个循环，它仍然会继续进行查询。我们还在报告中增加了manager_id列和CONNECT_BY_ISCYCLE伪列以帮助解决这个问题。
输出结果在Urman行显示1。回溯循环链，我们就能发现出错的地方：Kochhar那一行。
column "Reports To" format a30
set pagesize 9999
SELECT LPAD(' ', 2*(LEVEL-1))||last_name "Reports To", employee_id
FROM employees
START WITH employee_id IN (101, 102)
CONNECT BY PRIOR employee_id = manager_id
/
Reports To EMPLOYEE_ID
------------------------------ -----------
De Haan 102
Hunold 103
Austin 105
Ernst 104
Lorentz 107
Pataballa 106
Kochhar 101
Baer 204
Greenberg 108
Chen 110
Faviet 109
Popp 113
Sciarra 111
Urman 112
Kochhar 101
ERROR:
ORA-01436: CONNECT BY loop in user data
column "Reports To" format a30
set pagesize 9999
SELECT LPAD(' ', 2*(LEVEL-1))||last_name "Reports To", employee_id,
CONNECT_BY_ISCYCLE "Cycle"
FROM employees
START WITH employee_id IN (101, 102)
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name
/
Reports To EMPLOYEE_ID MANAGER_ID Cycle
------------------------------ ----------- ---------- ----------
De Haan 102 100 0
Hunold 103 102 0
Austin 105 103 0
Ernst 104 103 0
Lorentz 107 103 0
Pataballa 106 103 0
Kochhar 101 112 0
Baer 204 101 0
Greenberg 108 101 0
Chen 110 108 0
Faviet 109 108 0
Popp 113 108 0
Sciarra 111 108 0
Urman 112 108 1
Higgins 205 101 0
Gietz 206 205 0
Mavris 203 101 0
Whalen 200 101 0
18 rows selected.
