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
