时间是通过参数传进来,返回删除行数、执行状态和报错信息等,在存储过程里也增加了信息表。
可以根据实际需求更改传参、sql等。
create or replace procedure testuser.prc_del_balhist(
in_start_date in number, ---开始时间
in_end_date in number, ----结束时间
o_sum_count OUT number, ---总删除行数
o_status_flag out varchar2, ---状态标志
o_err_code out varchar2, ----报错码
o_err_msg out varchar2 ----报错信息
)
as
v_date date; ---日期变量
v_start_tm varchar2(50); ---任务开始时间
v_dule_tm number; ---运行时间 秒
v_del_count number; ---- 删除行数
str_del_sql varchar2(1000); ---删除sql
BEGIN
o_status_flag := 'begin';
v_del_count := 0;
o_sum_count := 0;
o_err_code := null;
o_err_msg := null;
str_del_sql := '';
v_date := to_date(in_start_date,'yyyymmdd');
loop
v_start_tm := to_char(sysdate,'yyyymmdd hh24:mi:ss');
str_del_sql := 'delete from testuser.balhist_test hist where hist.baldate='''||v_date|| ''' and hist.crbal= ''0'' ';
execute immediate str_del_sql ;
v_del_count := sql%rowcount;
commit;
o_status_flag := 'succ';
--- log info
v_dule_tm := (sysdate - to_date(v_start_tm,'yyyymmdd hh24:mi:ss')) * 24 * 60 * 60;
insert into testuser.prc_log (RUN_TIME,del_date,sql_str,cost_time,del_count,status,err_code,err_msg) values (to_char(sysdate,'yyyymmdd hh24:mi:ss'),to_char(v_date,'yyyymmdd'),str_del_sql,v_dule_tm,v_del_count,o_status_flag,o_err_code,o_err_msg);
commit;
--- next day
v_date := v_date + 1;
o_sum_count := o_sum_count + v_del_count;
exit when v_date > to_date(in_end_date,'yyyymmdd');
end loop;
exception
when others then
rollback;
o_status_flag := 'failed';
o_err_code := sqlcode;
o_err_msg := substr(sqlerrm, 1, 500);
end;