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

日期：2020-06-05 栏目：程序人生浏览：次

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

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;

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

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

转载注明出处：https://www.heiqu.com/2f710d3ac9f260b3a0b7ff980c3a52d3.html

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

相关推荐