SqlServer数据通过DBLink 同步到Oracle代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SqlServer数据通过DBLink 同步到Oracle
作者:晋鹏 / 发布于2016/1/25/ 941
/*
--获取刷卡机中刷卡数据,需要先建服务链接，如下：
exec   sp_addlinkedserver     'Ehrtest','','SQLOLEDB','192.168.8.210' 
exec   sp_addlinkedsrvlogin   'Ehrtest','false',null,'sa','digitalhr' 
go 
以代理运行维护计划时，计划中调用SQL格式库名.dbo.过程名称
传参日期格式：
  二个都为'' 空，则以同步近天数据
  二个都为'YYYY-MM-DD' ,则同步二日期区间点至点的数据
  二个都为'YYYY-MM-DD HH:MM' ，则同步二日期区间二时间点间的数据
传参EmpNo格式：'00027' 以此建立本次同步的员式工号
*/
ALTER PROCEDURE [dbo].[usp_GetCardData] 
@SDate nvarchar(20), --开始日期
@EDate nvarchar(20), --结束日期
@EmpNo nvarchar(500)='' --同步卡号指点
AS
Begin
Declare 
    @PersonID           nvarchar(80),   --员工ID
    @CardNum          nvarchar(8),  --员工卡号
    @DeviceNum          nvarchar(2),    --刷卡机号
    @TIMECARDDATE       nvarchar(10),   --刷卡日期
    @TIMECARDTIME       nvarchar(8),    --刷卡时间
    @DEPARTMENTID       nvarchar(80),   --员工所在部门ID 
    @MachineID          nvarchar(80)    --刷卡机ID

IF(@SDate='' OR @EDate='') 
BEGIN
  SET @SDate=Convert(varchar(10),DateAdd(dd,-15,getdate()),120)+' 00:00'
  SET @EDate=Convert(varchar(10),DateAdd(dd,1,getdate()),120)+' 00:00'
END
ELSE IF((charindex(':',@SDate)=0) OR (charindex(':',@EDate)=0))
BEGIN
  SET @SDate=Convert(varchar(10),@SDate,120)+' 00:00'
  SET @EDate=Convert(varchar(10),DateAdd(dd,1,cast(@EDate as datetime)),120)+' 00:00'
END



Create Table #Temp(cardnum nvarchar(40),devicenum nvarchar(40),timecarddate nvarchar(20),timecardtime nvarchar(20))
insert into #Temp 
    Select cardnum,devicenum,convert(varchar(10),cast(left(checkdate,10) as datetime),120),checktime  
       from vw_checkinout
    where  cast(checkdate+' '+checktime as datetime) between cast(@SDate as datetime) and  cast(@EDate as datetime)
       order by cardnum,checktime asc 

declare @countA int
select @countA=count(*) from #Temp
print '数量为:'+convert(nvarchar(20),@countA)


DECLARE curGetData CURSOR FOR 
    select cardnum,devicenum,timecarddate,timecardtime from #Temp order by cardnum asc 
--  Select cardnum,devicenum,convert(varchar(10),cast(left(checkdate,10) as datetime),120),checktime  
--     from vw_checkinout
--  where  cast(checkdate+' '+checktime as datetime) between cast(@SDate as datetime) and  cast(@EDate as datetime)
--     order by cardnum,checktime asc   ---this line add 20141127

OPEN curGetData FETCH  next from curGetData into @CardNum,@DeviceNum,@TIMECARDDATE,@TIMECARDTIME
WHILE @@FETCH_STATUS = 0
BEGIN
    print '剩余数量为:'+convert(nvarchar(20),@countA)
