--配置本地数据库服务器的tnsnames.ora文件
infosys_192.168.0.100 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = infosys)
)
)
--创建db_link(这里的infosys和infosys是目标数据库的用户名和密码)
--'infosys_192.168.0.100'是目标数据库的实例
--mydblink是我们创建的dblink名称,可自定义
create database link mydblink
connect to infosys identified by infosys
using 'infosys_192.168.0.100';
--测试使用链接的数据库(表名@dblink名称)
select * from P_GROUP@mydblink;
/*************************************************************/
/* 上面建立DBLink ,下面我们通过DBlink创建Oracle存储过程和任务 */
/*************************************************************/
--创建一个存储过程
create procedure proc_sjtb
as
begin
delete from swdx_gzryxx where is_tb='1';
insert into swdx_gzryxx(gzrybh,mc,jgdm,phone,is_tb)select distinct(t.zgswry_dm),t.zgswry_mc,t.nsr_swjg_dm,t.tel_zgy,'1' from dxpt_sjtb@mydblink t;
delete from gdzc_jg where is_tb='1';
insert into gdzc_jg(jg_dm,jg_mc,is_tb)select distinct(t.nsr_swjg_dm),t.nsr_swjg_mc,'1' from dxpt_sjtb@mydblink t;
delete from swdx_nsrxx where is_tb='1';
insert into swdx_nsrxx(nsrsbh,nsrmc,jgdm,zgydm,is_tb)select t.nsrsbh,t.nsrmc,t.nsr_swjg_dm,t.zgswry_dm,'1' from dxpt_sjtb@mydblink t;
insert into swdx_nsrphone(nsrsbh,lbbh,tel) select t.nsrsbh,'001',t.TEL_NSR_CW from dxpt_sjtb@mydblink t;
insert into swdx_nsrphone(nsrsbh,lbbh,tel) select t.nsrsbh,'002',t.TEL_NSR_FR from dxpt_sjtb@mydblink t;
insert into swdx_nsrphone(nsrsbh,lbbh,tel) select t.nsrsbh,'003',t.TEL_NSR_QT from dxpt_sjtb@mydblink t;
delete from xt_czy where is_tb='1';
insert into xt_czy(czy_id, czy_mc, jg_code, czy_lxdh, is_zgy,is_tb,is_jgczy,CZY_YXBZ) select distinct (t.zgswry_dm), t.zgswry_mc, t.nsr_swjg_dm, t.tel_zgy,'1','1','0','Y' from dxpt_sjtb@mydblink t;
insert into xt_czy(czy_id, czy_mc, jg_code, is_zgy,is_tb,is_jgczy,CZY_YXBZ) select distinct(t.nsr_swjg_dm),t.nsr_swjg_mc,t.nsr_swjg_dm,'0','1','1','Y' from dxpt_sjtb@mydblink t;
end;
--创建任务(每分钟执行一次)
DECLARE X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
,what => 'proc_sjtb;'
,next_date => to_date('09-12-2008 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(sysdate) + 1 +2 / (24)'
);
END;
--执行任务
--RUN(参数)是我们建立任务的时候自动生成的,指定任务号方可启动任务和删除任务
begin
SYS.DBMS_JOB.RUN(25);
end;
--删除任务
begin
sys.DBMS_JOB.REMOVE(23);
end;
Oracle两个数据库间的数据同步 (DBLink 、计划任务)
最新推荐文章于 2022-11-26 17:51:35 发布