用Oracle 10g新层次伪列探查层次循环

在上一篇文章中，我说明了如何分类层次化查询中的输入项。但是，要使一个层次化查询得以运行，就不能使数据返回到自身。也就是说，一个母行不能把自己或它的一个子行作为自己的母行。如果发生这种情况，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。组合使用这两个元素，就能查明需要修复的内容。

列表A是和上一篇文章中相同的层次化查询，我们根据HR样本方案执行它。

这次我们在数据中介绍另一个循环，将员工Kochhar（101）改为向Urman（112）报告，Urman向Greenberg（108）报告，Greenberg则向Kochhar报告，从而建立一个循环。

UPDATE employees

SET manager_id = 112

WHERE employee_id = 101;

COMMIT;

如果我们重新运行上述查询，将得到ORA-01436错误。列表B探查我们介绍的错误。我们没有使用CONNECT BY PRIOR，而是使用了CONNECT BY NOCYCLE PRIOR。即使探查到一个循环，它仍然会继续进行查询。我们还在报告中增加了manager_id列和CONNECT_BY_ISCYCLE伪列以帮助解决这个问题。

输出结果在Urman行显示1。回溯循环链，我们就能发现出错的地方：Kochhar那一行。

Bob Watkins(OCP、MCDBA、MCSE、MCT)是一位有25年经验的计算机专业人士，从事过技术培训师、顾问与数据库管理员等职。

责任编辑：德东

查看本文国际来源