我注意到在游标循环内部声明一个表变量时,该变量会在游标的生命周期内一直存在。我的问题是,是否有一种方法可以声明该变量,使其仅在迭代期间存在?以下是一个示例:
DECLARE @SourceTable TABLE(Id INT IDENTITY(1,1),Remarks VARCHAR(10))
INSERT INTO @SourceTable VALUES ('First')
INSERT INTO @SourceTable VALUES ('Second')
INSERT INTO @SourceTable VALUES ('Third ')
/* declare variables */
DECLARE @variable INT
DECLARE my_cursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT Id FROM @SourceTable
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @variable
WHILE @@FETCH_STATUS = 0
BEGIN
--Dose not creates a new instance
DECLARE @VarTable TABLE (Remarks VARCHAR(10))
INSERT INTO @VarTable
SELECT TOP 1 Remarks FROM @SourceTable
WHERE Id = @variable
--Works as intended if you drop when done
CREATE TABLE #TempTable (Remarks VARCHAR(10))
INSERT INTO #TempTable
SELECT TOP 1 Remarks FROM @SourceTable
WHERE Id = @variable
DROP TABLE #TempTable
FETCH NEXT FROM my_cursor INTO @variable
END
CLOSE my_cursor
DEALLOCATE my_cursor
SELECT * FROM @VarTable
但是这还更加令人困惑。如果你试图在游标之外查询临时表,你将会得到一个错误。看起来似乎临时表是这种情况下唯一正确的方式?