问题[cursors](dba)

我有一个具有该CURSOR VARYING类型的输出参数的存储过程。我想验证调用存储过程的代码是否可以使用输出游标。这似乎CURSOR_STATUS是使用正确的功能，但将它应用到我的输出光标时，我得到了意想不到的结果。该函数在创建它的存储过程中返回值 -3，但在存储过程之外按预期工作。请看下面的代码：

CREATE OR ALTER PROCEDURE dbo.OutputCursorTest  
(@Cursor_OUT CURSOR VARYING OUTPUT)
AS  
BEGIN
    SET NOCOUNT ON;

    SET @Cursor_OUT = CURSOR FORWARD_ONLY STATIC FOR
    SELECT [high]
    from master..spt_values

    OPEN @Cursor_OUT;

    SELECT CURSOR_STATUS('variable', '@Cursor_OUT'); -- this seems to always return -3

    -- possible workaround
    /*
    DECLARE @Cur_Copy CURSOR;
    SET @Cur_Copy =  @Cursor_OUT;
    SELECT CURSOR_STATUS('variable', '@Cur_Copy');
    DEALLOCATE @Cur_Copy;
    */

    RETURN;
END;

GO     

DECLARE @Cur CURSOR;
EXEC dbo.OutputCursorTest @Cursor_OUT = @Cur OUTPUT;
SELECT CURSOR_STATUS('variable', '@Cur'); -- this returns 1 as expected

如果重要的话，我在 SQL Server 2019 CU14 上。为什么CURSOR_STATUS在存储过程中返回值-3（“具有指定名称的游标不存在。”）？

以下查询在通过 db2 命令从控制台运行时有效。但是通过 DBeaver 运行时失败。

BEGIN NOT ATOMIC
  FOR R AS C CURSOR WITH HOLD FOR
    SELECT ROW_NUMBER() OVER() AS INDEX, ID
    FROM A_TABLE a
    WHERE a.ID BETWEEN 1 AND 1000
    ORDER BY a.ID
  DO
    UPDATE A_TABLE SET A_COL = 1 WHERE ID = R.ID;--
    IF MOD(R.INDEX, 100) = 0 THEN
      COMMIT;--
    END IF;--
  END FOR;--
  COMMIT;--
END;

DBeaver 给出以下结果

SQL 错误 [42601]：在“WHERE ID = R.ID”之后发现了意外的标记“END-OF-STATEMENT”。预期的标记可能包括：“<psm_semicolon>”.. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14

我希望复制表中的一组行，但在 SQL Server 17 中处理唯一列。该表是：

CREATE TABLE test1 (
    id int CONSTRAINT PK_test1 PRIMARY KEY  CLUSTERED IDENTITY,
    name nvarchar(10) CONSTRAINT UK_test1 UNIQUE NONCLUSTERED,
    value int,
    type varchar(20)
);

表格内容为：

INSERT INTO test1 VALUES ('e_1',1,'A');
INSERT INTO test1 VALUES ('342A',34,'ABC');
INSERT INTO test1 VALUES ('Tame_33',277,'Test10');

现在我希望多次复制这些行，但问题在于 NAME 列是唯一的。到目前为止我所做的解决方案只是使用光标，但这效率低下，我很好奇这是否可以以更有效的方式完成？

DECLARE @id int;
DECLARE @name nvarchar(20);
DECLARE @value int;
DECLARE @type varchar(10);
DECLARE @cnt int = 0;stackoverflow
DECLARE @intcnt int = 0;

DECLARE temp_cursor CURSOR FOR
SELECT * FROM test1
OPEN temp_cursor;
FETCH NEXT FROM temp_cursor INTO @id,@name,@value,@type;

WHILE @@FETCH_STATUS = 0
BEGIN
    WHILE @intcnt < 5
    BEGIN
        INSERT INTO test1 VALUES
        ('ins_' + CONVERT(varchar,@intcnt) + '_'  + CONVERT(varchar,@cnt), @value, @type);
        SET @intcnt = @intcnt + 1;
    END 

    SET @intcnt = 0;
    
    FETCH NEXT FROM temp_cursor INTO @id,@name,@value,@type;
    SET @cnt = @cnt + 1;
END

CLOSE temp_cursor;
DEALLOCATE temp_cursor;

发现这个代码错误导致了生产中的死循环：

DECLARE @BatchID INT
DECLARE MyCursor CURSOR FOR
    SELECT BatchID = ...
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @BatchID
WHILE @@FETCH_STATUS = 0
BEGIN
    ...
    IF [condition]
    BEGIN
        ...
        FETCH NEXT FROM MyCursor INTO @BatchID
    END
END
CLOSE MyCursor
DEALLOCATE MyCursor

假设需要一个游标是有保证的，有没有办法防止这个错误（除了更多的测试/代码审查）？

