SQLServer游标应用
SQLServer游标使用?Transact-SQL 语法约定????参数??注释??权限??示例USE AdventureWorksGODECLARE contac
SQLServer游标使用
?Transact-SQL 语法约定
?
?
??参数??注释??权限??示例USE AdventureWorksGODECLARE contact_cursor CURSOR FORSELECT LastName FROM Person.ContactWHERE LastName LIKE 'B%'ORDER BY LastNameOPEN contact_cursor-- Perform the first fetch.FETCH NEXT FROM contact_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 contact_cursorENDCLOSE contact_cursorDEALLOCATE contact_cursorGO
USE AdventureWorksGO-- Declare the variables to store the values returned by FETCH.DECLARE @LastName varchar(50), @FirstName varchar(50)DECLARE contact_cursor CURSOR FORSELECT LastName, FirstName FROM Person.ContactWHERE LastName LIKE 'B%'ORDER BY LastName, FirstNameOPEN contact_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 contact_cursorINTO @LastName, @FirstName-- 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 'Contact Name: ' + @FirstName + ' ' + @LastName -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM contact_cursor INTO @LastName, @FirstNameENDCLOSE contact_cursorDEALLOCATE contact_cursorGOUSE AdventureWorksGO-- Execute the SELECT statement alone to show the -- full result set that is used by the cursor.SELECT LastName, FirstName FROM Person.ContactORDER BY LastName, FirstName-- Declare the cursor.DECLARE contact_cursor SCROLL CURSOR FORSELECT LastName, FirstName FROM Person.ContactORDER BY LastName, FirstNameOPEN contact_cursor-- Fetch the last row in the cursor.FETCH LAST FROM contact_cursor-- Fetch the row immediately prior to the current row in the cursor.FETCH PRIOR FROM contact_cursor-- Fetch the second row in the cursor.FETCH ABSOLUTE 2 FROM contact_cursor-- Fetch the row that is three rows after the current row.FETCH RELATIVE 3 FROM contact_cursor-- Fetch the row that is two rows prior to the current row.FETCH RELATIVE -2 FROM contact_cursorCLOSE contact_cursorDEALLOCATE contact_cursorGO
?
?
??语法FETCH ??????????[ [ NEXT | PRIOR | FIRST | LAST ????????????????????| ABSOLUTE { n | @nvar } ????????????????????| RELATIVE { n | @nvar } ???????????????] ???????????????FROM ??????????] { { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]
