数据库创建存储过程，做为定时任务

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;”  查看定时任务状态