基于linked server的scorm课件播放器数据同步从sqlserver->oracle

       在基于微软scorm平台的开发过程中，由于用户系统数据库为oracle，因此需要将存储在sqlserver中的scorm学习记录，转存到oracle数据库中，采用sqlserver linked server技术，可以直接从oracle数据库中读取或者向oracle中存储数据。这样用户的学习记

录就可以很快捷的向用户系统同步，且不需要编写额外的程序或者服务，只需要将上述存储过程在sqlserver中配置为job运行即可，存

储过程中包含了对oracle的update、select、insert等技术元素，可以供用户学习使用。

 

存储过程1：

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[OperateUserLessionData] AS declare @srvr nvarchar(128), @retval int; set @srvr = 'ORASVR'; begin try exec @retval = sys.sp_testlinkedserver @srvr; end try begin catch set @retval = sign(@@error); end catch; if @retval <> 0 goto lberr declare @deleteId int; declare @ScormLessonID bigint; declare @Year nvarchar(30); declare @userId nvarchar(250); declare @status varchar(1); declare @Duration int; declare @IsEnd int; declare @StartDate datetime; declare @EndDate datetime; declare @Score real; declare @LessonCode int; declare @pct int; declare @isExits int; declare userl_cursor CURSOR FOR select deleteid,ScormLessonID,Year,userId, status,Duration,IsEnd,StartDate,EndDate ,Score,pct from dbo.UserLesson_View; open userl_cursor; FETCH next from userl_cursor into @deleteId,@ScormLessonID,@Year,@userId,@status,@Duration,@IsEnd,@StartDate ,@EndDate,@Score,@pct; if @@FETCH_STATUS <>0 goto lberr while @@FETCH_STATUS = 0 BEGIN declare @query varchar(500); declare @sql Nvarchar(500); set @isExits=0 set @query = 'select LESSONCODE from LESSONS where SCORMLESSONID='+convert(varchar,@ScormLessonID); -- print @query; set @sql = N'select @LessonCode1=lessoncode from openquery(ORASVR,'''+@query+''')'; -- print @sql; exec sp_executesql @sql, N'@LessonCode1 int out',@LessonCode1=@LessonCode out; --判断用户课程信息表记录是否存在 set @query = 'select LESSONCODE from USERLESSONS where LESSONCODE='+convert(varchar,@LessonCode)+' and year='+convert(varchar,@Year)+' and userid='''''+convert(varchar,@userId)+''''''; set @sql = N'select @isExits1=LESSONCODE from openquery(ORASVR,'''+@query+''')'; print @sql; exec sp_executesql @sql, N'@isExits1 int out',@isExits1=@isExits out; -- if(@IsEnd is null or @IsEnd = 0) -- begin -- set @Duration = null; -- set @EndDate = null; -- end -- print @isExits; declare @temp Nvarchar(500); select @LessonCode as lesscode,@Year as year,@userId as id,@status as status,@Duration as dura,@StartDate as stime,@EndDate as edate,@Score as score,@deleteId,@pct as pct,@isExits as isexits; if(@isExits = 0 or @isExits is null) begin if(@EndDate = 0 or @EndDate is null) begin set @query = 'select lessoncode,year,userid,status,duration,enddate,DEGREEPERCENT,score from USERLESSONS where 1=0'; set @sql = 'INSERT openquery(ORASVR,'''+@query+''')'+ ' values('+convert(varchar,@LessonCode)+','''+convert(varchar,@Year)+ ''','''+convert(varchar,@userId)+''','''+convert(varchar,@status)+''','''+convert(varchar,@Duration) +''','''+convert(varchar,@StartDate)+''','''+convert(varchar,isnull(@pct,0))+''','''+convert(varchar,isnull(@Score,0))+''')'; end else begin set @query = 'select lessoncode,year,userid,status,duration,startdate,enddate,DEGREEPERCENT,score from USERLESSONS where 1=0'; set @sql = 'INSERT openquery(ORASVR,'''+@query+''')'+ ' values('+convert(varchar,@LessonCode)+','''+convert(varchar,@Year)+ ''','''+convert(varchar,@userId)+''','''+convert(varchar,@status)+''','''+convert(varchar,@Duration)+''','''+convert(varchar,@StartDate)+''','''+convert(varchar,@EndDate) +''','''+convert(varchar,isnull(@pct,0))+''','''+convert(varchar,isnull(@Score,0))+''')'; end end else begin if(@EndDate = 0 or @EndDate is null) begin set @query = 'select lessoncode,year,userid,status,duration,startdate,score,DEGREEPERCENT from USERLESSONS where LESSONCODE='+convert(varchar,@LessonCode)+' and year='++convert(varchar,@Year)+' and userid='+convert(varchar,@userId); set @sql = 'UPDATE openquery(ORASVR,'''+@query+''') set status='''+convert(varchar,@status)+''',duration='''+convert(varchar,@Duration)+ ''',startdate='''+convert(varchar,@StartDate)+ ''',score='+convert(varchar,isnull(@Score,0))+',DEGREEPERCENT='+convert(varchar,isnull(@pct,0)); end else begin set @query = 'select lessoncode,year,userid,status,duration,startdate,enddate,score,DEGREEPERCENT from USERLESSONS where LESSONCODE='+convert(varchar,@LessonCode)+' and year='++convert(varchar,@Year)+' and userid='+convert(varchar,@userId); set @sql = 'UPDATE openquery(ORASVR,'''+@query+''') set status='''+convert(varchar,@status)+''',duration='''+convert(varchar,@Duration)+ ''',startdate='''+convert(varchar,@StartDate)+ ''',enddate='''+convert(varchar,@EndDate,0)+''',score='+convert(varchar,isnull(@Score,0))+',DEGREEPERCENT='+convert(varchar,isnull(@pct,0)); end end print @query; print @sql; insert into temp(id,date,sql) values (1,getdate(),@sql); exec(@sql); -- if @@ERROR =0 begin --insert into temp(id,date,sql) values (1,getdate(),@sql); delete from dbo.Temp_AttemptItem where deleteid=@deleteId; --end FETCH next from userl_cursor into @deleteId,@ScormLessonID,@Year,@userId,@status,@Duration,@IsEnd,@StartDate ,@EndDate,@Score,@pct; END --insert into temp(id,date,sql) values (1,getdate(),@sql); --exec dbo.OperateData lberr: close userl_cursor; DEALLOCATE userl_cursor; SET ANSI_NULLS OFF GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO

 

存储过程2：

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[OperateData] AS declare @srvr nvarchar(128), @retval int; set @srvr = 'ORASVR'; begin try exec @retval = sys.sp_testlinkedserver @srvr; end try begin catch set @retval = sign(@@error); end catch; if @retval <> 0 goto lberr declare @deleteId int; declare @ScormLessonID bigint; declare @DtsSeqNo bigint; declare @UserId nvarchar(250); declare @Year nvarchar(30); declare @FinishChk int; declare @LearningDate datetime; declare @isExits int; declare @LessonCode int; declare userLessonDts_cursor CURSOR FOR select deleteid,ScormLessonID,DtsSeqNo, UserId,Year,FinishChk,LearningDate from dbo.UserLessonDts_View; open userLessonDts_cursor; FETCH next from userLessonDts_cursor into @deleteId,@ScormLessonID,@DtsSeqNo,@UserId,@Year,@FinishChk,@LearningDate; while @@FETCH_STATUS = 0 BEGIN declare @query varchar(500); declare @sql nvarchar(500); set @isExits=0 set @query = 'select LESSONCODE from LESSONS where SCORMLESSONID='+convert(varchar,@ScormLessonID); set @sql = N'select @isExits2=LESSONCODE from openquery(ORASVR,'''+@query+''')'; exec sp_executesql @sql, N'@isExits2 int out',@isExits2=@LessonCode out; --判断用户课程明细表记录是否存在 set @query = 'select LESSONCODE from USERLESSONDTS where LESSONCODE='+convert(varchar,@LessonCode)+' and dtsseqno='+convert(varchar,@DtsSeqNo) +' and year='+convert(varchar,@Year)+' and userid='''''+convert(varchar,@UserId)+''''''; set @sql = N'select @isExits1=LESSONCODE from openquery(ORASVR,'''+@query+''')'; exec sp_executesql @sql, N'@isExits1 int out',@isExits1=@isExits out; -- print @sql; select @ScormLessonID,@Year,@UserId,@DtsSeqNo,@FinishChk,@LearningDate,@deleteId,@isExits if(@isExits = 0 or @isExits is null) begin set @query = 'select lessoncode,year,userid,dtsseqno,finishchk,learningdate from USERLESSONDTS where 1=0'; -- print @query; set @sql = 'INSERT openquery(ORASVR,'''+@query+''')'+ ' values('+convert(varchar,@LessonCode)+','''+convert(varchar,@Year)+ ''','''+convert(varchar,@UserId)+''','''+convert(varchar,@DtsSeqNo)+''','''+convert(varchar,@FinishChk)+''','''+convert(varchar,@LearningDate)+''')'; end else begin set @query = 'select lessoncode,year,userid,dtsseqno,finishchk,learningdate from USERLESSONDTS where LESSONCODE='+convert(varchar,@LessonCode)+' and dtsseqno='+convert(varchar,@DtsSeqNo) +' and year='+convert(varchar,@Year)+' and userid='''''+convert(varchar,@UserId)+''''''; set @sql = 'UPDATE openquery(ORASVR,'''+@query+''') set finishchk='''+convert(varchar,@FinishChk)+''',learningdate='''+convert(varchar,@LearningDate)+''''; end print @sql; exec(@sql); -- if @@ERROR =0 begin delete from dbo.Temp_ActivityAttemptItem where deleteid = @deleteId; -- -- end FETCH next from userLessonDts_cursor into @deleteId,@ScormLessonID,@DtsSeqNo,@UserId,@Year,@FinishChk,@LearningDate; END close userLessonDts_cursor; DEALLOCATE userLessonDts_cursor; lberr: SET ANSI_NULLS OFF GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO