Oracle PL/SQL之递归查询 - CONNECT BY PRIOR

Oracle PL/SQL之递归查询 - CONNECT BY PRIOR

^{Oracle PL/SQL之递归查询 - CONNECT BY PRIOR：}

1. duzz$scott@orcl>SELECT employee_id, last_name, job_id, manager_id  
2.   2  FROM employees  
3.   3  START WITH employee_id = 101  
4.   4  CONNECT BY PRIOR employee_id=manager_id;  
5. （PRIOR：先前的，优先的；按照先前的employee_id等于本行的manager_id进行递归查询，从上至下）  
6. EMPLOYEE_ID LAST_NAME                 JOB_ID                 MANAGER_ID  
7. -----------     -------------------------        ------------------------------          ----------   
8.         101 Kochhar                   AD_VP                         100  
9.         108 Greenberg                 FI_MGR                        101  
10.         109 Faviet                    FI_ACCOUNT                    108  
11.         110 Chen                      FI_ACCOUNT                    108  
12.         111 Sciarra                   FI_ACCOUNT                    108  
13.         112 Urman                     FI_ACCOUNT                    108  
14.         113 Popp                      FI_ACCOUNT                    108  
15.         200 Whalen                    AD_ASST                       101  
16.         203 Mavris                    HR_REP                        101  
17.         204 Baer                      PR_REP                        101  
18.         205 Higgins                   AC_MGR                        101  
19.         206 Gietz                     AC_ACCOUNT                    205  
20.   
21. 12 rows selected.  
22.   
23. Elapsed: 00:00:00.03  
24. duzz$scott@orcl >SELECT employee_id, last_name, job_id, manager_id  
25.   2    FROM employees  
26.   3    START WITH employee_id = 101  
27.   4    CONNECT BY employee_id= PRIOR manager_id;  
28. （把先前的manager_id(employee_id = 101)作为当前的employee_id，从下至上）  
29. EMPLOYEE_ID LAST_NAME                 JOB_ID                 MANAGER_ID  
30. -----------     -------------------------         ------------------------------         ----------   
31.         101 Kochhar                   AD_VP                         100  
32.         100 King                      AD_PRES  
33.   
34. Elapsed: 00:00:00.00