游标使用事例.doc

示例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 - Bennet au_lname - Blotchet-Halls au_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_cursor INTO au_lname, au_fnameENDCLOSE authors_cursorDEALLOCATE authors_cursorGOAuthor: Abraham BennetAuthor: Reginald Blotchet-HallsC. 声明 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 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 OLeary 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