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;
/