儲存過程返回不需要的游標結果

語境

我創建了一個儲存過程來模擬 OLTP 記憶體表的合併命令。它使用模擬合併將大量數據從普通表移動到 OLTP 表。

程序

這是程式碼：

CREATE PROCEDURE [cache].[MoveInverterData] (@sourecInverterID bigint, @from datetime2(7))
AS   
BEGIN
   SET NOCOUNT ON;

   DECLARE @i INT = 1; 
   DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

   DECLARE Employee_Cursor CURSOR READ_ONLY FOR  
       SELECT [InverterID],[Timestamp],[Status]
       FROM [data].[InverterData]
       WHERE [InverterID] = @sourecInverterID AND [Timestamp] >= @from;  

   OPEN Employee_Cursor;

   FETCH NEXT FROM Employee_Cursor; 
   WHILE @@FETCH_STATUS = 0
   BEGIN
       FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;

       UPDATE [cache].[InverterData]
       SET [Status] = @Status
       WHERE [InverterID] = @InverterID AND [Timestamp] = @Timestamp;  

       -- if there was no row to update, insert
       IF @@ROWCOUNT=0  
           INSERT INTO [cache].[InverterData]
               ([InverterID],[Timestamp],[Status])
           VALUES
               (@InverterID, @Timestamp, @Status);  
   END
     
   CLOSE Employee_Cursor;  
   DEALLOCATE Employee_Cursor;

   RETURN(0)
END

由於 OLTP 表（目標）不支持 MERGE命令，因此游標會像這樣插入一系列行。

問題

如果我執行我的 SP，我將得到一個結果，即選擇的第一個游標FETCH和單行計數列表。

問題

我知道這是正常行為，但我試圖抑制這種行為以獲得更簡單的結果。

我想要的是：

1. CURSOR選擇沒有表格結果
2. 受影響行數的總和作為所有更新/插入操作的單個數量

emptyFETCH確實是一個錯誤並解決了ROW結果問題（第 1 點）。

現在看起來像這樣：

CREATE PROCEDURE [cache].[MoveInverterData] (@sourecInverterID bigint, @from datetime2(7))
AS   
BEGIN
   SET NOCOUNT ON;

   DECLARE @i INT = 1; 
   DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

   DECLARE Employee_Cursor CURSOR READ_ONLY FOR  
       SELECT [InverterID],[Timestamp],[Status]
       FROM [data].[InverterData]
       WHERE [InverterID] = @sourecInverterID AND [Timestamp] >= @from;  

   OPEN Employee_Cursor;

   FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;
   WHILE @@FETCH_STATUS = 0
   BEGIN
       UPDATE [cache].[InverterData]
       SET [Status] = @Status
       WHERE [InverterID] = @InverterID AND [Timestamp] = @Timestamp;  

       -- if there was no row to update, insert
       IF @@ROWCOUNT=0  
           INSERT INTO [cache].[InverterData]
               ([InverterID],[Timestamp],[Status])
           VALUES
               (@InverterID, @Timestamp, @Status); 

       FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;
   END
     
   CLOSE Employee_Cursor;  
   DEALLOCATE Employee_Cursor;

   RETURN(0)
END

不使用游標*，而是將更新和插入操作顯式編寫為基於集合的操作：

CREATE OR ALTER PROCEDURE cache.MoveInverterData
(
   @sourceInverterID bigint, 
   @from datetime2(7)
)
AS   
BEGIN
   SET XACT_ABORT, NOCOUNT ON;

   DECLARE @RowsAffected integer = 0;

   -- TODO: Add error handling

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   BEGIN TRANSACTION;

       -- Update existing rows
       UPDATE CID
       SET [Status] = DID.[Status]
       FROM [data].InverterData AS DID
       JOIN cache.InverterData AS CID
               WITH (SNAPSHOT)
           ON CID.InverterID = DID.InverterID
           AND CID.[Timestamp] = DID.[Timestamp]
       WHERE
           DID.InverterID = @sourceInverterID
           AND DID.[Timestamp] >= @from;

       SET @RowsAffected += @@ROWCOUNT;

       -- Insert new rows
       INSERT cache.InverterData
           (InverterID, [Timestamp], [Status])
       SELECT
           DID.InverterID,
           DID.[Timestamp],
           DID.[Status]
       FROM [data].InverterData AS DID
       WHERE
           DID.InverterID = @sourceInverterID
           AND DID.[Timestamp] >= @from
           AND NOT EXISTS
           (
               SELECT 1
               FROM cache.InverterData AS CID
                   WITH (SNAPSHOT)
               WHERE
                   CID.InverterID = DID.InverterID
                   AND CID.[Timestamp] = DID.[Timestamp]
           );

       SET @RowsAffected += @@ROWCOUNT;

   COMMIT TRANSACTION;

   -- TODO: Use the @RowsAffected result
END;

確保兩個表在InverterID, [Timestamp].

• 與其他一些數據庫引擎不同，游標在 SQL Server 中通常效率很低。成本和每行操作幾乎總是使基於集合的解決方案更快、更高效。

或者，如果您希望盡可能使用本機編譯的儲存過程，請創建記憶體優化表類型來保存新數據：

CREATE TYPE dbo.InverterData AS TABLE
(
   RowID integer IDENTITY (1, 1) NOT NULL,
   InverterID  bigint NOT NULL,
   [Timestamp] datetime2(7) NOT NULL,
   [Status] nvarchar(50) NOT NULL,

   PRIMARY KEY NONCLUSTERED HASH (RowID)
       WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON);

然後編寫一個本地編譯的過程來合併新數據：

CREATE PROCEDURE cache.MoveInverterData_Native
   @InverterData dbo.InverterData READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

   DECLARE @RowID integer = 1; 
   DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

   WHILE @RowID > 0
   BEGIN
       SELECT
           @InverterID = ID.InverterID,
           @Timestamp = ID.[Timestamp],
           @Status = ID.[Status]
       FROM @InverterData AS ID
       WHERE ID.RowID = @RowID;

       IF @@ROWCOUNT = 0
       BEGIN
           SET @RowID = 0
       END
       ELSE
       BEGIN
           UPDATE cache.InverterData
           SET [Status] = @Status
           WHERE InverterID = @InverterID
           AND [Timestamp] = @Timestamp;

           IF @@ROWCOUNT = 0
               INSERT cache.InverterData
                   (InverterID, [Timestamp], [Status])
               VALUES
                   (@InverterID, @Timestamp, @Status);

           SET @RowID += 1;
       END;
   END;
END;

以及用於從源表填充記憶體優化表類型的包裝程序：

CREATE OR ALTER PROCEDURE cache.MoveInverterData
(
   @sourceInverterID bigint, 
   @from datetime2(7)
)
AS   
BEGIN
   SET XACT_ABORT, NOCOUNT ON;

   DECLARE @InverterData dbo.InverterData;

   INSERT @InverterData
       (InverterID, [Timestamp], [Status])
   SELECT
       ID.InverterID,
       ID.[Timestamp],
       ID.[Status]
   FROM [data].InverterData AS ID
   WHERE
       ID.InverterID = @sourceInverterID
       AND ID.[Timestamp] >= @from;

   EXECUTE cache.MoveInverterData_Native @InverterData;
END;

然後通過單個呼叫呼叫整個過程，例如：

EXECUTE cache.MoveInverterData
   @sourceInverterID = 1,
   @from = '20180601';

引用自：https://dba.stackexchange.com/questions/209832