[帮助]SQL SERVER存储过程中操作记录集

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_lname
OPEN 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
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO
au_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_fname
OPEN 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
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO
Author: Abraham Bennet
Author: Reginald Blotchet-Halls

C. 声明 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_fname
OPEN 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_cursor
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO
au_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