示例
A. 使用简单游标和语法
打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标,对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项,FETCH NEXT 是唯一可用的提取选项。DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursorB. 使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。SET NOCOUNT ONDECLARE @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_idOPEN authors_cursorFETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lnameWHILE @@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 " <<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
ENDCLOSE 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
A. 使用简单游标和语法
打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标,对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项,FETCH NEXT 是唯一可用的提取选项。DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursorB. 使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。SET NOCOUNT ONDECLARE @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_idOPEN authors_cursorFETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lnameWHILE @@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 " <<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
ENDCLOSE 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
A. 在简单的游标中使用 FETCH
下例为 authors 表中姓以字母 B 开头的行声明了一个简单的游标,并使用 FETCH NEXT 逐个提取这些行。FETCH 语句以单行结果集形式返回由 DECLARE CURSOR 指定的列的值。USE pubs
GO
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
WHERE au_lname LIKE 'B%'
ORDER BY au_lnameOPEN authors_cursor-- Perform the first fetch.
FETCH NEXT FROM authors_cursor-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
ENDCLOSE authors_cursor
DEALLOCATE authors_cursor
GOau_lname
----------------------------------------
Bennet
au_lname
----------------------------------------
Blotchet-Halls
au_lname
----------------------------------------B. 使用 FETCH 将值存入变量
下例与上例相似,但 FETCH 语句的输出存储于局部变量而不是直接返回给客户端。PRINT 语句将变量组合成单一字符串并将其返回到客户端。USE pubs
GO-- Declare the variables to store the values returned by FETCH.
DECLARE @au_lname varchar(40), @au_fname varchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE 'B%'
ORDER BY au_lname, au_fnameOPEN authors_cursor-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN -- Concatenate and display the current values in the variables.
PRINT 'Author: ' + @au_fname + ' ' + @au_lname -- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
ENDCLOSE authors_cursor
DEALLOCATE authors_cursor
GOAuthor: Abraham Bennet
Author: Reginald Blotchet-HallsC. 声明 SCROLL 游标并使用其它 FETCH 选项
下例创建一个 SCROLL 游标,使其通过 LAST、PRIOR、RELATIVE 和 ABSOLUTE 选项支持所有滚动能力。USE pubs
GO-- Execute the SELECT statement alone to show the
-- full result set that is used by the cursor.
SELECT au_lname, au_fname FROM authors
ORDER BY au_lname, au_fname-- Declare the cursor.
DECLARE authors_cursor SCROLL CURSOR FOR
SELECT au_lname, au_fname FROM authors
ORDER BY au_lname, au_fnameOPEN authors_cursor-- Fetch the last row in the cursor.
FETCH LAST FROM authors_cursor-- Fetch the row immediately prior to the current row in the cursor.
FETCH PRIOR FROM authors_cursor-- Fetch the second row in the cursor.
FETCH ABSOLUTE 2 FROM authors_cursor-- Fetch the row that is three rows after the current row.
FETCH RELATIVE 3 FROM authors_cursor-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM authors_cursorCLOSE authors_cursor
DEALLOCATE authors_cursor
GOau_lname au_fname
---------------------------------------- --------------------
Bennet Abraham
Blotchet-Halls Reginald
Carson Cheryl
DeFrance Michel
del Castillo Innes
Dull Ann
Green Marjorie
Greene Morningstar
Gringlesby Burt
Hunter Sheryl
Karsen Livia
Locksley Charlene
MacFeather Stearns
McBadden Heather
O'Leary Michael
Panteley Sylvia
Ringer Albert
Ringer Anne
Smith Meander
Straight Dean
Stringer Dirk
White Johnson
Yokomoto Akiko au_lname au_fname
---------------------------------------- --------------------
Yokomoto Akiko
au_lname au_fname
---------------------------------------- --------------------
White Johnson
au_lname au_fname
---------------------------------------- --------------------
Blotchet-Halls Reginald
au_lname au_fname
---------------------------------------- --------------------
del Castillo Innes
au_lname au_fname
---------------------------------------- --------------------
Carson Cheryl