sql-server - 当更新游标第二次运行时会发生什么，为什么？

Question

我想知道当我在 SQL Server 中运行更新游标两次时会发生什么。

我已经运行了两次更新游标。我第一次运行游标时，它给了我 19370 行需要更新。当我第二次运行同一个游标时，它只给了我一行需要更新的信息。

set nocount on;

declare @playerID varchar(255)
declare @total_g_all int

declare @today date
set @today = (CONVERT(date, getdate()))

declare @updateCount bigint
set @updateCount = 0

declare @stop int
set @stop = 0

print @today
Print 'Transaction Update Command Start Time - ' + (CAST(convert(varchar, getdate(), 108) AS nvarchar(30)))
--Declare Cursor
declare update_cursor cursor static for 
    select app.playerID, sum(G_all) as total_g_all
    from Appearances app right outer join People ppl
    on app.playerID = ppl.playerID
    where (jnk22_Date_Last_Update != @today or jnk22_Date_Last_Update is null)
    group by app.playerID
Select @@CURSOR_ROWS as 'Number of Cursor Rows After Declare'
Print 'Declare Cursor Complete Time - ' + (CAST(convert(varchar, getdate(), 108) AS nvarchar(30)))

--Open Cursor
open update_cursor

--c. Selects the system variable @@Cursor_Rows after you open the CURSOR so you can see how many rows are in the cursor. 
Select @@CURSOR_ROWS as 'Number of Cursor Rows'  
--19183 rows

--b. updates the NJIT_Total_Games_Played with the sum of G_ALL column from the cursor and sets NJIT_Date_Last_Update to the 
--   current date.
fetch next from update_cursor into @playerID, @total_g_all
while @@FETCH_STATUS = 0 and @stop = 0
begin
    if @updateCount = 0
        BEGIN 
            PRINT 'Begin Transaction At Record - ' + RTRIM(CAST(@updateCount AS nvarchar(30))) + ' At - ' + 
                (CAST(convert(varchar, getdate(), 108) AS nvarchar(30)))
        BEGIN TRANSACTION
    END
    update People
    set jnk22_Total_Games_Played = @total_g_all where @playerID = playerID

    update People
    set jnk22_Date_Last_Update = @today where @playerID = playerID

    set @updateCount = @updateCount + 1

--d. Prints the start and stop date and time as well as the  # of records updated with the date and time for every 1,000 
--   records updated
    IF @updateCount % 1000 = 0 
        BEGIN
            PRINT 'START TRANSACTION - ' + RTRIM(CAST(@updateCount AS nvarchar(30))) + ' At - ' + 
                            (CAST(convert(varchar, getdate(), 108) AS nvarchar(30)))
            PRINT 'COMMIT TRANSACTION - ' + RTRIM(CAST(@updateCount AS nvarchar(30))) + ' At - ' + 
                            (CAST(convert(varchar, getdate(), 108) AS nvarchar(30)))
            COMMIT TRANSACTION
            BEGIN TRANSACTION
            PRINT 'STOP TRANSACTION - ' + RTRIM(CAST(@updateCount AS nvarchar(30))) + ' At - ' + 
                            (CAST(convert(varchar, getdate(), 108) AS nvarchar(30)))
        END
    FETCH NEXT FROM update_cursor INTO @playerID, @total_g_all
    END

    IF @stop <> 1
    BEGIN
            PRINT 'Final Commit Transaction For Record - ' + RTRIM(CAST(@updateCount AS nvarchar(30))) + ' At - ' + 
                        (CAST(convert(varchar, getdate(), 108) AS nvarchar(30)))
            COMMIT TRANSACTION
            BEGIN TRANSACTION
    END

    IF @stop = 1
    BEGIN
        PRINT 'Rollback started For Transaction at Record - ' + RTRIM(CAST(@updateCount AS nvarchar(30))) + ' At - ' +
                                    (CAST(convert(varchar, getdate(), 108) AS nvarchar(30)))
        Rollback TRANSACTION
    END

--e. Closes and deallocates the cursor as the last step in the script.
CLOSE update_cursor
DEALLOCATE update_cursor
print 'Transaction Update Command End Date - ' + (CAST(convert(date, getdate(), 108) AS nvarchar(30)))
Print 'Transaction Update Command End Time - ' + (CAST(convert(varchar, getdate(), 108) AS nvarchar(30)))

set nocount off;

实际结果是“1”=> 游标运行两次时更新的行数。我想知道为什么。

标签： sql-servercursor