oracle 存储过程实现 按表、时间段删除数据

--创建表

create table t_test
(s_id    varchar2(30),
s_name  varchar2(30),
dt_insert date
);
--插入测试数据 
insert into t_test
  (s_id, s_name, dt_insert)
  select lpad(to_char(rownum + (select count(*) from t_test)), 10, '0'),
         '2018-11-08-1000条',
         date '2018-11-08'
    from dual
  connect by rownum <= 1000
  union all
  select lpad(to_char(rownum + (select count(*) from t_test)), 10, '0'),
         '2018-11-09-1000条',
         date '2018-11-09'
    from dual
  connect by rownum <= 1000
  union all
  select lpad(to_char(rownum + (select count(*) from t_test)), 10, '0'),
         '2018-11-10-1000条',
         date '2018-11-10'
    from dual
  connect by rownum <= 1000
  union all
  select lpad(to_char(rownum + (select count(*) from t_test)), 10, '0'),
         '2018-11-11-1000条',
         date '2018-11-11'
    from dual
  connect by rownum <= 1000;
  commit;

create or replace procedure p_del_tab(str_in_tab    in varchar2, --表名
                                      dt_in_beg     in date, --开始日期
                                      dt_in_end     in date, --结束日期
                                      str_o_flag    out varchar2, --成功/失败标识
                                                                  --返回数值 说明成功执行
                                                                  --返回失败  请查看异常信息
                                      str_o_sqlcode out varchar2, --异常代码
                                      str_o_sqlerrm out varchar2 --异常信息
                                      )
/******************************************************************************************
  name:p_del_tab
  purpose: 按开始、结束时间删除指定表中的记录
  ver     date       author    description
  ----- ----------- ---------- ----------------------
  v1.0   2018-11-11  dayoff    1.创建存储过程
 
  notes: 1.符合条件每500条提交一次
  *******************************************************************************************/
 as
  i_count      number; --记录数
  i_delcount   number := 0; --删除量
  s_p_sqldel   varchar2(1000); --删除sql
  s_p_sqlcount varchar2(1000); --统计sql
 
begin
  str_o_sqlcode := null;
  str_o_sqlerrm := null;
  s_p_sqlcount  := 'select count(*) from ' || str_in_tab ||
                   ' where dt_insert between ''' || dt_in_beg ||
                   ''' and ''' || dt_in_end || ''' ';
 -- dbms_output.put_line(s_p_sqlcount);
  execute immediate s_p_sqlcount
    into i_count;
  --符合条件的数据删除
  if i_count > 0 then
    --循环每500条删除并提取
    loop
      s_p_sqldel := 'delete from ' || str_in_tab ||
                    ' where dt_insert between ''' || dt_in_beg ||
                    ''' and ''' || dt_in_end || ''' and rownum <=500';
     -- dbms_output.put_line(s_p_sqldel);
      execute immediate s_p_sqldel;
     -- dbms_output.put_line(sql%rowcount);
      i_delcount := i_delcount + sql%rowcount;
      commit;
      --统计当前符合条件的量
      execute immediate s_p_sqlcount
        into i_count;
     --为0时退出
      exit when i_count <= 0;
    end loop;
  end if;
  str_o_flag := to_char(i_delcount);
  commit;
--异常处理
exception
  when others then
    rollback;
    str_o_flag    := '失败';
    str_o_sqlcode := sqlcode;
    str_o_sqlerrm := substr(sqlerrm, 1, 512);
end;