问一个低级问题：我想让数据库在每天定时执行一个存储过程，如何实现？

Question

问一个低级问题：我想让数据库在每天定时执行一个存储过程，如何实现？

Answer 1

使用JOB:
---
   --建立一存储过程
   create or replace procedure log_proc  as 
     begin
      insert into test(aa) values(sysdate);
      commit;
     end;
---
    --提交一个job
     declare 
       job_num  number;
     begin
       dbms_job.submit(job_num,'log_proc;',sysdate,sysdate+5/(24*60*60),false);
       dbms_output.put_line('Job numer='||to_char(job_num));
     end;--说明：
      1> 上面程序从当前开始，间隔5秒执行一次。
      2> 如果每天几点执行，可以写为(比如从2004-09-13开始执行，每天7点执行)
                      next_date => to_date('13-09-2004 07:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'trunc(sysdate)+(7+24)/24')
        3> 如果是每个月几号开始执行。比如每月2号21点执行。
         add_months(trunc(sysdate,'MONTH'),1) + 2-1 + 21/24

Answer 2

对，用JOB可以在表中配置JOBCREATE OR REPLACE package body pkg_jobs as--处理任务，定期的将SMS_PREPARE中超时纪录删除
procedure proc_job1
as
v_sqlerrm VARCHAR2(600);
BEGIN
  --INSERT INTO LOG_INFO (USERNAME, LOG_TIME, ACTION) VALUES ('JOB1' ,sysdate,'JOB EXEC1') ;
  LOOP
    DELETE FROM SMS_PREPARE WHERE SEND_TIME < SYSDATE AND ROWNUM < 201;
EXIT WHEN SQL%NOTFOUND;
    COMMIT;
  END LOOP;
  COMMIT;
  --INSERT INTO LOG_INFO (USERNAME, LOG_TIME, ACTION) VALUES ('JOB1' ,sysdate,'JOB EXEC2') ;  EXCEPTION
    WHEN OTHERS THEN
  v_sqlerrm := SUBSTR(SQLERRM,1,600);
      INSERT INTO ERR_LOG(LOG_TIME,DETAILS)
    VALUES(SYSDATE,'ERROR OCCUR IN PKG_JOBS.PROC_JOB1,REASON:' || v_sqlerrm);
END;/*
添加任务
说明：
  1、根据数据库中的配置添加一个任务；
*/
procedure add_job(iv_job in number)
as
  v_job_id      jobontime.job_id%TYPE;
  v_job_what    jobontime.JOB_WHAT%TYPE;
  v_job_next_date    jobontime.Job_next_date%TYPE;
  v_job_interval    jobontime.JOB_INTERVAL%TYPE;
BEGIN
  select job_id,job_what,job_next_date,job_interval
    into v_job_id,v_job_what,v_job_next_date,v_job_interval
from jobontime
where job_id = iv_job;  dbms_job.isubmit(v_job_id,v_job_what,to_date(v_job_next_date,'YYYYMMDDHH24MI'),v_job_interval);
END;/*
删除任务
说明：
  1、删除一个任务；
*/
procedure remove_job(iv_job in number)
as
BEGIN
  dbms_job.REMOVE( iv_job );
END;/*
开始任务
说明：
  1、根据数据库中的配置添加一个任务；
  2、使用Run让任务开始执行；
*/
procedure start_job(iv_job in number)
as
  v_job_id      jobontime.job_id%TYPE;
  v_job_what    jobontime.JOB_WHAT%TYPE;
  v_job_next_date    jobontime.Job_next_date%TYPE;
  v_job_interval    jobontime.JOB_INTERVAL%TYPE;
BEGIN
  select job_id,job_what,job_next_date,job_interval
    into v_job_id,v_job_what,v_job_next_date,v_job_interval
from jobontime
where job_id = iv_job;  dbms_job.isubmit(v_job_id,v_job_what,to_date(v_job_next_date,'YYYYMMDDHH24MI'),v_job_interval);
  dbms_job.run(v_job_id);
END;/*
重新开始任务
说明：
  1、删除一个任务；
  2、根据数据库中的配置添加一个任务；
  3、使用Run让任务开始执行；
*/
procedure restart_job(iv_job in number)
as
  v_job_id      jobontime.job_id%TYPE;
  v_job_what    jobontime.JOB_WHAT%TYPE;
  v_job_next_date    jobontime.Job_next_date%TYPE;
  v_job_interval    jobontime.JOB_INTERVAL%TYPE;
BEGIN
  dbms_job.remove(iv_job);
  select job_id,job_what,job_next_date,job_interval
    into v_job_id,v_job_what,v_job_next_date,v_job_interval
from jobontime
where job_id = iv_job;  dbms_job.isubmit(v_job_id,v_job_what,to_date(v_job_next_date,'YYYYMMDDHH24MI'),v_job_interval);
  dbms_job.run(v_job_id);
END;end;
/

Answer 3

也可以用pro*c写个程序，编译之后用sh文件调用。不过有点麻烦！