Oracle与SQL Server实现表数据同步

—查看 SQLServer 和其他数据库系统的数据类型对应关系
—SQL 转 Oracle 的类型对应
SELECT *FROM msdb.dbo.MSdatatype_mappings

—详细得显示了各个数据库系统的类型对应
SELECT *FROM msdb.dbo.sysdatatypemappings

DELETE FROM TESTORACLE..SCOTT.EMPLOY_EPL
insert into  TESTORACLE..SCOTT.EMPLOY_EPL
select * from employ_epl

—查看 Oracle 数据库中是否已经有数据了。
select * from  TESTORACLE..SCOTT.EMPLOY_EPL

—1. 准备一个新表
—SqlServer 表 EmployLastRec_Sql 用于记录 employ_epl 表的增删改记录
CREATE TABLE [dbo].[EmployLastRec_Sql](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [modiid] [int] NULL,
    [IsExec] [int] NULL,
    [epl_employID] [varchar](30) NULL,
    [epl_employName] [varchar](30) NULL,
    [epl_Sex] [int] NULL,
    [epl_data] [datetime] NULL
)

—2. 用一个视图 ” 封装 ” 了一下链接服务器下的一张表
create view v_ora_employ
as
    —TESTORACLE 链接服务器名
    select * from  TESTORACLE..SCOTT.EMPLOY_EPL

   
—3.SQL2008 表 employ_epl 建立触发器, 用表 EmployLastRec_Sql 记录下操作的标识
—modiid 等于 1 为 insert,2 为 delete,3 为 update, 字段 isexec 标识该条记录是否已处理,0 为未执行的,1 为已执行的
create trigger trg_employ_epl_insert on employ_epl for insert
as
    insert into EmployLastRec_Sql(modiid,IsExec,epl_employID,epl_employName,epl_Sex)
    select ‘1‘,‘0‘,epl_employID,epl_employName,epl_Sex from inserted

create trigger trg_employ_epl_update on employ_epl for update
as
    insert into EmployLastRec_Sql(modiid,IsExec,epl_employID,epl_employName,epl_Sex)
    select ‘3‘,‘0‘,epl_employID,epl_employName,epl_Sex from inserted

create trigger trg_employ_epl_delete on employ_epl for delete
as
    insert into EmployLastRec_Sql(modiid,IsExec,epl_employID,epl_employName,epl_Sex)
    select ‘2‘,‘0‘,epl_employID,epl_employName,epl_Sex from deleted

 

—4. 创建存储过程进行导数到 ORACLE
—使用游标逐行提取 EmployLastRec_Sql 记录, 根据 modiid 判断不同的数据操作, 该条记录处理完毕后把 isexec 字段更新为 1.
create proc sp_EmployLastRec_Sql
as  —epl_employID,epl_employName,epl_Sex
    declare @modiid int
    declare @employID varchar(30)
    declare @employName varchar(50)
    declare @sex int

    —字段 IsExec 标识该条记录是否已处理,0 为未执行的,1 为已执行的
    if not exists(select * from EmployLastRec_Sql where IsExec=0)
    begin   
        truncate table EmployLastRec_Sql—– 不存在未执行的，则清空表
    return
    end

    declare cur_sql cursor for
        select modiid,epl_employID,epl_employName,epl_Sex
        from EmployLastRec_Sql where IsExec=0 order by [id]—IsExec 0 为未执行的,1 为已执行的

    open cur_sql
    fetch next from cur_sql into @modiid,@employID,@employName,@sex
    while @@fetch_status=0
    begin
        if (@modiid=1) — 插入
        begin
            —– 将数据插入到 ORACLE 表中
            insert into v_ora_employ(epl_employID,epl_employName,epl_Sex)values(@employID,@employName,@sex)
        end

        if (@modiid=2) —删除
        begin
            delete from v_ora_employ where epl_employID=@employID
        end

        if (@modiid=3) —修改
        begin
            update v_ora_employ set epl_employName=@employName,epl_Sex=@sex,epl_data=getdate()
            where  epl_employID=@employID
        end

        update EmployLastRec_Sql set IsExec=1     where current of cur_sql

        fetch next from cur_sql into @modiid,@employID,@employName,@sex
    end

    deallocate cur_sql 

—5. 调用该存储过程的作业, 实现 5 秒执行一次该存储过程, 做到 5 秒数据同步。
—先建一个一分钟运行一次的作业, 然后在 ” 步骤 ” 的脚本中这样写: 
DECLARE @dt datetime 
SET @dt = DATEADD(minute, –1, GETDATE())
—select @dt
WHILE @dt < GETDATE()
BEGIN
EXEC sp_EmployLastRec_Sql 
    WAITFOR DELAY ‘00:00:05‘ — 等待 5 秒, 根据你的需要设置即可
END