SqlServer中游标的使用

最新推荐文章于 2025-04-24 09:26:29 发布

iteye_6794

最新推荐文章于 2025-04-24 09:26:29 发布

阅读量105

点赞数

文章标签：数据库

本文深入探讨了SQL查询中游标的使用方法，包括如何声明、打开、提取数据、关闭和释放游标，以及利用游标进行数据更新。通过实例展示了如何在数据库操作中灵活运用游标，提高数据处理效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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