SQL捕获异常

原文地址 http://technet.microsoft.com/zh-cn/office/ms179296%28v=sql.100%29
 
在 Transact-SQL 中使用 TRY...CATCH

Transact-SQL 代码中的错误可使用 TRY…CATCH 构造处理，此功能类似于 Microsoft Visual C++ 和 Microsoft Visual C# 语言的异常处理功能。TRY…CATCH 构造包括两部分：一个 TRY 块和一个 CATCH 块。如果在 TRY 块内的 Transact-SQL 语句中检测到错误条件，则控制将被传递到 CATCH 块（可在此块中处理此错误）。

CATCH 块处理该异常错误后，控制将被传递到 END CATCH 语句后面的第一个 Transact-SQL 语句。如果 END CATCH 语句是存储过程或触发器中的最后一条语句，则控制将返回到调用该存储过程或触发器的代码。将不执行 TRY 块中生成错误的语句后面的 Transact-SQL 语句。

如果 TRY 块中没有错误，控制将传递到关联的 END CATCH 语句后紧跟的语句。如果 END CATCH 语句是存储过程或触发器中的最后一条语句，控制将传递到调用该存储过程或触发器的语句。

TRY 块以 BEGIN TRY 语句开头，以 END TRY 语句结尾。在 BEGIN TRY 和 END TRY 语句之间可以指定一个或多个 Transact-SQL 语句。

CATCH 块必须紧跟 TRY 块。CATCH 块以 BEGIN CATCH 语句开头，以 END CATCH 语句结尾。在 Transact-SQL 中，每个 TRY 块仅与一个 CATCH 块相关联。

使用 TRY...CATCH

使用 TRY...CATCH 构造时，请遵循下列规则和建议：

  • 每个 TRY...CATCH 构造都必须位于一个批处理、存储过程或触发器中。例如，不能将 TRY 块放置在一个批处理中而将关联的 CATCH 块放置在另一个批处理中。下面的脚本将生成一个错误：

    BEGIN TRY
    SELECT *
        FROM sys.messages
        WHERE message_id = 21;
END TRY
GO
-- The previous GO breaks the script into two batches,
-- generating syntax errors. The script runs if this GO
-- is removed.
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO

  • CATCH 块必须紧跟 TRY 块。

  • TRY…CATCH 构造可以是嵌套式的。这意味着可以将 TRY…CATCH 构造放置在其他 TRY 块和 CATCH 块内。当嵌套的 TRY 块中出现错误时，程序控制将传递到与嵌套的 TRY 块关联的 CATCH 块。

  • 若要处理给定的 CATCH 块中出现的错误，请在指定的 CATCH 块中编写 TRY...CATCH 块。

  • TRY...CATCH 块不处理导致数据库引擎关闭连接的严重性为 20 或更高的错误。但是，只要连接不关闭，TRY...CATCH 就会处理严重性为 20 或更高的错误。

  • 严重性为 10 或更低的错误被视为警告或信息性消息，TRY...CATCH 块不处理此类错误。

  • 即使批处理位于 TRY...CATCH 构造的作用域内，关注消息仍将终止该批处理。分布式事务失败时，Microsoft 分布式事务处理协调器 (MS DTC) 将发送关注消息。MS DTC 用于管理分布式事务。

    注意注意

    如 果在 TRY 块的作用域内执行分布式事务且发生错误，执行将传递到关联的 CATCH 块。分布式事务进入不可提交状态。CATCH 块中的执行可能由管理分布式事务的 Microsoft 分布式事务处理协调器中断。发生错误时，MS DTC 将异步通知参与分布式事务的所有服务器，并终止分布式事务中涉及的所有任务。此类通知以关注消息的形式发送（TRY...CATCH 构造不处理此类通知），批处理将被终止。当批处理完成运行时，数据库引擎将回滚所有不可提交的活动事务。如果事务进入不可提交状态时未发送错误消息，则当 批处理完成时，将向客户端应用程序发送错误消息，该消息指示检测到或回滚了一个不可提交的事务。有关分布式事务的详细信息，请参阅 分布式事务（数据库引擎） 。

错误函数

TRY...CATCH 使用下列错误函数来捕获错误信息：

  • ERROR_NUMBER() 返回错误号。

  • ERROR_MESSAGE() 返回错误消息的完整文本。此文本包括为任何可替换参数（如长度、对象名或时间）提供的值。

  • ERROR_SEVERITY() 返回错误严重性。

  • ERROR_STATE() 返回错误状态号。

  • ERROR_LINE() 返回导致错误的例程中的行号。

  • ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称。