--   print  @TIMECARDDATE
--   print  @TIMECARDTIME
  --print convert(nvarchar(20),getdate(),20)

  --过滤非本次同步员工
  IF(@EmpNo<>'')
  BEGIN
    -- IF Exists(SELECT PersonID from PSNAccount Where Cardnum=@CardNum and EmployeeID in(@EmpNo))
    --IF Exists(SELECT PersonID from openquery(EHRORACLE,'select * from PSNACCOUNT')  Where Cardnum=@CardNum and EmployeeID in(@EmpNo))
    IF Exists(SELECT PersonID from openquery(EHRORACLE,'select cardnum,personid,EmployeeID,BRANCHID from PSNACCOUNT where accessionstate not in(4) and cardnum is not null ')  Where Cardnum=@CardNum and EmployeeID in(@EmpNo))  ---this line update 20141127

    BEGIN
      --SELECT @PersonID=PersonID,@DEPARTMENTID=BRANCHID from PSNAccount Where Cardnum=@CardNum
      --SELECT @PersonID=PersonID,@DEPARTMENTID=BRANCHID from openquery(EHRORACLE,'select * from PSNACCOUNT') Where Cardnum=@CardNum
       SELECT @PersonID=PersonID,@DEPARTMENTID=BRANCHID from openquery(EHRORACLE,'select cardnum,personid,EmployeeID,BRANCHID from PSNACCOUNT where accessionstate not in(4) and cardnum is not null') Where Cardnum=@CardNum   ---this line update 20141127
    
      IF(@PersonID<>'' AND @PersonID IS NOT NULL)   
      BEGIN
        --SELECT @MachineID=MachineID from ATDTIMECARDMACHINE where code=@DeviceNum
        SELECT @MachineID=MachineID from openquery(EHRORACLE,'select * from ATDTIMECARDMACHINE') where code=@DeviceNum

    
        --如果刷卡机尚未在HR系统中配置，则以刷卡机为默认刷卡机号
        IF(@MachineID='' or @MachineID is null) 
           --SELECT @MachineID=MachineID from ATDTIMECARDMACHINE where code='10'
           SELECT @MachineID=MachineID from openquery(EHRORACLE,'select * from ATDTIMECARDMACHINE') where code='10'
           
        --IF NOT Exists(Select Dataid from ATDTIMECARDDATA where PERSONID=@PersonID  and cast(Left(TIMECARDDATE,10) as datetime)=cast(@TIMECARDDATE as datetime) and Left(TIMECARDTIME,5)=Left(@TIMECARDTIME,5) )
        print convert(nvarchar(20),getdate(),20)
        IF NOT Exists(Select Dataid from openquery(EHRORACLE,'select * from ATDTIMECARDDATA WHERE TIMECARDDATE> ''2014-10-31''') where PERSONID=@PersonID  and cast(Left(TIMECARDDATE,10) as datetime)=cast(@TIMECARDDATE as datetime) and Left(TIMECARDTIME,5)=Left(@TIMECARDTIME,5) )
        BEGIN
           print '33333'
       
           INSERT INTO OPENQUERY(EHRORACLE,'SELECT PERSONID,DataID,TIMECARDDATE,TIMECARDTIME,RECEIVEDATE,MACHINEID,DEPARTMENTID,DATAFROM,BUSINESSUNITID FROM ATDTIMECARDDATA')
              SELECT @PersonID,newid(),@TIMECARDDATE,@TIMECARDTIME,convert(nvarchar(10),getdate(),120),@MachineID,@DEPARTMENTID,'1','0'
              
          --INSERT INTO ATDTIMECARDDATA(PERSONID,DataID,TIMECARDDATE,TIMECARDTIME,RECEIVEDATE,MACHINEID,DEPARTMENTID,DATAFROM,BUSINESSUNITID) VALUES(@PersonID,newid(),@TIMECARDDATE,@TIMECARDTIME,convert(nvarchar(10),getdate(),120),@MachineID,@DEPARTMENTID,'1','0')
         --select  'IN:'+@PersonID+' '+@DeviceNum+' '+@MachineID+' '+@Timecarddate+' '+@Timecardtime
          --小时非二位修正
         
            
          
            UPDATE OPENQUERY (EHRORACLE, 'SELECT * FROM ATDTIMECARDDATA WHERE TIMECARDDATE> ''2014-10-31''  ') 
                  SET timecardtime='0'+left(Timecardtime,1)+':'+substring(timecardtime,charindex(':',timecardtime)+1,2)+':'+right(timecardtime,2)
           WHERE charindex(':',left(Timecardtime,2))>0  and PERSONID=@PersonID and DEPARTMENTID=@DEPARTMENTID and cast(Left(TIMECARDDATE,10) as datetime)=cast(@TIMECARDDATE as datetime) and Left(TIMECARDTIME,5)=Left(@TIMECARDTIME,5) and MachineID=@MachineID
        

          --update ATDTIMECARDDATA 
          --    set  timecardtime='0'+left(Timecardtime,1)+':'+substring(timecardtime,charindex(':',timecardtime)+1,2)+':'+right(timecardtime,2)
          --    from  ATDTIMECARDDATA where charindex(':',left(Timecardtime,2))>0 
          --    and PERSONID=@PersonID and DEPARTMENTID=@DEPARTMENTID and cast(Left(TIMECARDDATE,10) as datetime)=cast(@TIMECARDDATE as datetime) and Left(TIMECARDTIME,5)=Left(@TIMECARDTIME,5) and MachineID=@MachineID
        END
        SET @MachineID=''
      END
    END
  END
  ELSE  
  
  BEGIN
    --SELECT @PersonID=PersonID,@DEPARTMENTID=BRANCHID from PSNAccount Where Cardnum=@CardNum
    --SELECT @PersonID=PersonID,@DEPARTMENTID=BRANCHID from openquery(EHRORACLE,'select * from PSNACCOUNT') Where Cardnum=@CardNum 
    SELECT @PersonID=PersonID,@DEPARTMENTID=BRANCHID from openquery(EHRORACLE,'select cardnum,personid,EmployeeID,BRANCHID from PSNACCOUNT where accessionstate not in(4) and cardnum is not null') Where Cardnum=@CardNum   ---this line update 20141127

    IF(@PersonID<>'' AND @PersonID IS NOT NULL)   
    BEGIN
      --SELECT @MachineID=MachineID from ATDTIMECARDMACHINE where code=@DeviceNum
      SELECT @MachineID=MachineID from openquery(EHRORACLE,'select * from ATDTIMECARDMACHINE') where code=@DeviceNum
