Microsoft SQL Server 基本仅前进光标

示例

通常，您会希望避免使用游标，因为它们会对性能产生负面影响。但是，在某些特殊情况下，您可能需要逐条记录遍历数据记录并执行一些操作。

DECLARE @orderId AS INT

-- here we are creating our cursor, as a local cursor and only allowing

-- forward operations

DECLARE rowCursor CURSOR LOCAL FAST_FORWARD FOR

-- this is the query that we want to loop through record by record

SELECT [OrderId]

FROM [dbo].[Orders]

-- first we need to open the cursor

OPEN rowCursor

-- now we will initialize the cursor by pulling the first row of data, in this example the [OrderId] column,

-- and storing the value into a variable called @orderId

FETCH NEXT FROM rowCursor INTO @orderId

-- start our loop and keep going until we have no more records to loop through

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @orderId

-- this is important, as it tells SQL Server to get the next record and store the [OrderId] column value into the @orderId variable

FETCH NEXT FROM rowCursor INTO @orderId

END

-- this will release any memory used by the cursor

CLOSE rowCursor

DEALLOCATE rowCursor