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

php中文网

发布时间：2016-06-07 16:40:54

1294人浏览过

来源于php中文网

原创

某客户有个需求，有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 /