一个很急的问题，在线等
create or replace procedure xpro_refreshOrgChart(empType number) is
begin
delete xt_departmentrefresh;
if empType != 0 then
insert into xt_departmentrefresh
select substr(d.treeid, 0, length(d.treeid) - 2),
substr(d.treeid, 0, length(d.treeid) - 2),
d.treeid,
d.treeid,
d.departmentid,
dd.empcount || '人' || '/' || d.departmentname
from xdepartment d,
(select dd.treeid,sum(empCount) as empcount from xdepartment dd ,
(select d.treeid,count(e.employeeid) empCount
from xdepartment d , (select employeeid,departmentid,type1 from xemployee where lastworkdate is null or lastworkdate > sysdate) e
where e.departmentid (+)= d.departmentid and e.type1 = empType
group by d.treeid )ee where ee.treeid like dd.treeid||'%'
group by dd.treeid) dd
where d.treeid = dd.treeid;
else
insert into xt_departmentrefresh
select substr(d.treeid, 0, length(d.treeid) - 2),
substr(d.treeid, 0, length(d.treeid) - 2),
d.treeid,
d.treeid,
d.departmentid,
dd.empcount || '人' || '/' || d.departmentname
from xdepartment d,
(select dd.treeid,sum(empCount) as empcount from xdepartment dd ,
(select d.treeid,count(e.employeeid) empCount
from xdepartment d , (select employeeid,departmentid,type1 from xemployee where lastworkdate is null or lastworkdate > sysdate) e
where e.departmentid (+)= d.departmentid
group by d.treeid )ee where ee.treeid like dd.treeid||'%'
group by dd.treeid) dd
where d.treeid = dd.treeid;
end if;
end xpro_refreshOrgChart;
这个过程是用来实现组织架构图，现还想增加，就是在下把这部门的每一个人也显示出来，
在人员表（xemployee）姓名字段的名为EmployeeName
PARENTTREEID TEMPARENTTREEID TREEID TEMTREEID DEPARTMENTID DEPARTMENTNAME
2 2 40160 3人/外单位
2 2 201 201 40168 1人/广东华夏电力有限公司
2 2 204 204 40171 1人/康景广州公司
2 2 206 206 40202 1人/广州杰伟实业有限公司
10 10 1012 1012 40193 1人/y
10405 10405 1040505 1040505 40081 2人/财务部
10405 10405 1040506 1040506 40082 88人/广州项目部
206 206 20601 20601 4545 张三
206 206 20602 20602 48787 李四
204 204 20401 20401 7878 王王
1040505 1040505 104050501 104050501 2356 张张
关键就是员工的TREEID TEMTREEID 是在本部门的TREEID TEMTREEID下的，自动加上01，02，03，
问题点数：50、回复次数：2Top
1 楼lsqkeke（可可）回复于 2006-07-04 15:55:00 得分 0
Oracle 的发到 SQL 版块了 :)Top
2 楼apms_long（fdfd）回复于 2006-07-04 17:53:27 得分 0
是啊，请高手看一看吧Top
