PLSQL 逻辑多线程机制

最新推荐文章于 2025-01-16 07:41:51 发布

weixin_33795093

最新推荐文章于 2025-01-16 07:41:51 发布

阅读量96

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/eastsea/p/5279118.html

本文介绍了一个Oracle PL/SQL包的实现，该包能够批量创建数据库作业，并提供了释放单个作业及清除作业运行历史记录的功能。通过使用dbms_scheduler过程，此包实现了对作业的动态管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE OR REPLACE PACKAGE jv_para_pkg IS

PROCEDURE main(i_job_name   VARCHAR2,
                 i_job_action VARCHAR2,
                 i_job_count NUMBER);

END jv_para_pkg;
/
CREATE OR REPLACE PACKAGE BODY jv_para_pkg IS

-- 释放一个job
PROCEDURE release_one_job(i_job_name VARCHAR2, i_job_action VARCHAR2) IS

BEGIN
    dbms_scheduler.create_job(job_name   => i_job_name,
                              job_type   => 'PLSQL_BLOCK',
                              job_action => i_job_action,
                              enabled    => TRUE);
END;

--清除job运行历史
PROCEDURE purge_job_log(i_job_name_prefix VARCHAR2) IS

    CURSOR job_cur IS
      SELECT DISTINCT job_name
        FROM user_scheduler_job_run_details
       WHERE job_name LIKE i_job_name_prefix;
BEGIN
    FOR job_rec IN job_cur LOOP
      dbms_scheduler.purge_log(log_history => 1,
                               which_log   => 'JOB_AND_WINDOW_LOG',
                               job_name    => job_rec.job_name);
    END LOOP;
END;

PROCEDURE main(i_job_name   VARCHAR2,
                 i_job_action VARCHAR2,
                 i_job_count NUMBER) IS

    v_count NUMBER;
BEGIN
    dbms_output.put_line(i_job_action);
    WHILE TRUE LOOP
      FOR i IN 1 .. i_job_count LOOP
        SELECT COUNT(1)
          INTO v_count
          FROM user_scheduler_jobs t
         WHERE t.job_name = i_job_name || '_' || i;
        IF v_count = 0 THEN
          release_one_job(i_job_name   => i_job_name || '_' || i,
                          i_job_action => i_job_action);
          -- 找到即退出
          RETURN;
        END IF;
      END LOOP;
      -- 等待
      dbms_lock.sleep(1);

    END LOOP;
    --清除job运行历史
    purge_job_log(i_job_name || '_%');
END;
END jv_para_pkg;
/