首页 > 代码库 > 利用存储过程进行表数据分离的案例分享

利用存储过程进行表数据分离的案例分享

某客户有个需求,有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;

ARCHBEGINTIME       ARCHMIDDLETIME      ARCHENDTIME         ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2
------------------- ------------------- ------------------- ------------- ------------- ------------- -------------
ARCHSTATUS           ARCHERRORCODE
-------------------- --------------------
ARCHERRORMSG
----------------------------------------------------------------------------------------------------------------------------------
2014-12-30 10:30:45 2014-12-30 10:30:53 2014-12-30 10:31:02        100000        100000        100000        100000
success

可以看到,执行了存储过程detach_pro以后,原来的两张表中都没有数据了,全部分离到归档表arch_tabdet和arch_tabhdr中去了,当然这是一种极端情况,之前插入的数据都是符合插入到归档表的筛选条件的,即字段"status=9"。操作日志表中记录了各表的插入和删除操作,以及执行的时间。

如果有新的记录插入原表,但是并不符合插入归档表中的筛选条件,比如status=8,来看一下测试结果:

--第2次测试(插入10000条status=8的记录)
SQL> begin 
  2  for i in 1..10000
  3  loop
  4  insert into tabhdr values(i,8);
  5  insert into tabdet values(i);
  6  end loop;
  7  commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> exec detach_pro;

PL/SQL procedure successfully completed.

SQL> exec detach_pro;

PL/SQL procedure successfully completed.

SQL> select count(*) from tabdet;

  COUNT(*)
----------
     10000

SQL> select count(*) from tabhdr;

  COUNT(*)
----------
     10000

SQL> select count(*) from arch_tabdet;

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

SQL> select count(*) from arch_tabhdr;

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

SQL> select * from arch_log;

ARCHBEGINTIME       ARCHMIDDLETIME      ARCHENDTIME         ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2
------------------- ------------------- ------------------- ------------- ------------- ------------- -------------
ARCHSTATUS           ARCHERRORCODE
-------------------- --------------------
ARCHERRORMSG
----------------------------------------------------------------------------------------------------------------------------------
2014-12-30 10:30:45 2014-12-30 10:30:53 2014-12-30 10:31:02        100000        100000        100000        100000
success


2014-12-30 10:43:38 2014-12-30 10:43:38 2014-12-30 10:43:38             0             0             0             0
success


ARCHBEGINTIME       ARCHMIDDLETIME      ARCHENDTIME         ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2
------------------- ------------------- ------------------- ------------- ------------- ------------- -------------
ARCHSTATUS           ARCHERRORCODE
-------------------- --------------------
ARCHERRORMSG
----------------------------------------------------------------------------------------------------------------------------------

2014-12-30 10:44:28 2014-12-30 10:44:28 2014-12-30 10:44:28             0             0             0             0
success



SQL> 


发现执行detach_pro的速度很快,而且原表和归档表的记录都没有发生变化,因为status=8不符合筛选条件,执行存储过程并不会进行迁移操作,即使没有操作成功。由于刚才执行了2次存储过程,就会在记录表中生成2行操作结果的记录

--第3次测试(再次插入1000条符合筛选条件的记录,status=9)
SQL> begin 
  2  for i in 1..1000
  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.

SQL> exec detach_pro;

PL/SQL procedure successfully completed.

SQL> select count(*) from tabdet;

  COUNT(*)
----------
     9000

SQL> select count(*) from tabhdr;

  COUNT(*)
----------
     10000

SQL> select count(*) from arch_tabdet;

  COUNT(*)
----------
    102000

SQL> select count(*) from arch_tabhdr;

  COUNT(*)
----------
    101000

SQL> select * from arch_log;

ARCHBEGINTIME       ARCHMIDDLETIME      ARCHENDTIME         ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2
------------------- ------------------- ------------------- ------------- ------------- ------------- -------------
ARCHSTATUS           ARCHERRORCODE
-------------------- --------------------
ARCHERRORMSG
----------------------------------------------------------------------------------------------------------------------------------
2014-12-30 10:30:45 2014-12-30 10:30:53 2014-12-30 10:31:02        100000        100000        100000        100000
success


2014-12-30 10:43:38 2014-12-30 10:43:38 2014-12-30 10:43:38             0             0             0             0
success


ARCHBEGINTIME       ARCHMIDDLETIME      ARCHENDTIME         ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2
------------------- ------------------- ------------------- ------------- ------------- ------------- -------------
ARCHSTATUS           ARCHERRORCODE
-------------------- --------------------
ARCHERRORMSG
----------------------------------------------------------------------------------------------------------------------------------

2014-12-30 10:44:28 2014-12-30 10:44:28 2014-12-30 10:44:28             0             0             0             0
success


2014-12-30 10:53:15 2014-12-30 10:53:15 2014-12-30 10:53:15          2000          2000          1000          1000
success

ARCHBEGINTIME       ARCHMIDDLETIME      ARCHENDTIME         ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2
------------------- ------------------- ------------------- ------------- ------------- ------------- -------------
ARCHSTATUS           ARCHERRORCODE
-------------------- --------------------
ARCHERRORMSG
----------------------------------------------------------------------------------------------------------------------------------

第一次迁移操作:
tabdet表中的2000行记录被插入到arch_tabdet表中,然后删除tabdet表中的2000行相应记录;
因此结果是tabdet表剩下9000条记录,arch_tabdet增加到12000条记录。

第二次迁移操作:
tabhdr表中的1000行记录被插入到arch_tabhdr表中,然后删除tabhdr表中的1000行相应记录;
因此结果是tabhdr表剩下10000条记录,arch_tabhdr表增加到11000条记录。

在实际生产中,具体是哪些符合迁移条件的表是根据存储过程中具体的where条件来定的,这里的测试并不一定很准确。

最后,可以通过创建job来定期自动运行存储过程。


利用存储过程进行表数据分离的案例分享