在其他语言中，我们FOREACH有为我们管理迭代进程的循环，或者FOR是事后诸葛亮的循环，那么 SQL Server 中是否有任何等价物可以防止诸如放错位置（或忘记！）之类的草率错误？

我从未见过 SQL 中的自定义循环需要对光标做任何花哨WHILE的事情，并且循环具有相同的风险（以及@BatchID 为DELETE #WorkData WHERE ID = @BatchID时的情况），那么对于典型的用例，如何以编程方式/干净地减轻这种风险？NULL

像这样的方法非常不吸引人：

DECLARE @BatchID INT
DECLARE MyCursor CURSOR FOR
    SELECT BatchID = -1--dummy entry to always be skipped
    UNION ALL SELECT BatchID = ...
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @BatchID
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM MyCursor INTO @BatchID
    IF @@FETCH_STATUS = 0
    BEGIN
        ...
    END
END
CLOSE MyCursor
DEALLOCATE MyCursor

在我看来，@@FETCH_STATUS在中间没有任何游标位置/控制语句的情况下连续检查两次可能是 SQL Server猜测是否存在此类错误的有效方法，因为我使用游标的经验从未见过连续两次检查故意（至少没有嵌套游标，但那些仍然有控制语句，例如OPEN和CLOSE检查@@FETCH_STATUS外部游标之间）。

_{PS[condition]这一次相当于“不是DST过渡日”（这是这个星期天）。更多时区错误！}

我对语言特性最感兴趣，以实现与其他语言如何处理 for 循环的事后考虑子句（例如i++in for (int i = 0; i < myArray.Length; i++)）相同的自动提供的保证。个人负责的事情越少，出错的地方就越少，审计我们系统中成千上万的存储过程将是一项艰巨的任务，特别是因为其中许多不恰当地迭代数据而不是使用已经基于集合的逻辑。

我正在尝试将每个交易者的前 253 个值从临时交易者表插入到临时结果表中。

我正在使用以下代码来做到这一点。

CREATE TABLE #trad ( Trad varchar(50))    
IF @trad is not null    
INSERT INTO #Trad    
SELECT DISTINCT * FROM dbo.Split(@trad,',') -- split will have traders seperated by commas          
ELSE INSERT INTO #Traders    
Select distinct Trader from TraderMap tm     
where traderorgroup = 'trader'  


CREATE TABLE #result      
(port VARCHAR(50),portdate DATE,P MONEY,V_95 MONEY,      
V_99 MONEY,VLimit MONEY,V_90 MONEY,H_99 MONEY,      
H_975 MONEY,H_95 MONEY,H_90 MONEY,H_80 MONEY,dCnt 
INT,Trad varchar(100))  

DECLARE @traderlist AS Varchar(100)      
DECLARE traderlist CURSOR FOR      
SELECT DISTINCT Traders FROM #traders      
OPEN traderlist      
FETCH next FROM traderlist INTO @traderlist      
WHILE @@FETCH_STATUS = 0      
BEGIN  
INSERT INTO #result (port, portdate, P, V_95, V_99, VLimit, 
dCnt,Trad)      
SELECT  TOP 253 pl.Port,pl.Portdate,pl.dtd_P 
PL,var.V_95,var.V_99, sum(gu.Guid * -1) VLimit,      
Row_number() OVER(partition by pl.TradName ORDER BY Portdate DESC) 
dCnt,pl.TradName      
FROM Trad_PL pl INNER JOIN rep_tab var      
ON pl.Portdate = var.V_Date      
AND pl.Port = var.Port     
AND pl.TradName = var.Trad      
INNER JOIN Trad_Guid gui      
ON pl.Port = gui.Port     
AND pl.TradName = gui.Trad      
WHERE pl.Portdate <= @portdate      
AND pl.Port = CASE WHEN @port = 'Her' THEN 'Herport' ELSE 
@port END      
AND pl.ONA = 'Act'  
AND pl.TradName = @tradlist          
AND var.PType = '0Trad' 
group by  
pl.TradName,pl.Port,pl.Portdate,pl.dtd_P,var.V_95,var.V_99
ORDER BY Portdate DESC
FETCH next FROM tradlist INTO @tradlist      
END      
CLOSE tradlist      
DEALLOCATE tradlist    

此代码运行数小时。有什么有效的方法可以从 Trad_PL 和 rep_tab 中获取每个交易的前 253 行。

我正在尝试在不再需要临时表时使用游标来清理它们。我有一个小表，其中包含临时表的名称和标识符。光标卡在无限循环中，但前提是我在其中执行某些语句。如果我只是从 中打印出值FETCH，它就可以完美地工作。这是代码。

DECLARE @id bigint;
DECLARE @table_name varchar(max);

DECLARE st CURSOR LOCAL FAST_FORWARD FOR 
SELECT ID, TableName FROM SearchTables WHERE CustomerID IS NULL