可 以使用这些函数从 TRY...CATCH 构造的 CATCH 块的作用域中的任何位置检索错误信息。如果在 CATCH 块的作用域之外调用错误函数，错误函数将返回 NULL。在 CATCH 块中执行存储过程时，可以在存储过程中引用错误函数并将其用于检索错误信息。如果这样做，则不必在每个 CATCH 块中重复错误处理代码。在下面的代码示例中，TRY 块中的 SELECT 语句将生成一个被零除错误。此错误将由 CATCH 块处理，它将使用存储过程返回错误信息。

USE AdventureWorks2008R2;
GO

-- Verify that the stored procedure does not exist.
IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL
    DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create a procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
GO

BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- Execute the error retrieval routine.
    EXECUTE usp_GetErrorInfo;
END CATCH;
GO

编译错误和语句级重新编译错误

对于与 TRY...CATCH 构造在同一执行级别发生的错误，TRY...CATCH 将不处理以下两类错误：

  • 编译错误，例如阻止批处理执行的语法错误。

  • 语句级重新编译过程中出现的错误，例如由于名称解析延迟而造成在编译后出现对象名解析错误。

当包含 TRY...CATCH 构造的批处理、存储过程或触发器生成其中一种错误时，TRY...CATCH 构造将不处理这些错误。这些错误将返回到调用生成错误的例程的应用程序或批处理。例如，下面的代码示例显示导致语法错误的 SELECT 语句。如果在 SQL Server Management Studio 查询编辑器中执行此代码，则由于批处理无法编译，执行将不启动。错误将返回到查询编辑器，将不会由 TRY...CATCH 捕获。

USE AdventureWorks2008R2;
GO

BEGIN TRY
    -- This PRINT statement will not run because the batch
    -- does not begin execution.
    PRINT N'Starting execution';

    -- This SELECT statement contains a syntax error that
    -- stops the batch from compiling successfully.
    SELECT ** FROM HumanResources.Employee;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

与上述示例中的语法错误不同，语句级重新编译过程中发生的错误不会阻碍批处理进行编译，但是一旦语句重新编译失败，它会立即终止批处理。例如，如果 批处理含有两条语句并且第二条语句引用的表不存在，则延迟的名称解析会使该批处理成功进行编译并开始执行（无需将缺少的表绑定到查询计划），直到重新编译 该语句为止。批处理到达引用缺失表的语句时将停止运行，并返回一个错误。在发生错误的执行级别，TRY...CATCH 构造将不处理此类错误。以下示例对此行为进行了说明。

USE AdventureWorks2008R2;
GO

BEGIN TRY
    -- This PRINT statement will run because the error
    -- occurs at the SELECT statement.
    PRINT N'Starting execution';

    -- This SELECT statement will generate an object name
    -- resolution error because the table does not exist.
    SELECT * FROM NonExistentTable;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

通过执行 TRY 块内单独批处理中的错误生成代码，可以使用 TRY...CATCH 来处理编译或语句级重新编译过程中发生的错误。例如，这可以通过在存储过程中放置代码或使用 sp_executesql 执行动态 Transact-SQL 语句来实现。这使 TRY...CATCH 能够在比错误发生的执行级别更高的执行级别捕获错误。例如，下面的代码显示一个生成对象名解析错误的存储过程。包含 TRY...CATCH 构造的批处理在比存储过程更高的级别执行，并捕获在更低级别发生的错误。

USE AdventureWorks2008R2;
GO

-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ('usp_MyError', 'P') IS NOT NULL
    DROP PROCEDURE usp_MyError;
GO

CREATE PROCEDURE usp_MyError
AS
    -- This SELECT statement will generate
    -- an object name resolution error.
    SELECT * FROM NonExistentTable;
GO

BEGIN TRY
    -- Run the stored procedure.
    EXECUTE usp_MyError;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

下面是结果集：

ErrorNumber ErrorMessage
----------- ---------------------------------------
208         Invalid object name 'NonExistentTable'.

有关详细信息，请参阅 延迟名称解析和编译 以及 执行计划的缓存和重新使用 中的“重新编译执行计划”一节。

不可提交的事务

在 TRY...CATCH 构造中，事务可以进入一种状态：事务保持打开但无法提交。事务无法执行写事务日志的任何操作，例如修改数据或尝试回滚到保存点。但是，在此状态下，事务获 取的锁将被维护，并且连接也保持打开。发出 ROLLBACK 语句之前，或批处理结束并且数据库引擎自动回滚事务之前，不会逆转事务效果。如果事务进入不可提交状态时未发送错误信息，则当批处理完成时，将向客户端应 用程序发送错误消息，该消息指示检测到或回滚了一个不可提交的事务。

