在此之前,曾看过大神们对游标的一些看法,即游标其实就像C语言中的指针一样,对于很多人来说,使用游标非常之不好用。在此,我只写一下两个使用游标的简单例子。
1、使用游标进行查询数据库记录。这里是查询作者表的一些信息:
go
declare @auid char(12),@aulname varchar(20), @age int,@sex varchar(2)
declare auth_cur cursor for
select Aid, authorname, age, sex
from author
open auth_cur
fetch next from auth_cur into @auid,@aulname,@age, @sex
while (@@fetch_status=0)
begin
if @age<18
begin
print '作者编号: '+@auid
print '作者姓名: '+@aulname
print '所年龄: '+(cast(@age as nvarchar))
print '性别:'+@sex
end
fetch next from auth_cur into @auid,@aulname,@age, @sex
end
close auth_cur
deallocate auth_cur
2、使用游标更新数据、删除数据
go
declare @aid int,@authorname nvarchar(50),@age int,@sex nvarchar(2)
declare curs_delete_update_author cursor
for select aid,authorname,age,sex from author
open curs_delete_update_author
fetch curs_delete_update_author into @aid,@authorname ,@age,@sex
while @@FETCH_STATUS=0
begin
if @aid>20
begin
update author
set age=60
where AId=@aid --current of curs_delete_update_author;
end
if @aid>20 and @aid<30
begin
delete from author
where AId=@aid
end
fetch next from curs_delete_update_author into @aid,@authorname ,@age,@sex
end
close curs_delete_update_author
deallocate curs_delete_update_author