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

某客户有个需求,有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来定期自动运行存储过程,如:
declare

v_jobnum number;
begin
dbms_job.submit(v_jobnum,'detach_pro',sysdate,'sysdate+1/24');
end;
commit;
或:

declare v_jobnum number;

begin

  dbms_job.submit

    ( job      => v_jobnum

    ,what      => 'detach_pro'

    ,next_date => sysdate

    ,interval  => 'SYSDATE+1/24'

    ,no_parse  => TRUE

    );

end;

/

commit;

 


--把存储过程防止到job中,每小时自动运行

 

SQL> declare v_jobnum number;

  2  begin

  3    dbms_job.submit

  4      ( job      => v_jobnum

  5      ,what      => 'detach_pro'

  6      ,next_date => sysdate

  7      ,interval  => 'SYSDATE+1/24'

  8      ,no_parse  => TRUE

  9      );

 10  end;

 11  /

 


PL/SQL procedure successfully completed.

 


SQL> commit;

 


Commit complete.

 


SQL> desc user_jobs;

 Name                                                                    Null?    Type

 ----------------------------------------------------------------------- -------- ------------------------------------------------

 JOB                                                                    NOT NULL NUMBER

 LOG_USER                                                                NOT NULL VARCHAR2(30)

 PRIV_USER                                                              NOT NULL VARCHAR2(30)

 SCHEMA_USER                                                            NOT NULL VARCHAR2(30)

 LAST_DATE                                                                        DATE

 LAST_SEC                                                                        VARCHAR2(8)

 THIS_DATE                                                                        DATE

 THIS_SEC                                                                        VARCHAR2(8)

 NEXT_DATE                                                              NOT NULL DATE

 NEXT_SEC                                                                        VARCHAR2(8)

 TOTAL_TIME                                                                      NUMBER

 BROKEN                                                                          VARCHAR2(1)

 INTERVAL                                                                NOT NULL VARCHAR2(200)

 FAILURES                                                                        NUMBER

 WHAT                                                                            VARCHAR2(4000)

 NLS_ENV                                                                          VARCHAR2(4000)

 MISC_ENV                                                                        RAW(32)

 INSTANCE                                                                        NUMBER

 


SQL> col interval for a15


SQL> col what for a15

SQL> select job,next_date,interval,what from user_jobs;

 


      JOB NEXT_DATE INTERVAL        WHAT

---------- --------- --------------- ---------------

        5 30-DEC-14 SYSDATE+1/24    detach_pro

        4 01-JAN-00 SYSDATE+1/24/60 xxx_pro;

SQL>

 

--删除其他无关的job
SQL> exec dbms_job.remove(4);


PL/SQL procedure successfully completed.


SQL> select job,next_date,interval,what from user_jobs;


      JOB NEXT_DATE INTERVAL        WHAT
---------- --------- --------------- ---------------
        5 30-DEC-14 SYSDATE+1/24    detach_pro

相关推荐