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

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

REF:

http://www.9flex.com/archives/295/

http://www.cnblogs.com/ZHF/archive/2008/09/10/1288101.html