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