使用游标显示销售报表_使用嵌套游标生成复杂的报表

A. 使用简单游标和语法

打开该游标时所生成的结果集包括 pubs 数据库的 authors表中的所有行和列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项，FETCH NEXT 是唯一可用的提取选项。DECLARE authors_cursor CURSOR

FOR SELECT * FROM authors

OPEN authors_cursor

FETCH NEXT FROM authors_cursor

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

OPEN titles_cursor

FETCH NEXT FROM titles_cursor INTO @title

IF @@FETCH_STATUS <> 0

PRINT " <>"

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