SQLSERVER cursor and loop statements

To sort it out, regarding cursors, MSDN has:

Retrieve specific rows through Transact-SQL server cursors.

Parameters

NEXT

returns the result row immediately following the current row, and the current row is incremented into the returned row. If FETCH NEXT is the first fetch operation on the cursor, the first row in the result set is returned. NEXT is the default cursor extraction option.

PRIOR

Returns the result row immediately preceding the current row, and the current row is reduced to the returned row. If FETCH PRIOR is the first fetch operation on the cursor, no rows are returned and the cursor is placed before the first row.

FIRST

Returns the first row in the cursor and makes it the current row.

LAST

Returns the last row in the cursor and makes it the current row.

ABSOLUTE { n | @nvar}

If n or @nvar is positive, return the nth row from the cursor head backwards and turn the returned row into the new current row. If n or @nvar is negative, returns the nth row forward from the end of the cursor and turns the returned row into the new current row. If n or @nvar is 0, no rows are returned. n must be an integer constant, and the data type of @nvar must be smallint, tinyint, or int.

RELATIVE { n | @nvar}

If n or @nvar is positive, return the nth row starting from the current row and turn the returned row into the new current row. If n or @nvar is negative, returns the nth row forward from the current row and turns the returned row into the new current row. If n or @nvar is 0, the current row is returned. On the first fetch of a cursor, if you specify FETCH RELATIVE with n or @nvar set to negative or 0, no rows are returned. n must be an integer constant, and the data type of @nvar must be smallint, tinyint, or int.

GLOBAL

Specify cursor_name refers to the global cursor.

cursor_name

The name of the open cursor to extract from. If both global and local cursors use cursor_name as their names, then when GLOBAL is specified, cursor_name refers to the global cursor; when GLOBAL is not specified, cursor_name refers to the local cursor.

@cursor_variable_name

Cursor variable name, referring to the open cursor from which the extraction operation is to be performed.

INTO @variable_name[ ,...n]

allows the column data of the extraction operation to be placed into local variables. Each variable in the list is associated from left to right with the corresponding column in the cursor result set. The data type of each variable must match the data type of the corresponding result set column, or be an implicit conversion supported by the result set column data type. The number of variables must match the number of columns in the cursor selection list.

Comments

If the SCROLL option is not specified in an ISO-style DECLARE CURSOR statement, NEXT is the only supported FETCH option. All FETCH options are supported if the SCROLL option is specified in an ISO-style DECLARE CURSOR statement.

If using the Transact-SQL DECLARE cursor extension plug-in, the following rules apply:

NEXT is the only supported FETCH option if FORWARD_ONLY or FAST_FORWARD is specified.

If no DYNAMIC, FORWARD_ONLY or FAST_FORWARD options are specified and one of KEYSET, STATIC or SCROLL is specified, all FETCH options are supported.

DYNAMIC SCROLL cursors support all FETCH options except ABSOLUTE.

@@FETCH_STATUS function reports the status of the previous FETCH statement. The same information is recorded in the fetch_status column in the cursor returned by sp_describe_cursor. This status information should be used to determine the validity of the data returned by a FETCH statement before performing any operations on the data. For more information, see @@FETCH_STATUS (Transact-SQL).

Permissions

FETCH permissions are granted to any valid user by default.

Example

A. Using FETCH in a simple cursor

The following example declares a simple cursor for the rows in the Person.Contact table whose last name starts with the letter B and uses FETCH NEXT to fetch these rows one by one. The FETCH statement returns the values ​​of the columns specified in the DECLARE CURSOR as a single-row result set.

USE AdventureWorks
GO
DECLARE contact_cursor CURSOR FOR
SELECT LastName FROM Person.Contact
WHERE LastName LIKE 'B%'
ORDER BY LastName

OPEN 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 = 0
BEGIN
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM contact_cursor
END

CLOSE contact_cursor
DEALLOCATE contact_cursor
GO

B. Use FETCH to store values ​​in variables

The following example is similar to example A, but the output of the FETCH statement is stored in a local variable instead of being returned directly to the client. The PRINT statement combines the variables into a single string and returns it to the client.

USE AdventureWorks
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @LastName varchar(50), @FirstName varchar(50)

DECLARE contact_cursor CURSOR FOR
SELECT LastName, FirstName FROM Person.Contact
WHERE LastName LIKE 'B%'
ORDER BY LastName, FirstName

OPEN 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_cursor
INTO @LastName, @FirstName

-- 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 'Contact Name: ' + @FirstName + ' ' +  @LastName

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM contact_cursor
   INTO @LastName, @FirstName
END

CLOSE contact_cursor
DEALLOCATE contact_cursor
GO

C. Declare SCROLL cursor and use other FETCH options

以下示例创建一个 SCROLL 游标，使其通过 LAST、PRIOR、RELATIVE 和 ABSOLUTE 选项支持全部滚动功能。

。

另外，再举一个简单的例子：

Declare @Id varchar(20) 
Declare @Name varchar(20) 
Declare Cur Cursor For 
select substring(id,0,7) as id,name from temp1 
Open Cur 
Fetch next From Cur Into @Id,@Name 
While @@fetch_status=0 
Begin 
Update temp Set [c3]=@Name where [id] like @Id+'%' 
Fetch Next From Cur Into @Id,@Name 
End 
Close Cur 
Deallocate Cur 

 

简单的FOR循环等：

declare   @i   int   
  set   @i=0   
  while   @i<10   
  begin   
      set   @i   =   @i+1   
  end   
    
  SQL   SERVER不支持FOR循环

USE AdventureWorks
GO
-- Execute the SELECT statement alone to show the 
-- full result set that is used by the cursor.
SELECT LastName, FirstName FROM Person.Contact
ORDER BY LastName, FirstName

-- Declare the cursor.
DECLARE contact_cursor SCROLL CURSOR FOR
SELECT LastName, FirstName FROM Person.Contact
ORDER BY LastName, FirstName

OPEN 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_cursor

CLOSE contact_cursor
DEALLOCATE contact_cursor
GO

语法

FETCH 
          [ [ NEXT | PRIOR | FIRST | LAST 
                    | ABSOLUTE { n | @nvar } 
                    | RELATIVE { n | @nvar } 
               ] 
               FROM 
          ] 
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } 
[ INTO @variable_name [ ,...n ] ]