创建job样例

CREATE OR REPLACE PROCEDURE job_procedure AS
i_count INTEGER;
v_job VARCHAR2(128);
v_schedule VARCHAR2(128);
BEGIN
v_job := upper('job_name');
SELECT COUNT(*)
INTO i_count
FROM user_objects t
WHERE t.OBJECT_TYPE = 'JOB'
AND t.OBJECT_NAME = upper(v_job);
IF i_count > 0
THEN
DBMS_SCHEDULER.DROP_JOB(v_job);
END IF;
v_schedule := 'S_' || v_job;
SELECT COUNT(*)
INTO i_count
FROM user_objects t
WHERE t.OBJECT_TYPE = 'SCHEDULE'
AND t.OBJECT_NAME = upper(v_schedule);
IF i_count > 0
THEN
DBMS_SCHEDULER.DROP_SCHEDULE(v_schedule);
END IF;
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => v_schedule,
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=2;',
comments => 'Run at 2 every 24 hours');
DBMS_SCHEDULER.CREATE_JOB(job_name => v_job,
job_type => 'STORED_PROCEDURE',
job_action => 'DELETEHISTORYDATA.P_DELETE_HISTROY_DATA',
schedule_name => v_schedule,
enabled => TRUE,
auto_drop => FALSE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
const.log_error_to_table(SQLCODE, SQLERRM, 'fail to create job ' || v_job);
END init_job_procedure;
/
call job_procedure();
drop procedure job_procedure;