在事务内部或外部打开和关闭游标，以及在事务失败时如何关闭游标 [英] Open and Close Cursors Inside or Outside a Transaction and How to Close a Cursor if a Transaction Fails

问题描述

我正在SQL Server 2012中编写一个存储过程，该过程使用游标读取并在 TRY CATCH 块内进行事务。基本上，我的问题如下：

I am writing a stored procedure in SQL Server 2012 that uses a cursor for reading and a transaction inside a TRY CATCH block. Basically, my questions are as follows:

1. 我应该在 TRY CATCH 阻止？如果是，我应该在 BEGIN TRANSACTION 语句之前还是之后声明游标吗？


2. 我应该在 BEGIN TRANSACTION 语句？


3. 我应该在 COMMIT TRANSACTION 语句？


4. 如果出现故障，我应该在 ROLLBACK TRANSACTION 语句之前或之后关闭并释放游标吗？

1. Should I declare my cursor inside the TRY CATCH block? If yes, should I declare the cursor before or after the BEGIN TRANSACTION statement?
2. Should I open the cursor before or after the BEGIN TRANSACTION statement?
3. Should I close and deallocate the cursor before or after the COMMIT TRANSACTION statement?
4. Should I close and deallocate the cursor before or after the ROLLBACK TRANSACTION statement if something fails?

示例T-SQL代码：

DECLARE @ColumnID AS INT;
DECLARE @ColumnName AS VARCHAR(20);
DECLARE @ColumnValue AS FLOAT;

-- Should I declare my cursor inside the TRY CATCH block?
-- If yes, should I declare the cursor before or after the BEGIN TRANSACTION statement?

DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT
        a.ColumnID,
        a.ColumnName,
        a.ColumnValue

    FROM
        MyTable a;

BEGIN TRY

    -- Should I open the cursor before or after the BEGIN TRANSACTION statement?

    BEGIN TRANSACTION myTransaction;

    OPEN myCursor;

    FETCH NEXT FROM myCursor INTO @ColumnID, @ColumnName, @ColumnValue;

    WHILE @@FETCH_STATUS = 0 BEGIN

        IF (@ColumnName IS NULL) BEGIN

            UPDATE
                MyTable

            SET
                @ColumnValue = NULL

            WHERE
                ColumnID = @ColumnID;

        END;

        FETCH NEXT FROM myCursor INTO @ColumnID, @ColumnName, @ColumnValue;

    END;

    -- Should I close and deallocate the cursor before or after the COMMIT TRANSACTION statement?

    CLOSE myCursor;
    DEALLOCATE myCursor;

    COMMIT TRANSACTION myTransaction;

END TRY
BEGIN CATCH

    -- Should I close and deallocate the cursor before or after the ROLLBACK TRANSACTION statement:

    IF CURSOR_STATUS('local', 'myCursor') = 1 BEGIN

        CLOSE myCursor;
        DEALLOCATE myCursor;

    END;

    ROLLBACK TRANSACTION myTransaction;

END CATCH;

推荐答案

我会声明并打开<$之前的游标c $ c> BEGIN TRY ，然后在 END CATCH 之后关闭并重新分配它，以最大程度地减少您在交易中花费的时间。这也意味着您不需要两次编写close / deallocate语句。

I would declare and open the cursor before the BEGIN TRY and then close and deallocate it after the END CATCH to minimize the amount of time you're spending in the transaction. This also means you don't need to write the close/deallocate statements twice.

我的第二个选择是声明并打开开始交易，然后在 ROLLBACK 之前关闭并取消分配。我确信其他人会喜欢这种样式。

My second choice would be to declare and open the cursor inside the BEGIN TRANSACTION and then close and deallocate before the ROLLBACK. I'm sure others will prefer this style.

这些方式使光标完全位于try / catch和transaction之外，或完全包含在try / catch和transaction中。否则，这样做对我来说就像跨过范围，但肯定仍然可以。我认为这个问题主要是风格问题

These ways the cursor is either entirely outside the try/catch and transaction or entirely contained within them. Doing it otherwise feels like crossing scopes to me, but would certainly still work. I think this question is mainly an issue of style

这篇关于在事务内部或外部打开和关闭游标，以及在事务失败时如何关闭游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！