SQL Server數(shù)據(jù)庫和Oracle數(shù)據(jù)同步方案
下面的文章主要介紹的是SQL Server數(shù)據(jù)庫和Oracle數(shù)據(jù)庫的數(shù)據(jù)同步方案以及具體的解決過程，SQL Server數(shù)據(jù)庫與Oracle的數(shù)據(jù)同步方案與其實(shí)際的解決流程，其更多內(nèi)容請參考下文：
說到同步,其實(shí)是靠"作業(yè)"定時(shí)調(diào)度存儲(chǔ)過程來操作數(shù)據(jù),增,刪,改,全在里面,結(jié)合觸發(fā)器,游標(biāo)來實(shí)現(xiàn),關(guān)于作業(yè)調(diào)度,我使用了5秒運(yùn)行一次來實(shí)行"秒級作業(yè)",這樣基本就算比較快的"同步"
我做的是SQL Server往Oracle端同步,先在SQL Server數(shù)據(jù)庫上建立往Oracle端的鏈接服務(wù)器,我用一個(gè)視圖"封裝"了一下鏈接服務(wù)器下的一張表。
- create view v_ora_PUBLISHLASTREC
- as
- select * from ORACLEDB..ROADSMS.PUBLISHLASTREC
ORACLEDB鏈接服務(wù)器名,ROADSMS為表空間名,PUBLISHLASTREC 為數(shù)據(jù)表名
然后我們分別在SQL Server數(shù)據(jù)庫要同步的表上建立,insert,delete,update觸發(fā)器
腳本如下:
說明:modiid等于1為insert,2為delete,3為update
- create trigger trg_PUBLISHLASTREC_insert on PUBLISHLASTREC for insert
- as
- insert into dbo.PublishLastRec_SQL(modiid,SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime,IsExec)
- select '1',SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime,'0' from inserted
- create trigger trg_PUBLISHLASTREC_update on PUBLISHLASTREC for update
- as
- insert into dbo.PublishLastRec_SQL(modiid,SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime,IsExec)
- select '3',SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime,'0' from inserted
- create trigger trg_PUBLISHLASTREC_delete on PUBLISHLASTREC for delete
- as
- insert into dbo.PublishLastRec_SQL(modiid,SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime,IsExec)
- select '2',SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime,'0' from deleted
所有的操作都是把各幻表的數(shù)據(jù)插入到一張表中,上面統(tǒng)一插入的表為PublishLastRec_SQL,記錄下操作的標(biāo)識,以標(biāo)識該條記錄是插入,刪除,還是修改,modiid等于1為insert,2為delete,3為update,字段isexec標(biāo)識該條記錄是否已處理,0為未執(zhí)行的,1為已執(zhí)行的
接著就是最關(guān)鍵的一步,存儲(chǔ)過程
腳本如下:
- ALTER proc pro_PublishLastRec_Sql
- as
- declare @modiid int
- declare @signalguid int
- declare @areano numeric(1,0)
- declare @signalnote varchar(50)
- declare @areanote varchar(50)
- declare @publishroadstatus varchar(20)
- declare @publishtime varchar(50)
- if not exists(select * from PublishLastRec_SQL where IsExec=0)
- begin
- truncate table PublishLastRec_SQL
- return
- end
- declare cur_sql cursor for
- select modiid,SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime from
- PublishLastRec_SQL where IsExec=0 order by [id]--IsExec 0為未執(zhí)行的,1為已執(zhí)行的
- open cur_sql
- fetch next from cur_sql into @modiid,@SignalGUID,@AreaNo,@SignalNote,@AreaNote,@PublishRoadStatus,@PublishTime
- while @@fetch_status=0
- begin
- if (@modiid=1) --插入
- begin
- insert into v_ora_PUBLISHLASTREC(SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime)
- values(@SignalGUID,@AreaNo,@SignalNote,@AreaNote,@PublishRoadStatus,@PublishTime)
- end
- if (@modiid=2) --刪除
- begin
- delete from v_ora_PUBLISHLASTREC where SignalGUID=@SignalGUID and AreaNo=@AreaNo
- end
- if (@modiid=3) --修改
- begin
- update v_ora_PUBLISHLASTREC
- set SignalNote=@SignalNote,AreaNote=@AreaNote,PublishRoadStatus=@PublishRoadStatus,
- PublishTime=@PublishTime
- where SignalGUID=@SignalGUID and AreaNo=@AreaNo
- end
- update PublishLastRec_SQL
- set IsExec=1
- where current of cur_sql
- fetch next from cur_sql into @modiid,@SignalGUID,@AreaNo,@SignalNote,@AreaNote,@PublishRoadStatus,@PublishTime
- end
- deallocate cur_sql
該存儲(chǔ)過程使用游標(biāo)逐行提取PublishLastRec_Sql記錄,根據(jù)modiid判斷不同的數(shù)據(jù)操作,該條記錄處理完畢后把isexec字段更新為1.
***是調(diào)用該存儲(chǔ)過程的作業(yè),我們先建一個(gè)一分鐘運(yùn)行一次的作業(yè),然后在"步驟"的腳本中這樣寫:
- DECLARE @dt datetime
- SET @dt = DATEADD(minute, -1, GETDATE())
- WHILE @dt < GETDATE()
- BEGIN
- EXEC pro_PublishLastRec_Sql
這里pro_PublishLastRec_Sql 為你要作業(yè)執(zhí)行的存儲(chǔ)過程
WAITFOR DELAY '00:00:05' -- 等待5秒, 根據(jù)你的需要設(shè)置即可
END
現(xiàn)在，我們即可以實(shí)現(xiàn)5秒執(zhí)行一次該存儲(chǔ)過程,做到5秒數(shù)據(jù)同步。
以上的相關(guān)內(nèi)容就是對SQL Server數(shù)據(jù)庫與Oracle的數(shù)據(jù)同步方案及解決過程的介紹，望你能有所收獲。
【編輯推薦】
