oracle中如何编写树级代码

　　
这是在2002年给一个制衣厂做系统的时候写的，请大家多批评指正！

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,' ')
from dept
start with topno='0'
connect by prior departno=topno;

create or replace function GetLevel(TempNo varchar2)
return varchar2
IS
Result varchar2(20);
cursor CurLevel(curTempNo varchar2)
is
select departNo
from dept
connect by prior departNo = TopNo
start with TopNo = curTempNo;
begin
open CurLevel;
for v_sor in CurLevel(TempNo) loop
   Result:=Result '/' v_sor.curTempNo;
end loop;
return(Result);
close CurLevel;
dbms_output.put_line('sUCcess');
end GetLevel;

REM************************************************************
REM**********   生成部门之间形成的树结构     ******************
REM********** 功能:输入部门后自动产生该部门 ******************
REM**********      及其子部门之间的树关系() ******************
REM********** 输入参数：部门编号ParaDeptId   ******************
REM************************************************************

CREATE OR REPLACE procedure getAllSubDeptId(ParaDeptId varchar2)
IS
   DeptNo1       varchar2(4);     /*存放部门编号*/
   UpperDeptNo1 varchar2(4);     /*存放该部门的上级部门编号*/
   UpperDeptNo2 varchar2(4);     /*存放查询同级上级部门编号*/
   UpperDeptNo3 varchar2(4);     /*存放查询的上级部门编号*/
      level         number;          /*部门所处级别*/
   DSql          varchar2(100);   /*动态SQL字符串*/
   CURSOR getDept IS
       select DepartNO,TopNo
       from dept
       where DepartNo !
= ParaDeptId;
BEGIN
--DSql :='create table tempdept(TempdeptNo,TempdeptName,UpperTempdeptNo,
  --UpperTempdeptName,TempdeptLevel)';
--EXECUTE IMMEDIATE DSql;
DSql :='TRUNCATE TABLE TempDept';
dbms_output.put_line(0);
EXECUTE IMMEDIATE DSql;
dbms_output.put_line(1);
insert into TempDept values(ParaDeptId,Null,Null,Null,0);
dbms_output.put_line(2);
open getDept;
fetch getDept into DeptNo1,UpperDeptNo1;
WHILE ( SQLCODE = 0 )
LOOP
      level := 1 ;
      UpperDeptNo3 := UpperDeptNo1;
      WHILE ( UpperDeptNo1 is NOT NULL )
      loop
         if (UpperDeptNo1 = ParaDeptId) then
            insert into tempdept           values(DeptNo1,NULL,UpperDeptNo3,NULL,level);
           exit;
        else
           UpperDeptNo2 := UpperDeptNo1;
           select topNo into UpperDeptNo1
           from dept
           where Departno= UpperDeptNo2;
           if UpperDeptNo2 != UpperDeptNo1
    then
                level := level+1;
           end if;
         end if;
      END LOOP;
    fetch getDept into DeptNo1,UpperDeptNo1;
END LOOP;
close getDept;
END;
/