SqlServer中游标的使用

select * from stuInfo --声明游标 declare stuinfoCur cursor scroll for select * from stuinfo declare @stuname varchar(20),@stuno varchar(20),@stuage int,@stuid varchar(20),@stuseat int,@stuaddress varchar(50) --打开游标 open stuinfoCur --提取数据 fetch next from stuinfoCur into @stuname,@stuno,@stuage,@stuid,@stuseat,@stuaddress while @@FETCH_STATUS=0 begin print '--------------------' print @stuname print @stuno print @stuage print @stuid print @stuseat print @stuaddress print '--------------------' fetch next from stuinfoCur into @stuname,@stuno,@stuage,@stuid,@stuseat,@stuaddress end --关闭游标 close stuinfoCur go -- 释放游标 deallocate stuinfoCur if exists(select * from sys.objects where name='PROC_UP_MARKS' and type='P') drop proc PROC_UP_MARKS go create proc PROC_UP_MARKS as begin declare stucur cursor for select stuno,stuage from stuinfo declare @no varchar(20),@age int open stucur fetch next from stucur into @no,@age while @@FETCH_STATUS=0 begin if @age>=20 begin if (select writtenExam from stuMarks where stuNo=@no)<80 begin update stuMarks set writtenExam=writtenExam+5 where stuNo=@no end if (select labExam from stuMarks where stuNo=@no)<80 begin update stuMarks set LabExam=LabExam+5 where stuNo=@no end end else begin if (select writtenExam from stuMarks where stuNo=@no)<80 begin update stuMarks set writtenExam=writtenExam+10 where stuNo=@no end if (select labExam from stuMarks where stuNo=@no)<80 begin update stuMarks set LabExam=LabExam+10 where stuNo=@no end end fetch next from stucur into @no,@age end close stucur deallocate stucur end exec proc_up_marks select * from stuInfo --利用游标更新数据 declare stucur cursor scroll for select * from stuinfo for update of stuage open stucur fetch first from stucur while @@FETCH_STATUS=0 begin update stuInfo set stuAge=stuAge+1 where current of stucur fetch next from stucur end close stucur deallocate stucur --游标变量 declare @cur cursor declare stucur cursor for select * from stuinfo set @cur=stucur open @cur fetch next from @cur while @@FETCH_STATUS=0 begin fetch next from @cur end close @cur deallocate @cur print '*********************************' open stucur fetch next from stucur while @@FETCH_STATUS=0 begin fetch next from stucur end close stucur deallocate stucur go