写个存储过程,里面采用merge into 来合并数据。 create or replace procedure my_sync as begin merge into a using (select id,name from b ) c on(a.id=c.id ) when matched then update set a.name=c.name when not matched then insert (a.id,a.name) values (c.id,c.name); end; / 再用个job,间隔10秒来调用这个存储过程。 VARIABLE jobno number; begin DBMS_JOB.SUBMIT(:jobno, 'my_sync;', SYSDATE,'SYSDATE + 10/60/60/24'); --10秒 commit; end; /如果过程执行时间很长,那就相应要调大间隔时间。
create or replace procedure my_sync
as
begin
merge into a
using (select id,name from b ) c
on(a.id=c.id )
when matched then update set a.name=c.name
when not matched then insert (a.id,a.name) values (c.id,c.name);
end;
/
再用个job,间隔10秒来调用这个存储过程。
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'my_sync;',
SYSDATE,'SYSDATE + 10/60/60/24'); --10秒
commit;
end;
/如果过程执行时间很长,那就相应要调大间隔时间。