T-SQL（一）游标嵌套查询

数据源
pubs 示例数据库

目标
利用游标用于生成作者出版情况报表，形式如下：

-------- Authors report --------
 
----- Books by Author: AuthorName1
         BookName1
         BookName2
 
----- Books by Author: AuthorName2
         BookName1
         BookName2

实现

-- 使用游标

begin

  print('-------- Authors report --------')

  -- 声明存储作家信息的变量
  declare @au_name varchar(20), @au_lname varchar(20), @au_fname varchar(20),
    @au_id varchar(20)

  -- 声明作家游标
  declare c_author_name cursor
  for select au_lname, au_fname from authors
  declare c_author_id cursor
  for select au_id from authors

  -- 打开作家游标
  open c_author_name
  open c_author_id

  -- 重置全局变量 @@fetch_status
  fetch next from c_author_id into @au_id
  fetch next from c_author_name into @au_lname, @au_fname

  -- 作家层循环
  while @@fetch_status = 0
    begin

        -- 拼接作家名称
        set @au_name = @au_lname + ' ' + @au_fname

        print '----- Books by Author:' + @au_name

        -- 书号
        declare @book_id varchar(20)

        -- 书号游标
        declare c_book_id cursor
        for select title_id from titleauthor where au_id = @au_id

        open c_book_id

        fetch next from c_book_id into @book_id

        -- 书号层循环
        while @@fetch_status = 0

          begin

            -- 书名
            declare @book_name varchar(20)

            -- 书名游标
            declare book_name cursor
            for select title from titles where title_id = @book_id

            open book_name

            fetch from book_name into @book_name

            print @book_name

            close book_name

            deallocate book_name

            fetch next from c_book_id into @book_id

          end

        close c_book_id

        deallocate c_book_id

        fetch next from c_author_id into @au_id
        fetch next from c_author_name into @au_lname, @au_fname

    end

  -- 关闭作家游标
  close c_author_id
  close c_author_name

  -- 释放作家游标
  deallocate c_author_id
  deallocate c_author_name

end


-- 不使用游标
select au_lname, au_fname, title
from authors, titleauthor, titles
where authors.au_id = titleauthor.au_id and titles.title_id = titleauthor.title_id