我正在SQL Server 2012中编写一个存储过程,该过程使用光标读取和TRY CATCH
块中的交易。基本上,我的问题如下:
- 我应该在
TRY CATCH
块内声明光标吗?如果是,我应该在BEGIN TRANSACTION
语句之前或之后声明光标吗? - 我应该在
BEGIN TRANSACTION
语句之前或之后打开光标吗? - 我应该在
COMMIT TRANSACTION
语句之前或之后关闭并处理光标吗? - 如果某些内容失败,我应该在
ROLLBACK TRANSACTION
语句之前或之后关闭光标吗?
示例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;
我会在BEGIN TRY
之前声明并打开光标,然后在END CATCH
之后关闭并进行对其进行交易,以最大程度地减少您在交易中花费的时间。这也意味着您不需要两次编写Close/Deallocation语句。
我的第二个选择是声明并打开BEGIN TRANSACTION
内的光标,然后在ROLLBACK
之前关闭并进行签名。我敢肯定其他人会喜欢这种样式。
这些方式光标完全在尝试/捕获和交易之外,或者完全包含在其中。否则这样做就像在我身上跨越范围,但肯定仍然有效。我认为这个问题主要是样式的问题