[協助]SQL SERVER預存程序中操作記錄集

A. 在簡單的遊標中使用 FETCH

下例為 authors 表中姓以字母 B 開頭的行聲明了一個簡單的遊標，並使用 FETCH NEXT 逐個提取這些行。FETCH 語句以單行結果集形式返回由 DECLARE CURSOR 指定的列的值。

USE pubsGODECLARE authors_cursor CURSOR FORSELECT au_lname FROM authorsWHERE 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 = 0BEGIN-- This is executed as long as the previous fetch succeeds.FETCH NEXT FROM authors_cursorENDCLOSE authors_cursorDEALLOCATE authors_cursorGOau_lname----------------------------------------Bennetau_lname----------------------------------------Blotchet-Hallsau_lname----------------------------------------

B. 使用 FETCH 將值存入變數

下例與上例相似，但 FETCH 語句的輸出儲存於局部變數而不是直接返回給用戶端。PRINT 語句將變數組合成單一字串並將其返回到用戶端。

USE pubsGO-- Declare the variables to store the values returned by FETCH.DECLARE @au_lname varchar(40), @au_fname varchar(20)DECLARE authors_cursor CURSOR FORSELECT au_lname, au_fname FROM authorsWHERE 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_cursorINTO @au_lname, @au_fname-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN-- 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_cursorINTO @au_lname, @au_fnameENDCLOSE authors_cursorDEALLOCATE authors_cursorGOAuthor: Abraham BennetAuthor: Reginald Blotchet-Halls

C. 聲明 SCROLL 遊標並使用其它 FETCH 選項

下例建立一個 SCROLL 遊標，使其通過 LAST、PRIOR、RELATIVE 和 ABSOLUTE 選項支援所有滾動能力。

USE pubsGO-- Execute the SELECT statement alone to show the-- full result set that is used by the cursor.SELECT au_lname, au_fname FROM authorsORDER BY au_lname, au_fname-- Declare the cursor.DECLARE authors_cursor SCROLL CURSOR FORSELECT au_lname, au_fname FROM authorsORDER 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_cursorDEALLOCATE authors_cursorGOau_lname                                 au_fname---------------------------------------- --------------------Bennet                                   AbrahamBlotchet-Halls                           ReginaldCarson                                   CherylDeFrance                                 Micheldel Castillo                             InnesDull                                     AnnGreen                                    MarjorieGreene                                   MorningstarGringlesby                               BurtHunter                                   SherylKarsen                                   LiviaLocksley                                 CharleneMacFeather                               StearnsMcBadden                                 HeatherO'Leary                                  MichaelPanteley                                 SylviaRinger                                   AlbertRinger                                   AnneSmith                                    MeanderStraight                                 DeanStringer                                 DirkWhite                                    JohnsonYokomoto                                 Akikoau_lname                                 au_fname---------------------------------------- --------------------Yokomoto                                 Akikoau_lname                                 au_fname---------------------------------------- --------------------White                                    Johnsonau_lname                                 au_fname---------------------------------------- --------------------Blotchet-Halls                           Reginaldau_lname                                 au_fname---------------------------------------- --------------------del Castillo                             Innesau_lname                                 au_fname---------------------------------------- --------------------Carson                                   Cheryl