sql-server - sql 循环游标的替代方案是什么？

Question

使用 SQL 2005/2008

我必须使用前向光标，但我不想遭受性能不佳的影响。有没有一种更快的方法可以在不使用光标的情况下循环？

Answer 1

这是使用光标的示例:

DECLARE @VisitorID int
DECLARE @FirstName varchar(30), @LastName varchar(30)
-- declare cursor called ActiveVisitorCursor 
DECLARE ActiveVisitorCursor Cursor FOR
SELECT VisitorID, FirstName, LastName 
FROM Visitors
WHERE Active = 1
-- Open the cursor
OPEN ActiveVisitorCursor 
-- Fetch the first row of the cursor and assign its values into variables
FETCH NEXT FROM ActiveVisitorCursor INTO @VisitorID, @FirstName, @LastName 
-- perform action whilst a row was found
WHILE @@FETCH_STATUS = 0
BEGIN
 Exec MyCallingStoredProc @VisitorID, @Forename, @Surname
 -- get next row of cursor
 FETCH NEXT FROM ActiveVisitorCursor INTO @VisitorID, @FirstName, @LastName 
END
 -- Close the cursor to release locks
CLOSE ActiveVisitorCursor 
 -- Free memory used by cursor
DEALLOCATE ActiveVisitorCursor 

现在这是我们如何在不使用光标的情况下获得相同结果的示例:

/* Here is alternative approach */

-- Create a temporary table, note the IDENTITY
-- column that will be used to loop through
-- the rows of this table
CREATE TABLE #ActiveVisitors (
       RowID int IDENTITY(1, 1), 
       VisitorID int,
       FirstName varchar(30),
       LastName varchar(30)
 )
DECLARE @NumberRecords int, @RowCounter int
DECLARE @VisitorID int, @FirstName varchar(30), @LastName varchar(30)

-- Insert the resultset we want to loop through
-- into the temporary table
INSERT INTO #ActiveVisitors (VisitorID, FirstName, LastName)
SELECT VisitorID, FirstName, LastName
FROM Visitors
WHERE Active = 1 

-- Get the number of records in the temporary table
SET @NumberRecords = @@RowCount 
--You can use: SET @NumberRecords = SELECT COUNT(*) FROM #ActiveVisitors
SET @RowCounter = 1

-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCounter <= @NumberRecords
BEGIN
 SELECT @VisitorID = VisitorID, @FirstName = FirstName, @LastName = LastName 
 FROM #ActiveVisitors
 WHERE RowID = @RowCounter

 EXEC MyCallingStoredProc @VisitorID, @FirstName, @LastName

 SET @RowCounter = @RowCounter + 1
END

-- drop the temporary table
DROP TABLE #ActiveVisitors