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,' ')
