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



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值