分层查询(遍历树)

1、建表
SQL> create table employees (employee_id number,last_name varchar2(20),JOB_ID varchar2(20),MANAGER_ID number);
表已创建。

2、表结构
SQL> desc employees;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ------------

 EMPLOYEE_ID                                        NUMBER
 LAST_NAME                                          VARCHAR2(20)
 JOB_ID                                             VARCHAR2(20)
 MANAGER_ID                                         NUMBER

3、插入一些数据
insert into employees values (100,'King','AD_PRES',null);
insert into employees values (101,'Kochhar','AD_VP',100);
insert into employees values (102,'De Haan','AD_VP',100);
insert into employees values (103,'Hunold','IT_PROG',102);
insert into employees values (104,'Emst','IT_PROG',103);
insert into employees values (107,'Lorentz','IT_PROG',103);
insert into employees values (124,'Mourgos','ST_MAN',100);
insert into employees values (141,'Rajs','ST_CLERK',124);
insert into employees values (142,'Davies','ST_CLERK',124);
insert into employees values (143,'Matos','ST_CLERK',124);
insert into employees values (144,'Vargas','ST_CLERK',124);
insert into employees values (149,'Zlotkey','SA_MAN',100);
insert into employees values (174,'Abel','SA_REP',149);
insert into employees values (176,'Taylor','SA_REP',149);
insert into employees values (178,'Gant','SA_REP',149);
insert into employees values (200,'Whalen','AD_ASST',101);
insert into employees values (201,'Hartstein','MK_MAN',100);
insert into employees values (202,'Fay','MK_REP',201);
insert into employees values (205,'Higgins','AC_MGR',101);
insert into employees values (206,'Gietz','AC_ACCOUNT',205);

4、查询的例子
从某点从下向上
SQL> SELECT employee_id, last_name, job_id, manager_id
  2  FROM   employees
  3  START  WITH  employee_id = 101
  4  CONNECT BY PRIOR manager_id = employee_id;

EMPLOYEE_ID LAST_NAME            JOB_ID               MANAGER_ID
----------- -------------------- -------------------- ----------
        101 Kochhar              AD_VP                       100
        100 King                 AD_PRES

自上而下
SQL> SELECT  last_name||' reports to '||
  2  PRIOR   last_name "Walk Top Down"
  3  FROM    employees
  4  START   WITH last_name = 'King'
  5  CONNECT BY PRIOR employee_id = manager_id ;

Walk Top Down
----------------------------------------------------
King reports to
Kochhar reports to King
Whalen reports to Kochhar
Higgins reports to Kochhar
Gietz reports to Higgins
De Haan reports to King
Hunold reports to De Haan
Emst reports to Hunold
Lorentz reports to Hunold
Mourgos reports to King
Rajs reports to Mourgos

Walk Top Down
----------------------------------------------------
Davies reports to Mourgos
Matos reports to Mourgos
Vargas reports to Mourgos
Zlotkey reports to King
Abel reports to Zlotkey
Taylor reports to Zlotkey
Gant reports to Zlotkey
Hartstein reports to King
Fay reports to Hartstein

已选择20行。

按层读出
SQL> COLUMN org_chart FORMAT A12
SQL> SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_')
  2  AS org_chart
  3  FROM employees
  4  START WITH last_name='King'
  5  CONNECT BY PRIOR employee_id=manager_id;

ORG_CHART
------------
King
__Kochhar
____Whalen
____Higgins
______Gietz
__De Haan
____Hunold
______Emst
______Lorentz

ORG_CHART
------------
__Mourgos
____Rajs
____Davies
____Matos
____Vargas
__Zlotkey
____Abel
____Taylor
____Gant
__Hartstein
____Fay

已选择20行。