在上一篇文章中，我说明了如何分类层次化查询中的输入项。但是，要使一个层次化查询得以运行，就不能使数据返回到自身。也就是说，一个母行不能把自己或它的一个子行作为自己的母行。如果发生这种情况，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年经验的计算机专业人士，从事过技术培训师、顾问与数据库管理员等职。

责任编辑：德东

查看本文国际来源

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.