，歡迎訪問網頁設計愛好者web開發。
這是在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;
/
