并行运行过程 - Oracle PL/SQL

我正在尝试使用 dbms_scheduler 并行运行存储过程 - Oracle PL/SQL，但出现类似未知作业的错误，我也尝试过 dbms_job，这里出现错误 - 必须声明标识符 dbms_jobs。有人可以帮我吗？ 以下是我尝试过的两种方法：

CREATE PACKAGE BODY pkg IS
CREATE PROCEDURE do_parallel_execution
IS
BEGIN
   DBMS_SCHEDULER.RUN_JOB('pkg1.proc1', false);
   DBMS_SCHEDULER.RUN_JOB('pkg1.proc2', false);
   DBMS_SCHEDULER.RUN_JOB('pkg1.proc3', false);
END;

CREATE PACKAGE BODY pkg IS
CREATE PROCEDURE run_in_parallel
IS
   l_jobno pls_integer;
BEGIN
   dbms_job.submit(l_jobno, 'pkg1.proc1; end;' );
   dbms_job.submit(l_jobno, 'pkg1.proc2; end;' );
  -- dbms_job.submit(l_jobno, 'pkg1.proc3; end;' );
END;

其中 pkg1 定义了所有 3 个过程。 谢谢你！

要并行执行其他不相关的过程，请使用调度程序作业链：

创建程序：

create or replace package test as
    procedure test1;
    procedure test2;
    procedure test3;
end test;
/

create or replace package body test as
    procedure test1 is
    begin
        sys.dbms_session.sleep(5);
    end test1;

    procedure test2 is
    begin
        sys.dbms_session.sleep(5);
    end test2;

    procedure test3 is
    begin
        sys.dbms_session.sleep(5);
    end test3;
end test;
/

为每个过程创建调度程序：

BEGIN
    DBMS_SCHEDULER.create_program(
        program_name => 'TEST1_PROGRAM',
        program_action => 'TEST.TEST1',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    DBMS_SCHEDULER.ENABLE(name=>'TEST1_PROGRAM');    

    DBMS_SCHEDULER.create_program(
        program_name => 'TEST2_PROGRAM',
        program_action => 'TEST.TEST2',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    DBMS_SCHEDULER.ENABLE(name=>'TEST2_PROGRAM');    

    DBMS_SCHEDULER.create_program(
        program_name => 'TEST3_PROGRAM',
        program_action => 'TEST.TEST3',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    DBMS_SCHEDULER.ENABLE(name=>'TEST3_PROGRAM');    
END;
/

创建调度程序链：

BEGIN
  -- one step for each program
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'TEST_CHAIN'
    ,step_name           => 'CHAIN_STEP1'
    ,program_name        => 'TEST1_PROGRAM');

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'TEST_CHAIN'
    ,step_name           => 'CHAIN_STEP2'
    ,program_name        => 'TEST2_PROGRAM');

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'TEST_CHAIN'
    ,step_name           => 'CHAIN_STEP3'
    ,program_name        => 'TEST3_PROGRAM');

  -- one rule with condition "true" to start each step immediately
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
      CHAIN_NAME  => 'TEST_CHAIN',
      rule_name  => 'TEST_RULE1',
      condition => 'TRUE',
      action => 'START "CHAIN_STEP1"');   

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
      CHAIN_NAME  => 'TEST_CHAIN',
      rule_name  => 'TEST_RULE2',
      condition => 'TRUE',
      action => 'START "CHAIN_STEP2"');   

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
      CHAIN_NAME  => 'TEST_CHAIN',
      rule_name  => 'TEST_RULE3',
      condition => 'TRUE',
      action => 'START "CHAIN_STEP3"');   

  -- one rule to close out the chain after all steps are completed    
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
     chain_name          => 'TEST_CHAIN',
     rule_name           => 'TEST_RULE4',
     condition           => 'CHAIN_STEP1 Completed AND CHAIN_STEP2 Completed AND CHAIN_STEP3 Completed',
     action              => 'END 0');

END;
/

链流程现在如下所示（如 SQL Developer 所示）：

现在创建一个调度程序作业来运行链：

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => 'TEST_JOB',
            job_type => 'CHAIN',
            job_action => 'TEST_CHAIN',
            number_of_arguments => 0,
            start_date => NULL,
            repeat_interval => NULL,
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => '');

    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => 'TEST_JOB', 
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);

END;
/

并运行作业：

BEGIN
    DBMS_SCHEDULER.RUN_JOB(job_name => 'TEST_JOB', USE_CURRENT_SESSION => FALSE);
END;
/

现在查看作业的作业运行详细信息：

"LOG_ID" "LOG_DATE"                               "JOB_NAME" "JOB_SUBNAME" "STATUS"    "ERROR#" "ACTUAL_START_DATE"                                "RUN_DURATION"
"1548"   "14-JUN-20 12.15.46.744612000 AM -04:00" "TEST_JOB" "CHAIN_STEP3" "SUCCEEDED" "0"      "14-JUN-20 12.15.41.708043000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"
"1544"   "14-JUN-20 12.15.46.746544000 AM -04:00" "TEST_JOB" "CHAIN_STEP2" "SUCCEEDED" "0"      "14-JUN-20 12.15.41.690404000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"
"1546"   "14-JUN-20 12.15.46.748830000 AM -04:00" "TEST_JOB" "CHAIN_STEP1" "SUCCEEDED" "0"      "14-JUN-20 12.15.41.690891000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"
"1550"   "14-JUN-20 12.15.46.968592000 AM -04:00" "TEST_JOB" ""            "SUCCEEDED" "0"      "14-JUN-20 12.15.41.574115000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"

注意：

• 该作业于“12.15.41.574115000”（ACTUAL_START_DATE，第 1550 行）开始。

• 每个作业步骤在整个作业启动的几分之一秒内启动（如第 1544、1546 和 1548 行中每个步骤的 ACTUAL_START_DATE 中所记录），并在预期的 5 秒内完成。

• 整个作业于“14-JUN-20 12.15.46.968592000”（LOG_DATE，第 1550 行）完成，完成所有三个步骤的总持续时间为 5 秒。

• 请注意，规则处理可能会给链的总执行时间增加一点点开销。