|
前提：本地数据库存在一张：A_TEST表，服务器数据库有两张表：A_TEST、A_TEST_HIST(历史表),需要将本地数据的更改、新增、删除同步到服务器数据库的两张表中。
方法一：通过触发器同步。
1、
用如下命令建立DB Link:
create database link MYTEST connect to XXXX identified by 'XXXX' using 'IP/Service Name(我的是：orcl)';
注意：需要DB支持Advanced replication功能，是否支持，可用如下SQL查看：
 select * from v$option where PARAMETER='Advanced replication';
如果是返回True就表示支持。
2、建立触发器：sendToCeShiTest
create or replace trigger sendToCeShiTest
after insert or update or delete
on A_TEST
for each row
declare cunt integer:=0;
--PRAGMA AUTONOMOUS_TRANSACTION; --自治事务解决触发器导致"ORA-04091：触发器/函数不能读它"不可行的验证
begin
case
when updating then
SELECT count(*) INTO cunt FROM A_TEST@MYTEST t WHERE t.A_TEST_ID=:old.A_TEST_ID;
IF cunt>0 THEN --当服务器数据库存在该条数据记录，只做更新，同时将原数据记录到历史表。
INSERT INTO A_TEST_HIST@MYTEST (A_TEST_ID,TEST_NAME,TEST_AGE,UPDATE_DATE) VALUES (:old.A_TEST_ID,:old.TEST_NAME,:old.TEST_AGE,SYSDATE);
update A_TEST@MYTEST t set t.TEST_AGE=:new.TEST_AGE, t.TEST_NAME=:new.TEST_NAME where t.A_TEST_ID=:old.A_TEST_ID;
END IF;
IF cunt<=0 THEN ---当服务器数据库不存在该条数据记录，将记录插入服务器数据库的A_TEST表。
INSERT INTO A_TEST@MYTEST (A_TEST_ID,TEST_NAME,TEST_AGE) VALUES (:new.A_TEST_ID,:new.TEST_NAME,:new.TEST_AGE);
END IF;
when inserting then
INSERT INTO A_TEST@MYTEST (A_TEST_ID,TEST_NAME,TEST_AGE) VALUES (:new.A_TEST_ID,:new.TEST_NAME,:new.TEST_AGE);
when deleting then
INSERT INTO A_TEST_HIST@MYTEST t (t.A_TEST_ID,t.TEST_NAME,t.TEST_AGE,t.UPDATE_DATE) VALUES (:old.A_TEST_ID,:old.TEST_NAME,:old.TEST_AGE,sysdate);
DELETE FROM A_TEST@MYTEST t WHERE t.A_TEST_ID=:old.A_TEST_ID;
end case;
end;
3、测试
--UPDATE A_TEST SET TEST_NAME='小李' WHERE A_TEST_ID='1';
--INSERT INTO A_TEST (A_TEST_ID,TEST_NAME,TEST_AGE) VALUES (2,'小陈',27);
--delete FROM A_TEST WHERE A_TEST_ID='1';
SELECT * FROM A_TEST;//查询本地库中的表A_TEST
SELECT * FROM A_TEST@MYTEST;//查询服务器数据库中的表A_TEST
SELECT * FROM A_TEST_HIST@MYTEST;//查询服务器数据库中的历史表A_TEST_HIST
4、结果
触发器同步数据成功。
方法二：通过定时任务调用存储过程同步。
1、创建存储过程JOB_PRO_TEMP
注：通过MERGE关键字合并两张表（条件是 id 相同），id为主键
CREATE OR REPLACE PROCEDURE JOB_PRO_TEMP
AS
BEGIN
MERGE INTO A_TEST
USING A_TEST@MYTEST f
ON ( A_TEST.A_TEST_id = f.A_TEST_id ) /*条件是 id 相同*/
WHEN MATCHED THEN UPDATE SET A_TEST.TEST_NAME = f.TEST_NAME,A_TEST.TEST_AGE= f.TEST_AGE /* 匹配的时候，更新*/
WHEN NOT MATCHED THEN INSERT VALUES(f.A_TEST_id,f.TEST_NAME,f.TEST_AGE);
commit;
EXCEPTION --出错回滚
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Exception happened,data was rollback');
ROLLBACK;
END;
2、创建定时任务
DECLARE
JOB_ID INTEGER;
BEGIN
DBMS_JOB.SUBMIT(
JOB => JOB_ID ,
WHAT => 'JOB_PRO_TEMP;', --调用的JOB_PRO_TEMP存储过程,多个存储过程以分号分割，注意最后面也有一个分号。
next_date => sysdate,--下一次任务运行时间
INTERVAL => 'SYSDATE+2/(24*60)'); --设置每2分钟执行一次
COMMIT;
END;
注：
（1）、在特定时间间隔后,重复运行该任务
SYSDATE+n’,n泛指一个以天为单位的时间间隔:
描述 Interval参数值
每天运行一次 ‘SYSDATE+1′
每小时运行一次 ‘SYSDATE+1/24′
10分钟运行一次 ‘SYSDATE+10/(60*24)’
每30秒运行一次 ‘SYSDATE+30/(60*24*60)’
每星期运行一次 ‘SYSDATE+7′
注:此任务表达式不能保证任务的下一次运行时间在特定的日期或者时间,只能够指定一个任务两次运行之间的时间间隔。
（2）、在特定的日期和时间运行任务
| 描述 |
Lnterval参数值 |
| 每天午夜12点 |
‘TRUNC(SYSDATE+1)’ |
| 每天早上8点30分 |
‘TRUNC(SYSDATE+1)+(8*60+30)/(24*60)’ |
| 每星期二中午12点 |
‘NEXT_DAY(TRUNC(SYSDATE),”TUESDAY”)+12/24′ |
| 每个月第一天的午夜12点 |
‘TRUNC(LAST_DAY(SYSDATE)+1)’ |
| 每个季度最后一天的晚上11点 |
‘TRUNC(ADD_MONTHS(SYSDATE+2/24,3),’Q')-1/24′ |
| 每星期六,日早上6点10分 |
‘TRUNC(LEAST(NEXT_DAY(SYSDATE,”SATURDAY”),
NEXT_DAY(SYSDATE,”SUNDAY”)))+(6*60+10)/(24*60)’ |
3、查询定时任务ID
SELECT * FROM DBA_JOBS;
SELECT * FROM USER_JOBS;
我这边查询定时任务ID=23；
4、手动执行定时任务。（注：如果设置的间隔较长可以采用手动运行测试）
BEGIN
dbms_job.run(23); --23为前面查询的定时任务ID
END;
5、移出定时任务
BEGIN
dbms_job.remove(23); --23为前面查询的定时任务ID
END;
运行结果：
数据已经由服务器上面的oracle数据库表同步过来了。
|
