在存储过程中使用带有动态 SQL 的游标

Question

我有一个在存储过程中创建的动态 SQL 语句。 我需要使用游标迭代结果。 我很难找出正确的语法。 这就是我正在做的事情。

SELECT @SQLStatement = 'SELECT userId FROM users'

DECLARE @UserId

DECLARE users_cursor CURSOR FOR
EXECUTE @SQLStatment --Fails here. Doesn't like this

OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @UserId

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC asp_DoSomethingStoredProc @UserId

END
CLOSE users_cursor
DEALLOCATE users_cursor

这样做的正确方法是什么？

Answer 1

游标只接受 select 语句，因此如果 SQL 确实需要动态，请将声明游标作为您正在执行的语句的一部分。 为了使下面的工作正常，您的服务器必须使用全局游标。

Declare @UserID varchar(100)
declare @sqlstatement nvarchar(4000)
--move declare cursor into sql to be executed
set @sqlstatement = 'Declare  users_cursor CURSOR FOR SELECT userId FROM users'

exec sp_executesql @sqlstatement


OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @UserId

WHILE @@FETCH_STATUS = 0
BEGIN
Print @UserID
EXEC asp_DoSomethingStoredProc @UserId

FETCH NEXT FROM users_cursor --have to fetch again within loop
INTO @UserId

END
CLOSE users_cursor
DEALLOCATE users_cursor

如果需要避免使用全局游标，还可以将动态 SQL 的结果插入到临时表中，然后使用该表来填充游标。

Declare @UserID varchar(100)
create table #users (UserID varchar(100))

declare @sqlstatement nvarchar(4000)
set @sqlstatement = 'Insert into #users (userID) SELECT userId FROM users'
exec(@sqlstatement)

declare users_cursor cursor for Select UserId from #Users
OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @UserId

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC asp_DoSomethingStoredProc @UserId

FETCH NEXT FROM users_cursor
INTO @UserId

END
CLOSE users_cursor
DEALLOCATE users_cursor

drop table #users

Answer 2

此代码是带有游标的动态列的一个非常好的示例，因为您不能在 @STATMENT 中使用“+”：

ALTER PROCEDURE dbo.spTEST
AS
    SET NOCOUNT ON
    DECLARE @query NVARCHAR(4000) = N'' --DATA FILTER
    DECLARE @inputList NVARCHAR(4000) = ''
    DECLARE @field sysname = '' --COLUMN NAME
    DECLARE @my_cur CURSOR
    EXECUTE SP_EXECUTESQL
        N'SET @my_cur = CURSOR FAST_FORWARD FOR
            SELECT
                CASE @field
                    WHEN ''fn'' then fn
                    WHEN ''n_family_name'' then n_family_name
                END
            FROM
                dbo.vCard
            WHERE
                CASE @field
                    WHEN ''fn'' then fn
                    WHEN ''n_family_name'' then n_family_name
                END
                LIKE ''%''+@query+''%'';
            OPEN @my_cur;',
        N'@field sysname, @query NVARCHAR(4000), @my_cur CURSOR OUTPUT',
        @field = @field,
        @query = @query,
        @my_cur = @my_cur OUTPUT
    FETCH NEXT FROM @my_cur INTO @inputList
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @inputList
        FETCH NEXT FROM @my_cur INTO @inputList
    END
    RETURN

Answer 3

通过 ODBC 连接使用非关系数据库（IDMS？）是游标和动态 SQL 似乎是唯一途径的时代之一。

select * from a where a=1 and b in (1,2)

需要 45 分钟才能响应，而重写为使用不带 in 子句的键集将在 1 秒内运行：

select * from a where (a=1 and b=1)
union all
select * from a where (a=1 and b=2)

如果 B 列的 in 语句包含 1145 行，则使用游标创建单独的语句并将它们作为动态 SQL 执行要快得多比使用 in 子句。 傻嘿？

是的，在关系数据库中没有时间应该使用游标。 我简直不敢相信我遇到过光标循环速度快了几个数量级的实例。

Answer 4

首先，尽可能避免使用光标。 当您似乎离不开时，可以使用以下一些资源来根除它：

那里丢失光标的 15 种方法...第 1 部分，简介

Row-没有游标的按行处理

尽管如此，您可能还是会被一个问题困扰——我从您的问题中了解到的信息不足以确定其中任何一个都适用。 如果是这种情况，您就会遇到不同的问题 - 游标的 select 语句必须是实际 SELECT 语句，而不是 EXECUTE 语句。 你被困住了。

但请参阅 cmsjr（在我写作时出现的）关于使用临时表的答案。 我会比“普通”游标更避免使用全局游标......

Answer 5

最近从 Oracle 切换到 SQL Server（雇主偏好）后，我注意到 SQL Server 中的游标支持滞后。 游标并不总是邪恶的，有时需要，有时更快，有时比尝试通过重新排列或添加优化提示来调整复杂查询更干净。 “游标是邪恶的”观点在 SQL Server 社区中更为突出。

所以我猜这个答案是改用Oracle或者给MS一个线索。

• Oracle EXECUTE IMMEDIATE 到游标
• 循环通过隐式光标（for循环隐式定义/打开/关闭光标！）

Answer 6

SQL Server 中的另一个选项是对存储过程中的表变量进行所有动态查询，然后使用游标来查询和处理它。 至于可怕的光标争论:)，我看到的研究表明，在某些情况下，如果设置正确，光标实际上可以更快。 当所需的查询太复杂，或者根本不可能（对我来说；））时，我自己使用它们。

Answer 7

这段代码可能对你有用。

sql server中游标的使用示例

DECLARE sampleCursor CURSOR FOR 
      SELECT K.Id FROM TableA K WHERE ....;
OPEN sampleCursor
FETCH NEXT FROM sampleCursor INTO @Id
WHILE @@FETCH_STATUS <> -1
BEGIN

UPDATE TableB
   SET 
      ...