基于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 ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[OperateUserLessionData]ASdeclare @srvr nvarchar(128), @retval int;set @srvr = 'ORASVR';begin tryexec @retval = sys.sp_testlinkedserver @srvr;end trybegin catchset @retval = sign(@@error);end catch;if @retval <> 0 goto lberrdeclare @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 = 0BEGINdeclare @query varchar(500);declare @sql Nvarchar(500); set @isExits=0set @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)beginif(@EndDate = 0 or @EndDate is null)beginset @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))+''')';endelsebeginset @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))+''')'; endendelsebeginif(@EndDate = 0 or @EndDate is null)beginset @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));endelsebeginset @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));endendprint @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 OFFGOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO

 

存储过程2：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[OperateData]ASdeclare @srvr nvarchar(128), @retval int;set @srvr = 'ORASVR';begin tryexec @retval = sys.sp_testlinkedserver @srvr;end trybegin catchset @retval = sign(@@error);end catch;if @retval <> 0 goto lberrdeclare @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 = 0BEGINdeclare @query varchar(500);declare @sql nvarchar(500); set @isExits=0set @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,@isExitsif(@isExits = 0 or @isExits is null)beginset @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)+''')';endelsebeginset @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)+'''';endprint @sql;exec(@sql);-- if @@ERROR =0 begindelete from dbo.Temp_ActivityAttemptItem where deleteid = @deleteId;-- --end FETCH next from userLessonDts_cursor into @deleteId,@ScormLessonID,@DtsSeqNo,@UserId,@Year,@FinishChk,@LearningDate;ENDclose userLessonDts_cursor;DEALLOCATE userLessonDts_cursor;lberr:SET ANSI_NULLS OFFGOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO