SQL之事务、回滚和TRY

最新推荐文章于 2025-05-26 16:57:40 发布

原创最新推荐文章于 2025-05-26 16:57:40 发布 · 779 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #sql

该博客探讨了SQL中的事务处理，包括如何使用SAVE TRANSACTION创建保存点，处理嵌套事务，以及在出现错误时如何回滚事务。还讨论了在存储过程中检查@@TRANCOUNT和@@ERROR变量来管理事务，以及嵌套TRY...CATCH构造的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

----测试嵌套过程与回滚问题(事务嵌套的层次数（标记）都是存储在@@trancount变量里的)
select top 4 * from dbo.Atype order by rec
/*
--可以使用一个变量来记录事务是否是嵌套的，若是嵌套则应用SAVE来另保存一个事务。
DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
-- Procedure called when there is
-- an active transaction.
-- Create a savepoint to be able
-- to roll back only the work done
-- in the procedure if there is an
-- error.
SAVE TRANSACTION ProcedureSave;
ELSE
-- Procedure must start its own
-- transaction.
BEGIN TRANSACTION;
*/

SAVE TRANSACTION ProcedureSave1; --不会对@@trancount有影响，只是产生一个事务标记
--begin tran upa --@@trancount+1=1
   update dbo.Atype
   set name='修改333s'
   where rec=3
   SAVE TRANSACTION ProcedureSave2;
   --begin tran upb --@@trancount+1=2
       update dbo.Atype
       set name='修改222s'
       where rec=2
       SAVE TRANSACTION ProcedureSave3;
       --begin tran upc --@@trancount+1=3
           update dbo.Atype
           set name='修改111s'
           where rec=1
       --ROLLBACK TRANSACTION ProcedureSave3;
       --rollback tran upc --置@@trancount=0
       --commit tran upc --@@trancount-1=2
       commit tran ProcedureSave3

   ----commit tran upb --@@trancount-1=1
   --ROLLBACK TRANSACTION ProcedureSave2;
   --rollback tran upb --报错找不到事务起点
   commit tran ProcedureSave2

--commit tran upa --@@trancount-1=0
--rollback tran upa --报错找不到事务起点
ROLLBACK TRANSACTION ProcedureSave1;

select top 4 * from dbo.Atype order by rec

--如果前一个语句遇到错误，则返回错误号。
select @@error
go
insert into dbo.Atype
select * from dbo.Atype where Rec=2
go
select @@error
go
--测试返回对本地服务器上执行的当前存储过程的嵌套级别（初始值为 0）
USE s52;
GO
IF OBJECT_ID (N'usp_OuterProc', N'P')IS NOT NULL
DROP PROCEDURE usp_OuterProc;
GO
IF OBJECT_ID (N'usp_InnerProc', N'P')IS NOT NULL
DROP PROCEDURE usp_InnerProc;
GO
CREATE PROCEDURE usp_InnerProc AS
SELECT @@NESTLEVEL AS 'Inner Level';
GO
CREATE PROCEDURE usp_OuterProc AS
SELECT @@NESTLEVEL AS 'Outer Level';
EXEC usp_InnerProc;
GO
EXECUTE usp_OuterProc;
GO

--测试嵌套过程与回滚问题
USE s52;
GO
IF OBJECT_ID (N'usp_OuterProc', N'P')IS NOT NULL
DROP PROCEDURE usp_OuterProc;
GO
IF OBJECT_ID (N'usp_InnerProc', N'P')IS NOT NULL
DROP PROCEDURE usp_InnerProc;
GO
CREATE PROCEDURE usp_InnerProc AS
   DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
SAVE TRANSACTION ProcedureSave;
ELSE
BEGIN TRANSACTION;

                   update dbo.Atype
                   set name='修改一'
                   where rec=1

                       select top 4 * from dbo.Atype order by rec

       if @TranCounter>0
           --commit tran ProcedureSave
           rollback tran ProcedureSave
       else
           --commit tran
           rollback tran

GO
CREATE PROCEDURE usp_OuterProc AS
               begin tran
                   update dbo.Atype
                   set name='修改二'
                   where rec=2

                   EXEC usp_InnerProc;
                   select top 4 * from dbo.Atype order by rec
                   --select * from dbo.Atype111 --查询没有表的错误会引起事务不能回滚。
                   --if @@error<>0
                   rollback tran
GO
select top 5 * from dbo.Atype order by rec
EXECUTE usp_OuterProc;
select top 5 * from dbo.Atype order by rec
GO
execute usp_InnerProc
go

--下面的示例将 @@ERROR 与 @@ROWCOUNT(返回受上一语句影响的行数) 一起使用来验证一条 UPDATE 语句的操作。为任何可能出现的错误而检验 @@ERROR 的值，并且用 @@ROWCOUNT 保证更新已成功应用于表中的某行
--如果前一个语句遇到错误，则返回错误号(由于 @@ERROR 在每一条语句执行后被清除并且重置，因此应在语句验证后立即查看它，或将其保存到一个局部变量中以备以后查看)。
USE s52;
GO
IF OBJECT_ID(N'usp_ChangePurchaseOrderHeader',N'P')IS NOT NULL
DROP PROCEDURE usp_ChangePurchaseOrderHeader;
GO
CREATE PROCEDURE usp_ChangePurchaseOrderHeader
(
@PurchaseOrderID INT
,@BusinessEntityID INT
)
AS
-- Declare variables used in error checking.
DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;

-- Execute the UPDATE statement.
UPDATE PurchaseOrderHeader
SET BusinessEntityID = @BusinessEntityID
WHERE PurchaseOrderID = @PurchaseOrderID;

-- Save the @@ERROR and @@ROWCOUNT values in local
-- variables before they are cleared.
SELECT @ErrorVar = @@ERROR
,@RowCountVar = @@ROWCOUNT;

print @ErrorVar

/*
--将执行多语句返回的错误信息一起保存到一个整型变量里，用于判断整个过程的执行情况。
select @ErrorVar=@ErrorVar+@@ERROR
   IF @ErrorVar <> 0
   BEGIN
       ROLLBACK TRAN UpdateBtype
       RETURN -1
   END
*/
-- Check for errors. If an invalid @BusinessEntityID was specified,
-- the UPDATE statement returns a foreign key violation error #547.
IF @ErrorVar <> 0
BEGIN
IF @ErrorVar = 547
BEGIN
PRINT N'ERROR: Invalid ID specified for new employee.';
RETURN 1;
END
ELSE
BEGIN
PRINT N'ERROR: error '
+ RTRIM(CAST(@ErrorVar AS NVARCHAR(10)))
+ N' occurred.';
RETURN 2;
END
END

-- Check the row count. @RowCountVar is set to 0
-- if an invalid @PurchaseOrderID was specified.
IF @RowCountVar = 0
BEGIN
PRINT 'Warning: The BusinessEntityID specified is not valid';
RETURN 1;
END
ELSE
BEGIN
PRINT 'Purchase order updated with the new employee';
RETURN 0;
END;
GO

--如何使用嵌套 TRY…CATCH 构造
BEGIN TRY
BEGIN TRY
SELECT CAST('invalid_date' AS datetime)
END TRY
BEGIN CATCH
PRINT 'Inner TRY error number: ' +
CONVERT(varchar,ERROR_NUMBER()) + ' on line: ' +
CONVERT(varchar, ERROR_LINE())
END CATCH
SELECT CAST('invalid_int' AS int)
END TRY
BEGIN CATCH
PRINT 'Outer TRY error mumber: ' + CONVERT(varchar,ERROR_NUMBER())+
' on line: ' + CONVERT(varchar, ERROR_LINE())
END CATCH