-
StackOverflow 文档
-
Microsoft SQL Server 教程
-
游标
-
基本的游标语法
一个简单的游标语法,在几个示例测试行上运行:
/* Prepare test data */
DECLARE @test_table TABLE
(
Id INT,
Val VARCHAR(100)
);
INSERT INTO @test_table(Id, Val)
VALUES
(1, 'Foo'),
(2, 'Bar'),
(3, 'Baz');
/* Test data prepared */
/* Iterator variable @myId, for example sake */
DECLARE @myId INT;
/* Cursor to iterate rows and assign values to variables */
DECLARE myCursor CURSOR FOR
SELECT Id
FROM @test_table;
/* Start iterating rows */
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @myId;
/* @@FETCH_STATUS global variable will be 1 / true until there are no more rows to fetch */
WHILE @@FETCH_STATUS = 0
BEGIN
/* Write operations to perform in a loop here. Simple SELECT used for example */
SELECT Id, Val
FROM @test_table
WHERE Id = @myId;
/* Set variable(s) to the next value returned from iterator; this is needed otherwise the cursor will loop infinitely. */
FETCH NEXT FROM myCursor INTO @myId;
END
/* After all is done, clean up */
CLOSE myCursor;
DEALLOCATE myCursor;
SSMS 的结果。请注意,这些都是单独的查询,它们不是统一的。请注意查询引擎如何逐个处理每个迭代而不是作为集合。