学习CURSOR之我见！！！

最新推荐文章于 2025-08-05 08:46:02 发布

原创最新推荐文章于 2025-08-05 08:46:02 发布 · 964 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#books #report #报表 #go

本文通过一个具体的SQL示例介绍了如何使用嵌套游标来生成报表输出，特别是针对数据库中特定地区作者及其作品的详细列表。示例展示了外层游标用于遍历作者，而内层游标则用于获取每位作者的作品。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

B. 使用嵌套游标生成报表输出

下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。

SET NOCOUNT ON

DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
   @message varchar(80), @title varchar(80)

PRINT "-------- Utah Authors report --------"

DECLARE authors_cursor CURSOR FOR 
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor 
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT " "
   SELECT @message = "----- Books by Author: " + 
      @au_fname + " " + @au_lname

   PRINT @message

   -- Declare an inner cursor based   
   -- on au_id from the outer cursor.

   DECLARE titles_cursor CURSOR FOR 
   SELECT t.title
   FROM titleauthor ta, titles t
   WHERE ta.title_id = t.title_id AND
   ta.au_id = @au_id   -- Variable value from the outer cursor -----利用上面CURSOR的值

   OPEN titles_cursor
   FETCH NEXT FROM titles_cursor INTO @title

   IF @@FETCH_STATUS <> 0 
      PRINT "         <<No Books>>"     

   WHILE @@FETCH_STATUS = 0
   BEGIN
      
      SELECT @message = "         " + @title
      PRINT @message
      FETCH NEXT FROM titles_cursor INTO @title
   
   END

   CLOSE titles_cursor
   DEALLOCATE titles_cursor
   
   -- Get the next author.
   FETCH NEXT FROM authors_cursor 
   INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

-------- Utah Authors report --------
 
----- Books by Author: Anne Ringer
         The Gourmet Microwave
         Is Anger the Enemy?
 
----- Books by Author: Albert Ringer
         Is Anger the Enemy?
         Life Without Fear