最近老大要我做几张流水表的数据迁移,要求每天定时清理A表180天前数据,把它先导入历史表B表中(B表结构和A表一致),然后删除a表180天前数据。
思路:先写一个迁移的存储过程,在建立一个oracle的job(定时任务)来定时执行 :
create or replace procedure PB_CLEAR_TEST_1(
in_keepday number, --清理多少天前数据
in_commit NUMBER --每次COMMIT提交数据
)
as
n_records number:=0;--插入+清理计数器
n_totcommit number:=0;--事务提交次数
n_totrecords number:=0;--清理总记录数
n_opencur number:=0;--游标计数器
c_trandate varchar(8);
n_insertrecords number:=0;--插入总记录数
paylog_type tbl_test_log%rowtype;
cursor cur_trans_log(trandate char) is
select * from tbl_test_log
where EXT_TXN_DT <= trandate;
begin
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy-mm-dd hh:mi:ss')||' begin deal records of tbl_test_log!');
c_trandate := to_char(sysdate-in_keepday, 'yyyymmdd');
loop
n_opencur := n_opencur + 1;
open cur_trans_log(c_trandate);
loop
fetch cur_trans_log into paylog_type;
exit when cur_trans_log%NOTFOUND;
--逐条插入历史表
insert into tbl_test_his_log select * from tbl_test_log
where INST_CODE= paylog_type.INST_CODE
and EXT_TXN_DT = paylog_type.EXT_TXN_DT
and MER_ID = paylog_type.MER_ID
and SERIALNO= paylog_type.SERIALNO;
n_insertrecords := n_insertrecords + 1;
n_totcommit := n_totcommit + 1;
--逐条删除
delete from tbl_test_log
where INST_CODE= paylog_type.INST_CODE
and EXT_TXN_DT = paylog_type.EXT_TXN_DT
and MER_ID = paylog_type.MER_ID
and SERIALNO= paylog_type.SERIALNO;
n_records := n_records + 2;
n_totrecords := n_totrecords + 1;
--超过指定条数提交
if n_records >= in_commit then
commit;
n_records := 0;
n_totcommit := n_totcommit + 2;
end if;
end loop;
exit when cur_trans_log%NOTFOUND;
close cur_trans_log;
end loop;
close cur_trans_log;
commit;
n_totcommit := n_totcommit + 1;
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy-mm-dd hh:mi:ss')||' Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_insertrecords)||' records moved!'||' committed '||to_char(n_totcommit)||'. Open cursor '||to_char(n_opencur)||' times.');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_totrecords)||' records moved!'||' committed '||to_char(n_totcommit)||'. Open cursor '||to_char(n_opencur)||' times.');
exception
when others then
rollback;
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy-mm-dd hh:mi:ss')||' Get exception! '||sqlerrm);
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_totrecords)||' records moved!'||' committed '||to_char(n_totcommit)||'. Open cursor '||to_char(n_opencur)||' times.');
return;
end;
/
这个存储过程是前人留下的,我勉强看懂,这里行尾的/是我加的,因为我发现不加这个/,不会执行完,不然你得再在命令行里加个/让这个sql执行,还有这个存储过程之前是半年执行一次的,所以要循环移动和删除每一条数据,并要加上commit的条数来提高效率,不然一次性移动和删除大量数据会很慢的,不过我没搞懂拿到整个循环体的select 语句不会执行很慢吗?不知道还有大神指导下。这里我也偷懒了吧,每天执行的话,数据量不大,不用写这么复杂的,不用循环的。
测试存储过程中发现,测试环境没有历史表的,然后get到一个新的知识点,创建一个和A表结果结果一样的b表语句:
create table_b as select * from table_a where 1<>1;--只建立表结构,不把a表数据塞入新表中
create table_b as select * from table_a ;建立和table_a表结构和数据一样的table_b表
当然你也可以查到a表的建立语句,然后改下表名,再去执行一下。不过这也显然方便多了,这里是oracle的建立一张表结构一样的表的方法,其他比如mysql也有类似语句,可以自行百度;
上述建立存储过程的sql执行发到sql执行窗口执行即可,如果是命令行也可以执行这样执行,最好放到一个文件里,然后@这个文件执行。
如果你想看这个存储过程有没有建立成功,可以执行:
select text from user_source where name ='存储过程名称' order by line;
如果你想执行这个存储过程,要看你在哪执行:
如果在sql窗口中执行,要按如下方式执行:
declare
begin
test_pro_001;--存储过程名称,如果带参数,就加括号并带上参数;
end;
或者
call test_pro_001();
如果是在命令行窗口执行:
exec test_pro_001();
可以参考: https://blog.csdn.net/chxkyy/article/details/83562456
下面写下用定时任务调起上面的存储过程:
declare job number; BEGIN DBMS_JOB.SUBMIT( JOB => job, WHAT => 'PB_CLEAR_EPAY_1(180,300);', INTERVAL => 'trunc(sysdate + 1) + 65/(24*60)' ); end; / |
写成sql文件,在命令行里执行即可,sql窗口也可以执行;
然后执行select * from user_jobs;查看你刚生成的定时任务,next_date加next_sec就是下次执行时间,job是定时任务的id,
手动执行一次定时任务sql如下:
begin
DBMS_JOB.RUN(28);--28是id
end;
删除定时任务是:
begin
DBMS_JOB.REMOVE(28);
DBMS_JOB.REMOVE(29);--可以删除多个
DBMS_JOB.REMOVE(30);
commit;
end;
停止一个定时任务:
begin
dbms_job.broken(31,true,sysdate); /*停止一个job,jobId, job的ID,里面参数true也可是false,next_date(某一时刻停止)也可是sysdate(立刻停止)。 */
dbms_job.broken(32,true,sysdate);
dbms_job.broken(33,true,sysdate);
commit;
end;
停止后nest_date是4000/1/1;
这样就是一个完整的定时执行存储过程的方法了,存储过程和定时任务怎么写可以百度了。都是百度出来的