Oracle定时任务DBMS_JOB

目录

引言

需求

示例

第一步：创建存储过程

第二步：创建定时任务job

第三步：启动定时任务job

测试：数据测试

---

引言

       开发中定制定时执行的任务有两种方式，代码级别和数据库级别，代码级别的不多阐述，本文主要介绍如何使用Oracle数据库的job来定制一个简单的定时执行任务的步骤。

需求

       每间隔1秒钟删除表 batchs_lock 中锁定时间(字段为lock_time)大于等于一分钟的所有记录（也就是字段 lock_time 的时间值与系统当前时间相差1分钟的所有记录），表结构如下：

--------------批次表
create table ecm_batchs_lock
(
  application_no       varchar2(20) not null,
  org_code             varchar2(10) not null,
  lock_time            timestamp(6) not null
);

-- create/recreate primary, unique and foreign key constraints 
alter table ecm_batchs_lock
  add constraint application_no primary key (application_no)
  using index;

示例

废话不多说，直接上例子：

第一步：创建存储过程

create or replace procedure ecm_batchs_lock_proce is
begin
  delete from ecm_batchs_lock e where e.lock_time<=sysdate-1/1440;
  commit;
end;

脚本讲解：

create or replace procedure 存储过程名可随意取 is
begin
  此处编写要执行的脚本逻辑并以分号结尾不能忘;
  commit;
end;
--------------------------------------------------------------
PS：
e.lock_time<=sysdate-1/1440;   表示1分钟前, 其中1440=60*24
e.lock_time<=sysdate-30/1440;  则表示30分钟前
类推：
e.lock_time<=sysdate-30/86400;  表示30秒前，其中86400=60*60*24
...

在如下图可查看到刚刚创建的存储过程：

第二步：创建定时任务job

declare jobno number;
begin
  dbms_job.submit(jobno,'ecm_batchs_lock_proce;',sysdate,'sysdate+1/86400');
  commit;
end;

脚本讲解：

declare 标志位随便写英文即可 number;
begin
  dbms_job.submit(同第一行的标志位,'存储过程名字切记分号不能忘;',sysdate,'sysdate+1/86400');
  commit;
end;
--------------------------------------------------------------
PS：
sysdate+1/86400   表示每隔1秒钟执行一次，86400=60*60*24
类推：
sysdate+1/1440    表示每隔1分钟执行一次，1440=60*24

然后，执行如下查询语句，在如下图可查看到创建好的job详细信息：

select * from dba_jobs;

第三步：启动定时任务job

（1）先查询存储过程 ecm_batchs_lock_proce 的 job 值是多少,其实上图中已经可以看到是74了：

select j.job as job from dba_jobs j where j.WHAT='ecm_batchs_lock_proce;'

脚本讲解：

select j.job as job from dba_jobs j where j.WHAT='存储过程名字;'

（2）将查询到的值代入如下脚本：

begin
  dbms_job.run(74);
  commit;
end;

脚本讲解：

dbms_job.run(查到的值);   可以理解为以id启动job。

启动完job后，在如下图可以看到：

选中后右键，即可进行查看、编辑、删除、启动等操作，如下图：

测试：数据测试

插入一条记录，等待一分钟后发现记录被删除，说明定时任务工作正常：

insert into ecm_batchs_lock(application_no, org_code, lock_time)values('Br-A794596690', 'CA', sysdate);

一分钟后：

案例：

定时查询数据集再插入某张表:

----1------创建存储过程loan_after_check_procedure   创建后在Procedures中查看
create or replace procedure loan_after_check_procedure is
BEGIN
  ---2---查询出数据集合cs
  declare cursor cs is 
          select max(t.task_end_datim),cfsv.NATIONAL_ORGANIZATION_CODE,cfsv.CL_APPLICATION_NUMBER,l.loan_id
          from contract_ocr c ,contract_bp bb ,bp_main_ocr mm,cl_fleet_summary_view cfsv,loan l,task t
          where c.contract_id=bb.contract_id 
          and bb.business_partner_id = mm.business_partner_id
          and c.proposal_nbr = cfsv.APPLICATION_NUMBER
          and cfsv.CL_APPLICATION_NUMBER = l.application_no
          and l.loan_id = t.loan_id
          and t.pri_type='1' and t.flag='8' and t.task_status='5'
          and t.task_end_datim is not null
          --每月1号00:00:00执行 如2022-05-01 00:00:00 执行，查询出2021-06-01 00:00:00 到 2022-07-01 00:00:00之间的所有符合的单子
          and t.task_end_datim between (select add_months(sysdate, -11) from dual) and (select add_months(sysdate, -10) from dual)
          and bb.role_cde='00003' 
          and mm.legal_status_cde='00002' 
          and c.live_sts='L'
          group by cfsv.NATIONAL_ORGANIZATION_CODE,cfsv.CL_APPLICATION_NUMBER,l.loan_id;
BEGIN  
  ---3--遍历数据集合cs将制定字段插入指定表
  for cc in cs loop  
  BEGIN
  INSERT INTO TASK(TASK_ID,LOAN_ID,PRI_TYPE,POSITION_ID,FLAG) 
  ---select (select to_char(sysdate,'yyyymmddhhmmss') FROM DUAL)||(SELECT TRUNC(1000+9000*dbms_random.value) FROM dual) as str from dual  年月日时分秒+4位随机数拼接作为taskid，和代码逻辑一致 
  VALUES((select (select to_char(sysdate,'yyyymmddhhmmss') FROM DUAL)||(SELECT TRUNC(1000+9000*dbms_random.value) FROM dual) as str from dual),cc.loan_id,'30','79','1');   
  COMMIT;
  END;  
END loop;  
END; 
END;


----4----创建定时job（每月1日凌晨0点执行）   创建后在DBMS_Jobs中查看
---TRUNC(sysdate,''mi'')+1/(24*60)      每分钟执行一次
---TTRUNC(LAST_DAY(SYSDATE)+1)          每月1日凌晨0点执行
---TRUNC(LAST_DAY(SYSDATE))+1+1/24      每月1日凌晨1点执行
declare jobno number;
begin
  dbms_job.submit(jobno,'loan_after_check_procedure;',sysdate,'TTRUNC(LAST_DAY(SYSDATE)+1)');
  commit;
end;

=================以上三步完成后，整个定时任务就创建并启动完成了，测试运行OK，是不是很简单，接下来就是按照自己实际需求作对应的修整了，快去试试=================