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

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.