发生错误时，事务在 TRY 块内进入无法提交状态，否则此错误将终止该事务。例如，数据定义语言 (DDL) 语句（如 CREATE TABLE）中的大多数错误或 SET XACT_ABORT 设置为 ON 时出现的大多数错误都在 TRY 块外终止事务，而在 TRY 块内使事务无法提交。

CATCH 块中的代码可以通过使用 XACT_STATE 函数来测试事务的状态。如果会话中包含无法提交的事务，XACT_STATE 将返回 -1。如果 XACT_STATE 返回 -1，则 CATCH 块将不能执行写日志的任何操作。下面的代码示例生成 DDL 语句错误，并使用 XACT_STATE 测试事务的状态，以便执行最合适的操作。

USE AdventureWorks2008R2;
GO

-- Verify that the table does not exist.
IF OBJECT_ID (N'my_books', N'U') IS NOT NULL
    DROP TABLE my_books;
GO

-- Create table my_books.
CREATE TABLE my_books
    (
    Isbn        int PRIMARY KEY,
    Title       NVARCHAR(100)
    );
GO

BEGIN TRY
    BEGIN TRANSACTION;
        -- This statement will generate an error because the 
        -- column author does not exist in the table.
        ALTER TABLE my_books
            DROP COLUMN author;
    -- If the DDL statement succeeds, commit the transaction.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;

    -- Test XACT_STATE for 1 or -1.
    -- XACT_STATE = 0 means there is no transaction and
    -- a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. ' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT
            N'The transaction is committable. ' +
            'Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END CATCH;
GO

处理死锁

TRY...CATCH 可用于处理死锁。CATCH 块可以捕获 1205 死锁牺牲品错误，并且事务可以回滚，直至线程解锁。有关死锁的详细信息，请参阅 死锁 。

下面的示例显示如何使用 TRY...CATCH 处理死锁。第一部分创建用于说明死锁状态的表和用于打印错误信息的存储过程。

USE AdventureWorks2008R2;
GO

-- Verify that the table does not exist.
IF OBJECT_ID (N'my_sales',N'U') IS NOT NULL
    DROP TABLE my_sales;
GO

-- Create and populate the table for deadlock simulation.
CREATE TABLE my_sales 
    (
    Itemid       INT PRIMARY KEY,
    Sales        INT not null
    );
GO

INSERT my_sales (itemid, sales) VALUES (1, 1);
INSERT my_sales (itemid, sales) VALUES (2, 1);
GO
  
-- Verify that the stored procedure for error printing
-- does not exist.
IF OBJECT_ID (N'usp_MyErrorLog',N'P') IS NOT NULL
    DROP PROCEDURE usp_MyErrorLog;
GO

-- Create a stored procedure for printing error information.
CREATE PROCEDURE usp_MyErrorLog
AS
    PRINT 
        'Error ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) +
        ', Severity ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) +
        ', State ' + CONVERT(VARCHAR(5), ERROR_STATE()) + 
        ', Line ' + CONVERT(VARCHAR(5), ERROR_LINE());
    PRINT 
        ERROR_MESSAGE();
GO

下面的会话 1 和会话 2 的代码脚本在两个单独的 SQL Server Management Studio 连接下同时运行。两个会话都尝试更新表中的相同行。在第一次尝试过程中，其中一个会话将成功完成更新操作，而另一个会话将被选择为死锁牺牲品。死锁牺牲品 错误将使执行跳至 CATCH 块，事务将进入无法提交状态。在 CATCH 块中，死锁牺牲品会回滚事务并重试更新此表，直到更新成功或达到了重试限制（以先发生者为准）。

会话 1

会话 2

USE AdventureWorks2008R2;
GO

-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
SET @retry = 5;

-- Keep trying to update 
-- table if this task is 
-- selected as the deadlock 
-- victim.
WHILE (@retry > 0)
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 1;

        WAITFOR DELAY '00:00:13';
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 2;

        SET @retry = 0;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH 
        -- Check error number.
        -- If deadlock victim error,
        -- then reduce retry count
        -- for next update retry. 
        -- If some other error
        -- occurred, then exit
        -- retry WHILE loop.
        IF (ERROR_NUMBER() = 1205)
            SET @retry = @retry - 1;
        ELSE
            SET @retry = -1;

        -- Print error information.
        EXECUTE usp_MyErrorLog;
  
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
    END CATCH;