--     print 'CardNum:'+@CardNum
       print 'PersonID:'+@PersonID +'....'++convert(nvarchar(20),getdate(),120)
--     print 'MachineID:'+@MachineID
      
       --如果刷卡机尚未在HR系统中配置，则以刷卡机为默认刷卡机号
      IF(@MachineID='' or @MachineID is null) 
      BEGIN
         --SELECT @MachineID=MachineID from ATDTIMECARDMACHINE where code='10'
         SELECT @MachineID=MachineID from openquery(EHRORACLE,'select * from ATDTIMECARDMACHINE') where code='10'
      END

      --IF NOT Exists(Select Dataid from ATDTIMECARDDATA where PERSONID=@PersonID and cast(Timecarddate+' '+TimecardTime as datetime)=cast(@Timecarddate+' '+@Timecardtime as datetime))
      --IF NOT Exists(Select Dataid from openquery(EHRORACLE,'select * from ATDTIMECARDDATA') where PERSONID=@PersonID  and cast(Left(TIMECARDDATE,10) as datetime)=cast(@TIMECARDDATE as datetime) and Left(TIMECARDTIME,5)=Left(@TIMECARDTIME,5) )
      --IF NOT Exists(Select Dataid from openquery(EHRORACLE,'select * from ATDTIMECARDDATA WHERE TIMECARDDATE> ''2014-10-31''') where PERSONID=@PersonID  and cast(Left(TIMECARDDATE,10) as datetime)=cast(@TIMECARDDATE as datetime) and Left(TIMECARDTIME,5)=Left(@TIMECARDTIME,5) )  ---this line update 20141127
       
       DECLARE @COUNT INT
       Select @COUNT=COUNT(*) from openquery(EHRORACLE,'select * from ATDTIMECARDDATA WHERE TIMECARDDATE> ''2014-10-31''') where PERSONID=@PersonID  and cast(Left(TIMECARDDATE,10) as datetime)=cast(@TIMECARDDATE as datetime) and Left(TIMECARDTIME,5)=Left(@TIMECARDTIME,5)   ---this line update 20141127
