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

?????? 在基于微软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,duration,enddate,DEGREEPERCENT,score from USERLESSONS where 1=0'; set @sql = 'INSERT openquery(ORASVR,'''+@query+''')'+ ' values('+convert(varchar,@LessonCode)+','''+convert(varchar,@Year)+ ''',@userId)+''',@status)+''',@Duration) +''',@StartDate)+''',isnull(@pct,0))+''',isnull(@Score,0))+''')'; end else begin set @query = 'select lessoncode,startdate,score from USERLESSONS where 1=0'; set @sql = 'INSERT openquery(ORASVR,@Year)+ ''',@Duration)+''',@EndDate) +''',0))+''')'; end end else begin if(@EndDate = 0 or @EndDate is null) begin set @query = 'select lessoncode,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,duration='''+convert(varchar,@Duration)+ ''',startdate='''+convert(varchar,@StartDate)+ ''',score='+convert(varchar,0))+',DEGREEPERCENT='+convert(varchar,0)); end else begin set @query = 'select lessoncode,enddate='''+convert(varchar,0)+''',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,@sql); delete from dbo.Temp_AttemptItem where deleteid=@deleteId; --end FETCH next from userl_cursor into @deleteId,@pct; END --insert into temp(id,@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,DtsSeqNo,UserId,FinishChk,LearningDate from dbo.UserLessonDts_View; open userLessonDts_cursor; FETCH next from userLessonDts_cursor into @deleteId,@DtsSeqNo,@UserId,@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,@UserId)+''''''; set @sql = N'select @isExits1=LESSONCODE from openquery(ORASVR,'''+@query+''')'; exec sp_executesql @sql,@isExits1=@isExits out; -- print @sql; select @ScormLessonID,@LearningDate,@isExits if(@isExits = 0 or @isExits is null) begin set @query = 'select lessoncode,dtsseqno,finishchk,learningdate from USERLESSONDTS where 1=0'; -- print @query; set @sql = 'INSERT openquery(ORASVR,@Year)+ ''',@UserId)+''',@DtsSeqNo)+''',@FinishChk)+''',@LearningDate)+''')'; end else begin set @query = 'select lessoncode,learningdate from USERLESSONDTS where LESSONCODE='+convert(varchar,@UserId)+''''''; set @sql = 'UPDATE openquery(ORASVR,'''+@query+''') set finishchk='''+convert(varchar,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,@LearningDate; END close userLessonDts_cursor; DEALLOCATE userLessonDts_cursor; lberr: SET ANSI_NULLS OFF GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!