END; -- End WHILE loop.
GO
 
USE AdventureWorks2008R2;
GO

-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
SET @retry = 5;

--Keep trying to update 
-- table if this task is 
-- selected as the deadlock 
-- victim.
WHILE (@retry > 0)
BEGIN
    BEGIN TRY
       BEGIN TRANSACTION;
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 2;

        WAITFOR DELAY '00:00:07';
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 1;

        SET @retry = 0;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH 
        -- Check error number.
        -- If deadlock victim error,
        -- then reduce retry count
        -- for next update retry. 
        -- If some other error
        -- occurred, then exit
        -- retry WHILE loop.
        IF (ERROR_NUMBER() = 1205)
            SET @retry = @retry - 1;
        ELSE
            SET @retry = -1;

        -- Print error information.
        EXECUTE usp_MyErrorLog;
  
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
    END CATCH;
END; -- End WHILE loop.
GO

使用 RAISERROR 的 TRY...CATCH

RAISERROR 可用在 TRY...CATCH 构造的 TRY 或 CATCH 块中影响错误处理行为。

在 TRY 块内执行的严重性为 11 至 19 的 RAISERROR 会使控制传递到关联的 CATCH 块。在 CATCH 块内执行的严重性为 11 至 19 的 RAISERROR 将错误返回到调用应用程序或批处理。这样，RAISERROR 可用于返回有关导致 CATCH 块执行的错误的调用方信息。TRY...CATCH 错误函数提供的错误信息（包括原始错误号）可在 RAISERROR 消息中捕获；但是，RAISERROR 的错误号必须 >= 50000。

严重性为 10 或更低的 RAISERROR 在不调用 CATCH 块的情况下将信息性消息返回到调用批处理或应用程序。

严重性为 20 或更高的 RAISERROR 在不调用 CATCH 块的情况下关闭数据库连接。

下面的代码示例显示如何在 CATCH 块内使用 RAISERROR 将原始错误信息返回到调用应用程序或批处理。存储过程 usp_GenerateError 在 TRY 块内执行 DELETE 语句，该语句生成违反约束错误。此错误使执行传递到 usp_GenerateError 内关联的 CATCH 块，存储过程 usp_RethrowError 在此块内执行以使用 RAISERROR 生成违反约束错误。RAISERROR 生成的此错误将返回到调用批处理（usp_GenerateError 在其中执行）并使执行传递到调用批处理中关联的 CATCH 块。

注意注意

RAISERROR 仅能生成状态从 1 到 127 的错误。由于数据库引擎可能引发状态为 0 的错误，因此，建议您先检查由 ERROR_STATE 返回的错误状态，然后将它作为一个值传递给状态参数 RAISERROR。

 
USE AdventureWorks2008R2;
GO

-- Verify that stored procedure does not exist.
IF OBJECT_ID (N'usp_RethrowError',N'P') IS NOT NULL
    DROP PROCEDURE usp_RethrowError;
GO

-- Create the stored procedure to generate an error using 
-- RAISERROR. The original error information is used to
-- construct the msg_str for RAISERROR.
CREATE PROCEDURE usp_RethrowError AS
    -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Build the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR 
        (
        @ErrorMessage, 
        @ErrorSeverity, 
        1,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
GO

-- Verify that stored procedure does not exist.
IF OBJECT_ID (N'usp_GenerateError',N'P') IS NOT NULL
    DROP PROCEDURE usp_GenerateError;
GO

-- Create a stored procedure that generates a constraint violation
-- error. The error is caught by the CATCH block where it is 
-- raised again by executing usp_RethrowError.
CREATE PROCEDURE usp_GenerateError 
AS 
    BEGIN TRY
        -- A FOREIGN KEY constraint exists on the table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;
    END TRY
    BEGIN CATCH
        -- Call the procedure to raise the original error.
        EXEC usp_RethrowError;
    END CATCH;
GO

-- In the following batch, an error occurs inside 
-- usp_GenerateError that invokes the CATCH block in
-- usp_GenerateError. RAISERROR inside this CATCH block
-- generates an error that invokes the outer CATCH
-- block in the calling batch.
BEGIN TRY  -- outer TRY
    -- Call the procedure to generate an error.
    EXECUTE usp_GenerateError;
END TRY
BEGIN CATCH  -- Outer CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH;
GO

更改执行流

posted @ 2012-11-01 10:15  伤心木乃伊  阅读(1872)  评论(0)  编辑  收藏  举报