OPEN st
FETCH NEXT FROM st INTO @id, @table_name
WHILE @@FETCH_STATUS <> -1 
BEGIN   
    IF(OBJECT_ID(@table_name) IS NOT NULL) 
        EXEC('DROP TABLE ' + @table_name);

    UPDATE SearchTables SET Deleted=1 WHERE ID=@id;

    PRINT CAST(@id AS varchar(max)) + ' ' + @table_name;

    FETCH NEXT FROM st INTO @id, @table_name;
END 

CLOSE st
DEALLOCATE st

如果我注释掉这些行

    IF(OBJECT_ID(@table_name) IS NOT NULL) 
        EXEC('DROP TABLE ' + @table_name);

    UPDATE SearchTables SET Deleted=1 WHERE ID=@id;

PRINT输出所有的 ID 和表名。如果我不评论它们，我得到的只是第一行一遍又一遍，直到我取消查询。我也尝试将IF线路更改为，EXEC('DROP TABLE IF EXISTS ' + @table_name)但这也不起作用。

我想知道游标的一般替换是什么。我看到的游标的一般实现是

DECLARE @variable INT, @sqlstr NVARCHAR(MAX)

DECLARE cursor_name CURSOR
FOR select_statement --essentially to get an array for @variable 
                     --usually it's a subset of unique ids for accounts, clients, parts, etc

OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @variable
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @sqlstr = N'
     /* some query that uses '+ str(@variable) +' to do dirty work
     such as: go through all our accounts, if it''s some subset (possible new cursor), 
     go through those accounts and connect this way, 
     map those fields and add it to our big uniform table */
     '

     EXEC sp_executesql @sqlstr
FETCH NEXT FROM cursor_name INTO @variable
END

CLOSE cursor_name
DEALLOCATE cursor_name

既然这么多人反对游标（对SO：Why do people hat cursors表示赞同），一般实现（最好是 SQL Server）的一般替代品是什么？

我尝试创建一个表来存储每个数据库的已用存储信息。
由于这些托管在不同的集群/实例上，我创建了一个包含所有实例/服务器信息的表。
_______________________
|________DBInstance_______|
|____ID___|_实例_______|
|____1____|服务器\实例__|
|____2____|AG-Listener_____|

然后我创建一个游标并将我的查询发送到实例表中列出的每个服务器。

DECLARE @ID nvarchar(2), @Instancename VARCHAR(50), @cmd NVARCHAR(MAX)
DECLARE DBCursor CURSOR FOR 
SELECT ID, Instance 
from DBInstancelist; 

DECLARE @ID nvarchar(2), @Instancename VARCHAR(50), @cmd NVARCHAR(MAX)
DECLARE DBCursor CURSOR FOR 
SELECT ID, Instance 
from DBInstancelist; 

OPEN DBCursor 
FETCH NEXT FROM DBCursor into @id , @Instancename
WHILE @@FETCH_STATUS = 0 
BEGIN
select @cmd = N'INSERT INTO DBSIZE 
select a.*
from OPENROWSET (''SQLNCLI'',''Server=' + @Instancename + ';Trusted_Connection=YES;'',''select 
        [ID] = '+@ID+',
        [Database Name] = DB_NAME(database_id),
        [Type] = CASE WHEN Type_Desc = ''''ROWS'''' THEN ''''Data File''''
                      WHEN Type_Desc = ''''LOG''''  THEN ''''Log File''''
                      ELSE ''''SUMME'''' END,
        [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL ),
        GETDATE() AS Scantime
FROM   sys.master_files
WHERE      database_id > 4
GROUP BY      GROUPING SETS
              (
                     (DB_NAME(database_id), Type_Desc),
                     (DB_NAME(database_id))
              )
ORDER BY      DB_NAME(database_id), Type_Desc DESC'') 
AS a;'
--select @cmd
exec sp_executesql @cmd
--select @cmd
FETCH NEXT FROM DBCursor into @ID, @Instancename
END 
CLOSE DBCursor
DEALLOCATE DBCursor

对于前 4 个实例，我收到了信息，但对于第 5 个实例，我收到了以下错误。

 "Arithmetic overflow error converting expression to data type int.

这个错误只会出现，如果我添加行

 "[ID] = '+@ID+',",

但进一步报告需要实例 ID。

为什么会出现转换，我该如何阻止它（我的错误在哪里）？

我正在尝试写一个游标。我创建了一个用于从 sys.dm_exec_sessions 捕获登录名和时间的表。现在，如果登录已经存在，我需要编写一个游标来将登录时间更新为上次登录时间，并在我的表中不存在登录的情况下插入一行。我想出了以下方法，但不幸的是，子查询返回多个值时出现错误。有任何想法吗？？？

