SQLServer与Oracle的数据同步有何特点???PublishLastRec_SQL(modiid,SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime,IsExec)select’2′,SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadSta
SQLServer与Oracle的数据同步有何特点?
??PublishLastRec_SQL(modiid,SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime,IsExec)
select ‘2’,SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime,’0′ from deleted
所有的操作都是把各幻表的数据插入到一张表中,上面统一插入的表为PublishLastRec_SQL,记录下操作的标识,以标识该条记录是插入,删除,还是修改,modiid等于1为insert,2为delete,3为update,字段isexec标识该条记录是否已处理,0为未执行的,1为已执行的
接着就是最关键的一步,存储过程
脚本如下:
ALTER proc pro_PublishLastRec_Sql
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
declare cur_sql cursor for
select modiid,SignalGUID,AreaNo,SignalNote,AreaNote,PublishRoadStatus,PublishTime from
PublishLastRec_SQL where IsExec=0 order by [id]–IsExec 0为未执行的,1为已执行的
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)
if (@modiid=2) –删除
begin
delete from v_ora_PUBLISHLASTREC where SignalGUID=@SignalGUID and AreaNo=@AreaNo
if (@modiid=3) –修改
begin
update v_ora_PUBLISHLASTREC
set SignalNote=@SignalNote,AreaNote=@AreaNote,PublishRoadStatus=@PublishRoadStatus,
PublishTime=@PublishTime
where SignalGUID=@SignalGUID and AreaNo=@AreaNo
update PublishLastRec_SQL
set IsExec=1
where current of cur_sql
fetch next from cur_sql into @modiid,@SignalGUID,@AreaNo,@SignalNote,@AreaNote,@PublishRoadStatus,@PublishTime
deallocate cur_sql
该存储过程使用游标逐行提取PublishLastRec_Sql记录,根据modiid判断不同的数据操作,该条记录处理完毕后把isexec字段更新为1。
数据链路层同步协议有什么作用?
数据链路层同步协议同步协议是以许多字符或许多比特组织成的数据块帧为传输单位,在帧的起始处同步,使帧内维持固定的时钟
创业项目群,学习操作 18个小项目,添加 微信:niuben22 备注:小项目!
如若转载,请注明出处:https://www.gzxsgy.com/13065.html