触发器 迁移数据

alter table TEST_SYNC_A
add constraint pk_test_sync_a_id primary key (ID);

--目标表
create table test_sync_b
(id number,
name varchar2(32),
mono varchar2(32),
gmt_create date,
price number);

alter table TEST_SYNC_B
add constraint pk_test_sync_b_id primary key (ID);

--触发器生成的日志表
create table tri_test_sync_log
(id number,
dml_type varchar2(1),
gmt_create date,
status number);

create index idx_tri_sync_log_id on TRI_TEST_SYNC_LOG (id);

--计数和时间表
create table TMP_TIME
(
ELAPSE_TIME NUMBER,
EXEC_TIME   DATE,
EXEC_PROC   VARCHAR2(20),
EXEC_CNT    NUMBER
);

--错误日志表
create table TMP_DEBUG
(
DEBUG_ID   VARCHAR2(32),
ERROR_MSG VARCHAR2(2000),
ERROR_TIME DATE default sysdate
);

--源表上的触发器
create or replace trigger tri_test_sync
before insert or delete or update on test_sync_a
for each row
declare
/*
创建: 玄风
日期: 2008-02-14
目的: 测试用触发器同步数据
*/
begin
if inserting then
insert into tri_test_sync_log(id, dml_type, gmt_create, status)
values(:new.id, 'I', sysdate, 0);
elsif updating then
insert into tri_test_sync_log(id, dml_type, gmt_create, status)
values(:new.id, 'U', sysdate, 0);
elsif deleting then
insert into tri_test_sync_log(id, dml_type, gmt_create, status)
values(:old.id, 'D', sysdate, 0);
end if;
end tri_test_syns;

---用于同步数据的存储过程

create or replace procedure SP_SYNC_TEST is
/*
创建: xuanfeng
目的: 同步数据
日期：2009-2-14
*/
v_count     number := 0;
start_time number;
end_time    number;
elapse_time number;
v_spname    varchar2(20) := 'SP_SYNC_TEST';
errormsg    varchar2(2000);

begin
execute immediate 'alter session set nls_date_format =''YYYY-MM-DD HH24:MI:SS''';
--删除上次已经迁移过的脚本(数据量太大，每次只删除50000条)
delete from tri_test_sync_log
where status = 1
and rownum < 50000;
commit;
for i in (select id, dml_type from tri_test_sync_log where status = 0) loop

start_time := dbms_utility.get_time;

if i.dml_type in ('I', 'U') then
delete from test_sync_b t where t.id = i.id;
insert into test_sync_b
(ID, NAME, MONO, GMT_CREATE, PRICE)
select ID, NAME, MONO, GMT_CREATE, PRICE
from test_sync_a t
where t.id = i.id;

elsif i.dml_type = 'D' then
delete from test_sync_b t where t.id = i.id;
end if;

--更新迁移的状态
update tri_test_sync_log t set status = 1 where t.id = i.id;

v_count := v_count + 1;
if mod(v_count, 100) = 0 then
--提交
end_time    := dbms_utility.get_time;
elapse_time := (end_time - start_time) / 100;
insert into tmp_time
(elapse_time, exec_time, exec_proc, exec_cnt)
values
(elapse_time, sysdate, v_spname, v_count);

commit;
end if;
end loop;
commit;
exception
when others then
rollback;
errormsg := sqlerrm;
insert into tmp_debug
(debug_id, error_msg)
values
(v_spname, errormsg);
commit;
end SP_SYNC_TEST;
/

----
insert into test_sync_a
(id, name, gmt_create)
values
(1, 'orphean', sysdate);

--初始化数据
declare
-- Local variables here
i number;

begin
select max(id) into i from test_sync_a;
i := i + 1;
for c in 1 .. 1000 loop
insert into test_sync_a
(id, name, gmt_create)
values
(i, 'orphean', sysdate);
i := i + 1;
end loop;
commit;
end;
/

--初始化(test_sync_a)
insert into tri_test_sync_log(id,DML_TYPE,STATUS) select id,'I',0 from test_sync_a;
commit;

--编译存储过程(tbshop)

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'SP_SYNC_TEST;', sysdate, 'sysdate+20/1440', TRUE, :instno);
commit;
end;
/