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行。
阅读(2262) | 评论(0) | 转发(0) |