Transact-SQL的游标实例

print '------------- Authors report ---------------'
print ''
--定义authors表的游标
declare author_cursor cursor for
select au_id,au_fname,au_lname
from authors
--声明变量
declare @au_id varchar(100),@au_fname varchar(100),@au_lname varchar(100)
open author_cursor
fetch next from author_cursor into @au_id,@au_fname,@au_lname
--对authors表项进行循环检查每个作者
while @@FETCH_STATUS=0
begin

--打印格式要求的字符串
print ''
print '--------- Books by Author: '+@au_lname+' '+@au_fname
declare @title_id varchar(100)
--定义titleauthor表的游标
declare titleauthor_cursor scroll cursor for
select title_id
from titleauthor
where au_id=@au_id
open titleauthor_cursor
fetch next from titleauthor_cursor into @title_id
--对当前作者的titleauthor表项进行遍历
while @@FETCH_STATUS=0
begin


declare @title varchar(100),@pubdate varchar(100)
--定义titles表的游标
declare titles_cursor scroll cursor for
select title,pubdate
from titles
where title_id=@title_id
open titles_cursor
fetch next from titles_cursor into @title,@pubdate
--对相应title_id的title表项进行遍历，这里其实可以去除这一层循环
while @@FETCH_STATUS=0
begin
--打印格式输出，包括书籍书目和出版时间
print '      '+@title+'  '+@pubdate
fetch next from titles_cursor into @title,@pubdate

end
close titles_cursor
deallocate titles_cursor
fetch next from titleauthor_cursor into @title_id


end
close titleauthor_cursor
deallocate titleauthor_cursor
fetch next from author_cursor into @au_id,@au_fname,@au_lname



end
close author_cursor
deallocate author_cursor