oracle 反向生成树,oracle生成树结构

create table Dept(

DepartNO varchar2(10),

DepartName varchar2(20),

TopNo varchar2(10));

insert into Dept values('001','董事会','0');

insert into Dept values('002','总裁办','001');

insert into Dept values('003','财务部','001');

insert into Dept values('004','市场部','002');

insert into Dept values('005','公关部','002');

insert into Dept values('006','销售部','002');

insert into Dept values('007','分销处','006');

insert into Dept values('008','业务拓展处','004');

insert into Dept values('009','销售科','007');

CREATE TABLE TempDept

(

TempdeptNo char(4), /*部门编号*/

TempdeptName varchar2(20), /*部门名称*/

UpperTempdeptNo char(4), /*上级部门编号*/

UpperTempdeptName varchar2(20), /*上级部门名称*/

TempdeptLevel number(4) /*部门所处的级别*/

);

select departname

from dept

connect by prior departno=topno

start with topno='0';

select a.departname top ,b.departname next

from dept a,dept b

where a.departno=b.topno;

select lpad(departno,level*2+length(departno),'\')

from dept

start with topno='0'

connect by prior departno=topno;

select departno,departname,level ||'\'|| topno

from dept

start with topno='0'

connect by prior departno=topno;

select lpad(departno,level*2+length(departno),' ')

from dept

start with topno='0'

connect by prior departno=topno;

select lpad(departno,level*3,' ')