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

d 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不符合筛选条件，执行存储

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