在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,@# @#)

原文转自：http://www.ltesting.net