--     PRINT  @TIMECARDDATE
--     PRINT  @TIMECARDTIME
--     PRINT 'COUNT:'+CONVERT(NVARCHAR(10),@COUNT)
       IF @COUNT=0
        BEGIN
          --INSERT INTO ATDTIMECARDDATA(PERSONID,DataID,TIMECARDDATE,TIMECARDTIME,RECEIVEDATE,MACHINEID,DEPARTMENTID,DATAFROM,BUSINESSUNITID) VALUES(@PersonID,newid(),@TIMECARDDATE,@TIMECARDTIME,convert(nvarchar(10),getdate(),120),@MachineID,@DEPARTMENTID,'1','0')
           print 'Insert Start!'+convert(nvarchar(20),getdate(),120)
           
           INSERT INTO OPENQUERY(EHRORACLE,'SELECT PERSONID,DataID,TIMECARDDATE,TIMECARDTIME,RECEIVEDATE,MACHINEID,DEPARTMENTID,DATAFROM,BUSINESSUNITID FROM ATDTIMECARDDATA')
              SELECT @PersonID,newid(),@TIMECARDDATE,@TIMECARDTIME,convert(nvarchar(10),getdate(),120),@MachineID,@DEPARTMENTID,'1','0'
           print 'Insert Success'+convert(nvarchar(20),getdate(),120)
            ------this line update 20141127
--         UPDATE OPENQUERY (EHRORACLE, 'SELECT * FROM ATDTIMECARDDATA WHERE TIMECARDDATE> ''2014-10-31'' ') 
--                SET timecardtime='0'+left(Timecardtime,1)+':'+substring(timecardtime,charindex(':',timecardtime)+1,2)+':'+right(timecardtime,2)   
--         WHERE charindex(':',left(Timecardtime,2))>0  and PERSONID=@PersonID and DEPARTMENTID=@DEPARTMENTID and cast(Left(TIMECARDDATE,10) as datetime)=cast(@TIMECARDDATE as datetime) and Left(TIMECARDTIME,5)=Left(@TIMECARDTIME,5) and MachineID=@MachineID
--         
         

          --小时非二位修正
          --update ATDTIMECARDDATA 
          --    set  timecardtime='0'+left(Timecardtime,1)+':'+substring(timecardtime,charindex(':',timecardtime)+1,2)+':'+right(timecardtime,2)
          --    from  ATDTIMECARDDATA where charindex(':',left(Timecardtime,2))>0 
          --    and PERSONID=@PersonID and DEPARTMENTID=@DEPARTMENTID and cast(Left(TIMECARDDATE,10) as datetime)=cast(@TIMECARDDATE as datetime) and Left(TIMECARDTIME,5)=Left(@TIMECARDTIME,5) and MachineID=@MachineID

      END
      SET @MachineID=''
    END
  END
  
  SELECT @countA=@countA-1
  SELECT @CardNum='',@DeviceNum='',@TIMECARDDATE='',@TIMECARDTIME='',@MachineID='',@PersonID='',@DEPARTMENTID=''
  FETCH  curGetData into @CardNum,@DeviceNum,@TIMECARDDATE,@TIMECARDTIME
end
CLOSE curGetData
Deallocate curGetData
END
试试其它关键字
同语言下
可能有用的
晋鹏贡献的其它代码(4)