oracle中怎样编写树级代码-数据库专栏,ORACLE-数据库其他-idc中文资讯站-客观公证的IDC产业权威媒体,关注域名、虚拟主机、服务器、网络、开源技术资讯，整合运营商资源，客观评价各地机房带宽线路质量、IDC企业名录等信息

oracle中怎样编写树级代码-数据库专栏,ORACLE

来源：作者：发布时间:2007-12-25 13:55:36

这是在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;
/

喜欢本文，那就收藏到：

上一篇: SQL 恢复技术-数据库专栏,SQL Server

下一篇: 使用sql*loader进行数据导入-数据库专栏,SQL Server

相关推荐

相关资讯