Oracle DBMS_SCHEDULER并行运行多个存储过程

Question

我正在尝试弄清楚Oracle 11g中的DBMS_SCHEDULER，并需要帮助设置以下内容：

我有一个调用其他过程列表的过程，如下所示：

CREATE OR REPLACE
PROCEDURE RUN_JOBS AS
BEGIN
  MYUSER.MYPROCEDURE1();
  MYUSER.MYPROCEDURE2();
  MYUSER.MYPROCEDURE3();
  MYUSER.MYPROCEDURE4();
  MYUSER.MYPROCEDURE5();
END;
/

我想使用DBMS_SCHEDULER来在MYPROCEDURE2()完成后同时运行MYPROCEDURE3()，MYPROCEDURE4()和MYPROCEDURE5()。

能否有人给我展示如何设置这个示例？

- jdamae

Answer 1

您可以参考DBMS_SCHEDULER包下的Chains：http://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse009.htm 您也可以通过Oracle Enterprise Manager实现相同的功能，但我现在找不到任何文档链接。

- Andrew not the Saint

Answer 2

你可以使用DBMS_SCHEDULER来实现这个功能。

CREATE OR REPLACE PROCEDURE RUN_JOBS
AS
v_JobNum NUMBER := 1;
BEGIN
 BEGIN
  DBMS_JOB.SUBMIT(v_JobNum,'MYUSER.MYPROCEDURE1;',sysdate,'sysdate +1');
  DBMS_JOB.SUBMIT(v_JobNum,'MYUSER.MYPROCEDURE2;',sysdate,'sysdate +1');
  DBMS_JOB.SUBMIT(v_JobNum,'MYUSER.MYPROCEDURE3;',sysdate,'sysdate +1');
  DBMS_JOB.SUBMIT(v_JobNum,'MYUSER.MYPROCEDURE4;',sysdate,'sysdate +1');
  COMMIT;
 END;
END RUN_JOBS; 
/

这将提交作业并立即运行它们。

- Krisanth Kumar

Answer 3

为每个程序创建三个不同的作业，并在同一时间安排它们。

- Pandey Amit

---

那样做不行 - 在 Proc 2 完成后，您将无法触发它们所有。 - Andrew not the Saint

Answer 4

这是我自定义的方法，将工作并行化为N个单独的作业，保留dbms_scheduler的日志记录和反压支持。日期间隔通过模N进行分配。

create table message_fixup_log (
    source_date date not null,
    started_at timestamp(6) not null,
    finished_at timestamp(6),
    fixed_message_count number(10)
);
alter table message_fixup_log add duration as (finished_at - started_at);
create unique index ix_message_fixup_log_date on message_fixup_log(source_date desc);

create or replace procedure message_fixup(jobNumber number, jobCount number, jobName varchar default null)
is
    minSince date;
    maxSince date;
    since date;
    msgUpdatedCount number;
begin
    -- choose interval
    select trunc(min(ts)) into minSince from message_part;
    select trunc(max(ts))+1 into maxSince from message_part;
    begin
        select max(source_date) + jobCount into since from message_fixup_log
        where finished_at is not null
              and mod(source_date - minSince, jobCount) = jobNumber
              and source_date >= minSince;
    exception when no_data_found then null;
    end;
    if (since is null) then
        since := minSince + jobNumber;
    end if;
    if (since >= maxSince) then
        if (jobName is not null) then
            dbms_scheduler.set_attribute(jobName, 'end_date', systimestamp + interval '1' second);
        end if;
        return;
    end if;

    insert into message_fixup_log(source_date, started_at) values(since, systimestamp);

    -- perform some actual work for chosen interval
    msgUpdatedCount := sql%rowcount;

    update message_fixup_log
    set fixed_message_count = msgUpdatedCount, finished_at = systimestamp
    where source_date = since;
end;

-- manual test
--call message_fixup(0, 1);

declare
    jobName varchar2(256);
    jobCount number default 8;
begin
    for jobNumber in 0..(jobCount-1) loop
        jobName := 'message_fixup_job' || jobNumber;
        begin
            dbms_scheduler.drop_job(jobName, true);
            exception
            when others then null;
        end;
        dbms_scheduler.create_job(
                job_name          => jobName,
                job_type          =>  'stored_procedure',
                job_action        =>  'message_fixup',
                enabled           =>  false,
                start_date        =>  systimestamp,
                repeat_interval   =>  'freq = minutely; interval = 1',
                number_of_arguments => 3
        );
        dbms_scheduler.set_attribute(jobName, 'logging_level', dbms_scheduler.logging_full);
        dbms_scheduler.set_job_anydata_value(jobName, 1, ANYDATA.ConvertNumber(jobNumber));
        dbms_scheduler.set_job_anydata_value(jobName, 2, ANYDATA.ConvertNumber(jobCount));
        dbms_scheduler.set_job_argument_value(jobName, 3, jobName);
        dbms_scheduler.enable(jobName);
    end loop;
end;

- Vadzim