在 SQLServer 游标中获取多个值

我有一个光标，其中包含它带回的行中的几个列，我希望立即处理它们。我注意到我看到的大多数关于如何使用游标的例子都显示它们将游标中的一个特定列分配给一个标量值，然后移动到下一行,

例如:。

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name


WHILE @@FETCH_STATUS = 0
BEGIN
--Do Stuff with @name scalar value, then get next row from cursor


FETCH NEXT FROM db_cursor INTO @name
END

我想知道的是，是否有可能做到以下几点:

    OPEN db_cursor
FETCH NEXT FROM db_cursor;


WHILE @@FETCH_STATUS = 0
BEGIN
SET @myName = db_cursor.name;
SET @myAge = db_cursor.age;
SET @myFavoriteColor = db_cursor.favoriteColor;
--Do stuff with scalar values


FETCH NEXT FROM db_cursor;
END

我很感激你的帮助。

144026 次浏览
小开
最佳答案

This should work:

DECLARE db_cursor CURSOR FOR SELECT name, age, color FROM table;
DECLARE @myName VARCHAR(256);
DECLARE @myAge INT;
DECLARE @myFavoriteColor VARCHAR(40);
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @myName, @myAge, @myFavoriteColor;
WHILE @@FETCH_STATUS = 0
BEGIN


--Do stuff with scalar values


FETCH NEXT FROM db_cursor INTO @myName, @myAge, @myFavoriteColor;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
小开

Do not use @@fetch_status - this will return status from the last cursor in the current connection. Use the example below:

declare @sqCur cursor;
declare @data varchar(1000);
declare @i int = 0, @lastNum int, @rowNum int;
set @sqCur = cursor local static read_only for
select
row_number() over (order by(select null)) as RowNum
,Data -- you fields
from YourIntTable
open @cur
begin try
fetch last from @cur into @lastNum, @data
fetch absolute 1 from @cur into @rowNum, @data --start from the beginning and get first value
while @i < @lastNum
begin
set @i += 1


--Do your job here
print @data


fetch next from @cur into @rowNum, @data
end
end try
begin catch
close @cur      --|
deallocate @cur --|-remove this 3 lines if you do not throw
;throw          --|
end catch
close @cur
deallocate @cur

提交答案