基本的游标语法

一个简单的游标语法,在几个示例测试行上运行:

/* 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 的结果。请注意,这些都是单独的查询,它们不是统一的。请注意查询引擎如何逐个处理每个迭代而不是作为集合。

ID Val
1 Foo
(1 排受影响)
ID Val
2 Bar
(1 排受影响)
ID Val
3 Baz
(1 排受影响)