1、存储过程:
create or replace procedure SYNC_WORKGROUP
as
v_org_id number(15);
v_group_id number(15);
v_org_users number(9);
v_group_uers number(9);
cursor orgGroups is select a.id as orgid, b.id as workgroupid from usi_organization a, usi_workgroup b where instr(b.name, '('||a.id||')') > 0;
begin
for orgGroup in orgGroups loop
v_org_id := orgGroup.orgid;
v_group_id := orgGroup.workgroupid;
select count(1) into v_org_users from usi_user t
where t.del_flag = 0 and t.org_id in(select id from usi_organization start with parentid = v_org_id connect by
prior id = parentid union select id from usi_organization where id = v_org_id);
select count(1) into v_group_uers from usi_workgroup_user t where t.state = 0 and t.workgroupid = v_group_id;
if v_org_users <> v_group_uers then
delete from usi_workgroup_user t where t.workgroupid = v_group_id;
insert into usi_workgroup_user (
select seq_usi_workgroup_user.nextval, v_group_id, t.user_code, 0, sysdate, sysdate
from usi_user t
where t.del_flag = 0
and t.org_id in(select id from usi_organization start with parentid = v_org_id
connect by prior id = parentid union select id from usi_organization where id = v_org_id));
end if;
end loop;
end;
2、创建定时任务,每天执行
在命令窗口执行命令:声明job
variable jobno number;
begin
dbms_job.submit(:jobno,'sync_workgroup;',trunc(sysdate)-1/1440,'trunc(sysdate)-1/1440+1');
end;
/
启动job
begin
dbms_job.run(:jobno);
end;
/
3、PS:可以用“select job,next_date,next_sec,broken from user_jobs;” 查看定时任务状态