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;
查询的结果是:
1 James Smith 2 1 Ron Johnson 3 2 Fred Hobbs 5 2 Rob Green 4 1 Susan Jones 6 4 Jane Brown 9 6 Henry Heyson 7 4 Jone Grey 8 7 Jean Blue 10 1 Kenvin Black 11 10 Keith Long 12 10 Frank Howard 13 10 Doreen Penn
使用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;
查询结果:
1 1 James Smith 2 10 1 Kenvin Black 2 2 1 Ron Johnson 2 4 1 Susan Jones 3 13 10 Doreen Penn 3 7 4 Jone Grey 3 11 10 Keith Long 3 5 2 Rob Green 3 3 2 Fred Hobbs 3 12 10 Frank Howard 3 6 4 Jane Brown 4 8 7 Jean Blue 4 9 6 Henry Heyson
格式化层次查询结果:
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;
结果:
1 James 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