我正在 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
最大限度地减少您在交易中花费的时间。这也意味着您不需要编写两次关闭/解除分配语句。
我的第二个选择是在BEGIN TRANSACTION
然后在之前关闭并取消分配ROLLBACK
。我相信其他人会更喜欢这种风格。
通过这些方式,游标要么完全位于 try/catch 和事务之外,要么完全包含在其中。这样做对我来说就像跨越了范围,但肯定仍然有效。我觉得这个问题主要是风格问题
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)