目录
引言
开发中定制定时执行的任务有两种方式,代码级别和数据库级别,代码级别的不多阐述,本文主要介绍如何使用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,是不是很简单,接下来就是按照自己实际需求作对应的修整了,快去试试=================