游标的作用总结

概念：游标是数据库系统用来供用户标志一条指定记录的指针，它可以是变动的，但每次只指向一条记录。

使用示例：
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