Oracle 层次化查询

段干140

　　
　　
　　

create table employees(
employee_id integer constraint employees_pk primary key,
manager_id integer constraint employees_fk_employees references employees(employee_id),
first_name varchar2(10) not null,
last_name varchar2(10) not null,
title varchar2(20),
salary number(6,0)
);
insert into employees(employee_id,first_name,last_name,title,salary) values(1,'James','Smith','CEO',800000);
insert into employees values(2,1,'Ron','Johnson','Sales Manager',600000);
insert into employees values(3,2,'Fred','Hobbs','Sales Person',200000);
insert into employees values(4,1,'Susan','Jones','Support Manager',500000);
insert into employees values(5,2,'Rob','Green','Sales Person',40000);
insert into employees values(6,4,'Jane','Brown','Support Person',45000);
insert into employees values(7,4,'Jone','Grey','Support Person',30000);
insert into employees values(8,7,'Jean','Blue','Support Person',29000);
insert into employees values(9,6,'Henry','Heyson','Support Person',30000);
insert into employees values(10,1,'Kenvin','Black','Ops Manager',100000);
insert into employees values(11,10,'Keith','Long','Ops Person',50000);
insert into employees values(12,10,'Frank','Howard','Ops Person',50000);
insert into employees values(13,10,'Doreen','Penn','Ops Person',47000);

　　

员工的关系式一个树形结构，要提取不同员工之间的关系可以使用select 语句的connect by 和start with 子句执行层次化查询。

语法：
  select [level],column,...
  from table
  [where where_condition]
  start with start_condition connect by prior prior_condition
 
  level 是一个伪列，代表树的第几层。根节点的值是1，根节点的子节点是2，以此类推。
  start_condition 定义了层次化查询的起点。当编写层次化查询时必须指定start with 子句。例如：将start_condition 定义为 employee_id=1,表示从员工Id=1开始查询。
  prior_condition 定义了父行和子行之间的关系。当编写层次化查询时必须指定prior_codition子句。例如将prior_condition 定义为 employee_id=manager_id,表示父节点的employee_id和子节点的manager_id之间存在相等关系。

select employee_id,manager_id,first_name,last_name
from employees
start with employee_id=1 connect by prior employee_id=manager_id;
　　查询的结果是：

1JamesSmith
21RonJohnson
32FredHobbs
52RobGreen
41SusanJones
64JaneBrown
96HenryHeyson
74JoneGrey
87JeanBlue
101KenvinBlack
1110KeithLong
1210FrankHoward
1310DoreenPenn
　　
　　
　　使用LEVEL 展示员工所处的层次：

select level,employee_id,manager_id,first_name,last_name from employees start with employee_id=1 connect by prior employee_id=manager_id order by level;
　　
　　查询结果：

11JamesSmith
2101KenvinBlack
221RonJohnson
241SusanJones
31310DoreenPenn
374JoneGrey
31110KeithLong
352RobGreen
332FredHobbs
31210FrankHoward
364JaneBrown
487JeanBlue
496HenryHeyson
　　格式化层次查询结果：

select level,lpad('-',2*(level-1))||first_name||' '||last_name as employee from employees start with employee_id=1 connect by prior employee_id=manager_id;
　　结果：

1James Smith
2 -Ron Johnson
3   -Fred Hobbs
3   -Rob Green
2 -Susan Jones
3   -Jane Brown
4     -Henry Heyson
3   -Jone Grey
4     -Jean Blue
2 -Kenvin Black
3   -Keith Long
3   -Frank Howard
3   -Doreen Penn