oracle中使用job来周期性的定时执行sql

oracle中使用job来周期性的定时执行sql

  (2012-04-20 13:38:19)

转载▼

标签：  oracle   job   dbms_jobs   作业   杂谈

分类： DataBase

一.查看oracle数据库中的job
select job,what from user_jobs;

二.删除oracle中的job
execute dbms_job.remove(jobID);
注：jobID即"select job,what from user_jobs"中查询出来的job值。

三.创建job，即DBMS_Jobs

1.下面给出一个样例：

SQL >  create  table  a( a  date);
Table  created
// 创建一个过程 procedure
SQL >  create  or  replace  procedure  test  as
    2    begin
    3    insert  into  a  values( sysdate);
    4    end;
    5    /
Procedure  created
// 提交作业
SQL >  declare
    2    job1  number;      // 定义一个数字型变量
    3    begin
    4    dbms_job . submit ( job1 , 'test;' , sysdate , 'sysdate+1/1440');    // 按分钟算一天 1440 分钟。该语句表示从当前时间开始执行，每隔一分钟执行一次，其中sysdate表示系统当前时间。
    5    end;
    6    /
PL / SQL  procedure  successfully  completed
job1
---------
4
SQL >  commit;
Commit  complete
// 运行作业
SQL >  begin
    2    dbms_job . run( 4);
    3    end;
    4    /
PL / SQL  procedure  successfully  completed
// 删除作业
SQL >  begin
    2    dbms_job . remove( 4);
    3    end;
    4    /
PL / SQL  procedure  successfully  completed
SQL >  commit;
Commit  complete

// job  change // 修改作业
execute  dbms_job . change( 186 , null , null , 'sysdate+3');
execute  dbms_job . change( 186 , 'scott.test(update)');

2.下面给出第二个样例：

create  or  replace  procedure  backup_table
as
v_year  varchar2( 20);
begin
select  to_char( sysdate , 'yyyy-mm')  into  v_year  from  dual;
insert  into  t_backup  select  *  from  t_table  where  to_char( C_WORKTIME , 'yyyy-mm') = v_year;
commit;

exception
when  others  then
rollback;
end;
/


declare
    JOB_BACKUP  number ;
begin
    dbms_job . submit( JOB_BACKUP ,
                                    ' backup_table ;' ,
                                 
to_date( '01-07-2012 02:00:00' , 'dd-mm-yyyy hh24:mi:ss' ),
                                    'ADD_MONTHS(trunc(sysdate,''yyyy''),6)+2/24'
);
    commit;
end;
/

讲解：首先创建了一个backup_table的存储过程，该存储过程是将t_table中的c_worktime字段为当前月的数据备份到t_backup表中； 然后创建了一个JOB_BACKUP，该JOB首次执行时间为01-07-2012 02:00:00，以后的每次执行时间为每隔半年执行一次。

四.job的定时执行和时间间隔

interval是指上一次执行结束到下一次开始执行的时间间隔，当interval设置为null时，该job执行结束后， 就被从队列中删除。假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。

1.每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/ (24*60)

2.每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/ (24)

3.每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24

4.每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

5.每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

6.每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

7.每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24