创建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;