Oracle案例：利用存储过程进行表数据分离

某客户有个需求，有2张业务表，数据量比较大，有2000W行，现在的需求是把这2张表中的一部分记录，根据一定的where条件分离出去，

某客户有个需求，有2张业务表，数据量比较大，有2000W行，现在的需求是把这2张表中的一部分记录，根据一定的where条件分离出去，创建到另外的归档表中，即做表记录的迁移操作。最后得到的结果是：未满足筛选条件的记录留在原表中，满足筛选条件的表要插入到归档表中，并且要在原表中删除这些插入到归档表中的全部记录，最后满足：新表记录+归档记录=原表记录数

下面我来模拟一下这个过程：
由于没有拿到具体的建表语句，这里把表的内容最简化，只留2个列，作为最基本的演示

--连接到测试用户，创建测试表
SQL> conn zlm/zlm
Connected.
SQL> select * from cat;

no rows selected
SQL> create table tabhdr(tabhdrid number(10),status number(10));

Table created.

SQL> create table tabdet(tabhdrid number(10));

Table created.

SQL> create table arch_tabdet as select * from tabdet;

Table created.

SQL> create table arch_tabhdr as select * from tabhdr;

Table created.

--创建操作日志表
SQL> create table arch_log(
  2        archbegintime char(19),
  3        archmiddletime char(19),
  4        archendtime  char(19),
  5        archinscount1    number,
  6        archdelcount1    number,
  7        archinscount2    number,
  8        archdelcount2    number,
  9        archstatus    varchar2(20),
 10        archerrorcode varchar2(20),
 11        archerrormsg  varchar2(1000));

Table created.

操作日志表字段说明：
archbegintime->archimiddletime    //第一次迁移操作（insert+delete）的时间
archimiddletime->archendtime    //第二次迁移操作（insert+delete）的时间
archcount1    //第一次迁移操作（insert+delete）的数据量
archcount2    //第二次迁移操作（insert+delete）的数据量
archstatus    //操作状态（success/failure）
archerrorcode    //报错代码
archerrormsg    //报错信息

--插入测试数据（每个表插入10W条记录，仅测试功能没必要用很大的数据）
SQL> begin
  2  for i in 1..100000
  3  loop
  4  insert into tabhdr values(i,9);
  5  insert into tabdet values(i);
  6  end loop;
  7  commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

--创建存储过程detach_pro
SQL> create or replace procedure detach_pro
  2  is
  3    maxrows number default 10000;
  4    rowid_table dbms_sql.Urowid_Table;
  5    i number;
  6    cursor cur_1 is SELECT a.rowid FROM tabdet a WHERE tabhdrid IN(SELECT tabhdrid FROM tabhdr WHERE STATUS=9)  order by a.rowid;
  7    cursor cur_2 is SELECT a.rowid FROM tabhdr a WHERE status=9 order by a.rowid;
  8   
  9    v_begintime char(19):=to_char(sysdate,'yyyy-mm-dd hh:mi:ss');
 10    v_middletime char(19);
 11    v_inscount1  number:=0;
 12    v_delcount1  number:=0;
 13    v_inscount2  number:=0;
 14    v_delcount2  number:=0;
 15    v_errcode varchar2(100);
 16    v_errerrm varchar2(1000);
 17   
 18  begin
 19    open cur_1;
 20    loop   
 21      exit when cur_1%notfound;
 22      fetch cur_1 bulk collect into rowid_table limit maxrows;
 23     
 24      forall i in 1 .. rowid_table.count
 25      insert into arch_tabdet select * from tabdet where rowid = rowid_table(i);
 26      commit;
 27      v_inscount1:=v_inscount1+rowid_table.count;
 28      forall i in 1 .. rowid_table.count
 29      delete from tabdet where rowid = rowid_table(i);
 30      commit;
 31      v_delcount1:= v_delcount1+rowid_table.count;
 32    end loop;
 33    close cur_1;
 34    v_middletime:=to_char(sysdate,'yyyy-mm-dd hh:mi:ss');
 35    open cur_2;
 36    loop 
 37      exit when cur_2%notfound;
 38      fetch cur_2 bulk collect    into rowid_table limit maxrows;
 39     
 40      forall i in 1 .. rowid_table.count
 41      insert into arch_tabhdr select * from tabhdr where rowid = rowid_table(i);
 42      commit;
 43      v_inscount2:=v_inscount2+rowid_table.count;
 44      forall i in 1 .. rowid_table.count
 45      delete from tabhdr where rowid = rowid_table(i);
 46      commit;
 47      v_delcount2:= v_delcount2+rowid_table.count;
 48    end loop;
 49    close cur_2; 
 50    insert into arch_log values (v_begintime,v_middletime,to_char(sysdate,'yyyy-mm-dd hh:mi:ss'), v_inscount1,v_delcount1,v_inscount2,v_delcount2,'success',null,null);
 51    commit;
 52  exception
 53    when others then
 54        v_errcode :=sqlcode;
 55        v_errerrm :=sqlerrm;
      insert into arch_log values (v_begintime,v_middletime,to_char(sysdate,'yyyy-mm-dd hh:mi:ss'),  v_inscount1,v_delcount1,v_inscount2,v_delcount2,'failure',v_errcode,v_errerrm);
 57        commit;
 58  end;
 59  /

Procedure created.

--开始第1次测试
SQL> select count(*) from tabdet;

  COUNT(*)
----------
    100000

SQL> select count(*) from tabhdr;

  COUNT(*)
----------
    100000

 

SQL> select count(*) from arch_tabdet;

  COUNT(*)
----------
        0

SQL> select count(*) from arch_tabdet;

  COUNT(*)
----------
        0

SQL> exec detach_pro;

PL/SQL procedure successfully completed.

SQL> select count(*) from tabdet;

  COUNT(*)
----------
        0

SQL> select count(*) from tabhdr;

  COUNT(*)
----------
        0

SQL> select count(*) from arch_tabdet;

  COUNT(*)
----------
    100000

SQL> select count(*) from arch_tabhdr;

  COUNT(*)
----------
    100000

SQL> select * from arch_log;