SQL 游标使用实例
IF EXISTS(SELECT *FROM sysobjects WHERE name='sp_ContestSubmit') DROP PROC sp_ContestSubmit GO -- ============================================= -- Author: zqt -- Create date: 2011-11-25 -- Desc: 系统管理员批量给未交卷的考生交卷-竞赛 -- ============================================= Create proc sp_ContestSubmit @GroupID int --考核ID as --申明一个游标 DECLARE MyCursor CURSOR FOR select PK_UserExamID,FK_UserID,FK_ExamOrContestID from Score_UserExam where IsComplete=0 AND FK_ExamOrContestID in(select PK_ContestInfoID from Contest_Info where FK_ContestGroupID=@GroupID) --打开一个游标 OPEN MyCursor --循环一个游标 DECLARE @UserExamID int ,@UserID int ,@ExamManageID int FETCH NEXT FROM MyCursor INTO @UserExamID, @UserID,@ExamManageID WHILE @@FETCH_STATUS =0 BEGIN --获取考试考试分数 DECLARE @UserExamScore int select @UserExamScore=sum(Score) from Score_UserAnswer where FK_UserID=@UserID AND FK_UserExamID=@UserExamID --修改考生交卷信息 UPDATE [Score_UserExam] SET [Score] =@UserExamScore,[IsComplete] = 1,[EndTime] = getdate(),[DurationSecs] = datediff(ss,BeginTime,getdate()) where PK_UserExamID=@UserExamID FETCH NEXT FROM MyCursor INTO @UserExamID, @UserID,@ExamManageID END --关闭游标 CLOSE MyCursor --释放资源 DEALLOCATE MyCursor /* 测试 exec sp_ContestSubmit 1 */ GO