SqlServer中游标的使用

 

select * from stuInfo--声明游标declare stuinfoCur cursor scroll for select * from stuinfodeclare @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,@stuaddresswhile @@FETCH_STATUS=0beginprint '--------------------'print @stunameprint @stunoprint @stuageprint @stuidprint @stuseatprint @stuaddressprint '--------------------'fetch next from stuinfoCur into @stuname,@stuno,@stuage,@stuid,@stuseat,@stuaddressend--关闭游标close stuinfoCurgo-- 释放游标deallocate stuinfoCurif exists(select * from sys.objects where name='PROC_UP_MARKS' and type='P')drop proc PROC_UP_MARKSgocreate proc PROC_UP_MARKSasbegindeclare stucur cursor for select stuno,stuage from stuinfodeclare @no varchar(20),@age intopen stucurfetch next from stucur into @no,@agewhile @@FETCH_STATUS=0beginif @age>=20beginif (select writtenExam from stuMarks where stuNo=@no)<80beginupdate stuMarks set writtenExam=writtenExam+5 where stuNo=@noendif (select labExam from stuMarks where stuNo=@no)<80beginupdate stuMarks set LabExam=LabExam+5 where stuNo=@noendendelsebeginif (select writtenExam from stuMarks where stuNo=@no)<80beginupdate stuMarks set writtenExam=writtenExam+10 where stuNo=@noendif (select labExam from stuMarks where stuNo=@no)<80beginupdate stuMarks set LabExam=LabExam+10 where stuNo=@noendendfetch next from stucur into @no,@ageendclose stucurdeallocate stucurendexec proc_up_marksselect * from stuInfo--利用游标更新数据declare stucur cursor scroll for select * from stuinfo for update of stuageopen stucurfetch first from stucurwhile @@FETCH_STATUS=0beginupdate stuInfo set stuAge=stuAge+1 where current of stucurfetch next from stucurendclose stucurdeallocate stucur--游标变量declare @cur cursordeclare stucur cursor for select * from stuinfoset @cur=stucuropen @curfetch next from @curwhile @@FETCH_STATUS=0beginfetch next from @curendclose @curdeallocate @curprint '*********************************'open stucurfetch next from stucurwhile @@FETCH_STATUS=0beginfetch next from stucurendclose stucurdeallocate stucurgo