Oracle PL/SQL之递归查询 - CONNECT BY PRIOR
原创
©著作权归作者所有：来自51CTO博客作者mb61c186c579369的原创作品，请联系作者获取转载授权，否则将追究法律责任
duzz$scott@orcl>SELECT employee_id, last_name, job_id, manager_id
2 FROM employees
3 START WITH employee_id = 101
4 CONNECT BY PRIOR employee_id=manager_id;
（PRIOR：先前的，优先的；按照先前的employee_id等于本行的manager_id进行递归查询，从上至下）
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ------------------------------ ----------
101 Kochhar AD_VP 100
108 Greenberg FI_MGR 101
109 Faviet FI_ACCOUNT 108
110 Chen FI_ACCOUNT 108
111 Sciarra FI_ACCOUNT 108
112 Urman FI_ACCOUNT 108
113 Popp FI_ACCOUNT 108
200 Whalen AD_ASST 101
203 Mavris HR_REP 101
204 Baer PR_REP 101
205 Higgins AC_MGR 101
206 Gietz AC_ACCOUNT 205
12 rows selected.
Elapsed: 00:00:00.03
duzz$scott@orcl >SELECT employee_id, last_name, job_id, manager_id
2 FROM employees
3 START WITH employee_id = 101
4 CONNECT BY employee_id= PRIOR manager_id;
（把先前的manager_id(employee_id = 101)作为当前的employee_id，从下至上）
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ------------------------------ ----------
101 Kochhar AD_VP 100
100 King AD_PRES
Elapsed: 00:00:00.00