declare @log as varchar(200)
declare @log_time as datetime
declare LoginsSize cursor for
        SELECT  login_name, login_time
        FROM sys.dm_exec_sessions 
open LoginsSize
            fetch next from LoginsSize into @log, @log_time
            while( @@fetch_status = 0)
        begin                                                           
          If (Select Login from [dbo].[LoginsForDBUserList])  = @log 

             Begin
             UPDATE  [dbo].[LoginsForDBUserList]
             SET     LastLoginTime = @log_time
             WHERE   [login]= @log
             END

             Else 

             Begin
             Insert Into [dbo].[LoginsForDBUserList]
             SELECT  login_name, login_time
             FROM sys.dm_exec_sessions
             END

        fetch next from LoginsSize into @log, @log_time
        close LoginsSize
        deallocate LoginsSize

end

我有两个这样的表：
表实验（我现在只使用一个 roadID (rID)）

rID | latBegin |longBegin |latEnd   |longEnd  
041 | 23.875464|-80.456798|30.549879|-83.5465521  
041 | 33.776563|-81.157526|33.248261|-81.2468425 

对于上述每一行，我希望光标获取 rID、latBegin、longBegin 并在Table ExperimentDetails中检查 lat 和 long 的距离

rID | lat      |long      |temp |direction|tension    
041 | 33.665632|-81.137952|56.23|L        |54.54      
041 | 23.245632|-80.127952|56.23|L        |23.54  
041 | 23.454555|-80.232456|66.23|L        |13.54  
041 | 23.568763|-80.346586|26.23|L        |63.54  
041 | 23.796545|-80.446586|86.23|L        |83.54   
041 | 23.996545|-80.946586|96.23|L        |22.54   

查询的步骤是：
第 1 步：从 Table Experiment 中获取特定 rID 的 latBegin 和 longBegin
第 2 步：转到 ExperimentDetails 并运行此计算以获取 Details 表中每个 lat 和 long 的距离差

Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000  

第 3 步：在详细信息表中获取此新关联列的最小值
第 4 步：获取该最小值的关联温度、方向和张力值并将其放入新表中（尚未完成>需要帮助）
第 5 步：从最小值获取前 30 英尺或 360 英寸的温度、张力平均值并将其放在另一个表中（列：rID、latBegin、longBegin、avgTension、avgtemp 尚未完成>需要帮助）

到目前为止，这是我的代码：

      DECLARE @rID int,@latBegin decimal(15,10),@longBegin decimal(15,10),@minV decimal(15,10),@temp decimal(15,10)

    DECLARE MY_CURSOR CURSOR   
      LOCAL STATIC READ_ONLY FORWARD_ONLY  
    FOR   
    SELECT DISTINCT rid,latbeg,longbeg   
    FROM dbo.Experiment  
    WHERE rID='041'  

    OPEN MY_CURSOR  
    FETCH NEXT FROM MY_CURSOR INTO @rID,@latBegin,@longBegin  
    WHILE @@FETCH_STATUS = 0  
    BEGIN   


        SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID Order by rID)  
, @temp=temp

        FROM dbo.ExperimentDetails  
            WHERE rid='041'  

        Insert into dbo.Test(rid,lat,lon,min,temp) values   (@rid,@latBegin,@longBegin,@minV,@temp)  



        FETCH NEXT FROM MY_CURSOR INTO @rID,@latBegin,@longBegin  
    END  

    CLOSE MY_CURSOR  
    DEALLOCATE MY_CURSOR

测试表如下所示：

rID|lat      |long      |min |temp  
041|23.875464|-80.456798|12.1|96.23
041|33.776563|-81.157526|11.0|96.23

最后一行重复临时数据，不正确

它应该如下所示：

rID|lat      |long      |min |temp  
041|23.875464|-80.456798|12.1|83.54
041|33.776563|-81.157526|11.0|54.54

我已经提供了尽可能多的信息，但是如果您需要更多信息，请询问，将不胜感激，从昨天开始就一直在努力解决这个问题。目前，除了@minV 之外，我还试图引入温度、方向和张力，但它不允许我这样做

对于第 5 步：对于详细信息表，光标中的计算理论上如下所示：

rID |lat       |long      |temp |DISTDIFF  
041 | 23.245632|-80.127952|56.23|372.12    
041 | 23.454555|-80.232456|66.23|300.22   
041 | 23.568763|-80.346586|26.23|50.48   
041 | 23.796545|-80.446586|86.23|12.10

然后在插入时检查最小值，即 12.10 并插入与该行相关的值。
对于第 5 步，我需要在最小值之前而不是之后找到最接近 360 的值（之后可能还有更接近的其他值），在本例中为 372.12，然后取 temp 的平均值，如下所示：

rID |minlat    |minlong   |lastlat  |lastlong  |Avgtemp  
041 | 23.796545|-80.446586|23.245632|-80.127952|58.73 

使用 SQL Server 2014