sql-server - 了解游标的工作原理

Question

尝试更改一些存储过程和查询以获得更好的性能。对于某些部分，它正在重写游标语法。为此，我必须充分了解它们的工作原理。我尝试了这个简单的 ETL 示例，但它没有给我预期的结果。基本上，在此处使用光标执行 UPSERT。

示例代码:

CREATE TABLE #Destination 
(PersonID INT, FirstName VARCHAR(10), LastName VARCHAR (10))
 INSERT INTO #Destination VALUES (101, 'M', 'Donalds')
 INSERT INTO #Destination VALUES (102, NULL, 'Richards')
 INSERT INTO #Destination VALUES (103, 'Rianna', 'Lock')
 INSERT INTO #Destination VALUES (104, 'Leo', 'Svensson')

 CREATE TABLE #SourceTable 
 (PersonID INT, FirstName VARCHAR(10), LastName VARCHAR (10))
 INSERT INTO #Destination VALUES (102, 'Diana', 'Richards')
 INSERT INTO #SourceTable VALUES (103, 'Rianna', 'Locks')
 INSERT INTO #SourceTable VALUES (106, 'Cleo', 'Davung')

  DECLARE @PersonID INT
  DECLARE @Firstname VARCHAR (10)
  DECLARE @Lastname VARCHAR (10)

  DECLARE SimpleCursor CURSOR FOR 
  SELECT PersonID, FirstName, LastName 
  FROM #SourceTable

  Open SimpleCursor
  FETCH NEXT FROM SimpleCursor INTO @PersonID, @Firstname, @Lastname
  WHILE @@FETCH_STATUS = 0 
  BEGIN
  IF EXISTS ( SELECT PersonID FROM #Destination
              WHERE PersonID = @PersonID )
    UPDATE #Destination  
    SET #Destination.FirstName = #SourceTable.FirstName,
        #Destination.LastName = #SourceTable.LastName
    FROM #SourceTable
    WHERE #Destination.PersonID = #SourceTable.PersonID
  ELSE
    INSERT INTO #Destination
    SELECT PersonID, Firstname, Lastname FROM #SourceTable

  FETCH NEXT FROM SimpleCursor INTO @PersonID, @Firstname, @Lastname
  END

  CLOSE SimpleCursor
  DEALLOCATE SimpleCursor

SELECT * FROM #Destination

我在这里错过了什么？我没有更新任何东西，而 PersonID 102 和 103 确实存在。

非常感谢。

Answer 1

您没有在 UPDATE 或 INSERT 语句中使用将值提取到其中的变量。尝试:

...
IF EXISTS (SELECT *
                  FROM #Destination
                  WHERE PersonID = @PersonID)
BEGIN
  UPDATE #Destination
         SET FirstName = @FirstName,
             LastName = @LastName
         WHERE PersonID = @PersonID;
END
ELSE
BEGIN
  INSERT INTO #Destination
              (PersonID,
               FirstName,
               LastName)
              VALUES (@PersonID,
                      @FirstName,
                      @LastName);
END;
...