欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SQL Server 2005 中使用 Try Catch 處理異常

 更新時間：2016年05月03日 09:20:04   作者：OpenSource  
本文主要對比了Sql Server 2000和Sql Server 2005對異常處理的方法，Sql Server 2005 之后的TRY CATCH捕捉異常，更類似C#、JAVA等語言，更容易被程序員理解。

TRY...CATCH是Sql Server 2005/2008令人印象深刻的新特性.提高了開發(fā)人員異常處理能力.沒有理由不嘗試一下Try.. Catch功能.

* TRY 塊 - 包含可能產生異常的代碼或腳本
* CATCH 塊 - 如果TRY塊出現(xiàn)異常,代碼處理流將被路由到CATCH塊.在這里你可以處理異常,記錄日志等.
Sql Server中的Try Catch和C#,JAVA等語言的處理方式一脈相承.這種一致性才是最大的創(chuàng)新之處.

一、SQL SERVER 2000中異常處理

CREATE PROC usp_AccountTransaction 
 
  @AccountNum INT, 
 
  @Amount DECIMAL 
 
AS 
 
BEGIN 
 
  BEGIN TRANSACTION --beginning a transaction.. 
 
    UPDATE MyChecking SET Amount = Amount - @Amount 
 
      WHERE AccountNum = @AccountNum 
 
    IF @@ERROR != 0 --check @@ERROR variable after each DML statements.. 
 
    BEGIN 
 
      ROLLBACK TRANSACTION --RollBack Transaction if Error.. 
 
      RETURN 
 
    END 
 
    ELSE 
 
    BEGIN 
 
      UPDATE MySavings SET Amount = Amount + @Amount 
 
        WHERE AccountNum = @AccountNum 
 
      IF @@ERROR != 0 --check @@ERROR variable after each DML statements.. 
 
      BEGIN 
 
        ROLLBACK TRANSACTION --RollBack Transaction if Error.. 
 
        RETURN 
 
      END 
 
      ELSE 
 
      BEGIN 
 
        COMMIT TRANSACTION --finally, Commit the transaction if Success.. 
 
        RETURN 
 
      END 
 
    END 
 
END 
 
GO

上面是Sql server 2000的一個存儲過程,在每個數(shù)據(jù)庫操作之后立即必須檢查@@ERROR,進行Commit / RollBack該事務.
Sql server 2000中監(jiān)測錯誤,只能通過監(jiān)測全局遍歷 @@ERROR.由于@@ERROR會被下一個數(shù)據(jù)庫操作所覆蓋. 所以在每次操作完后必須立即監(jiān)測.

二、SQL SERVER 2005中異常處理

TRY...CATCH是SQL Server 2005提供的更具有可讀性的語法.每個開發(fā)人員都熟悉這種寫法.SQL Server 2005仍然支持@@ERROR這種用法.

1.try catch語法: 

BEGIN TRY 
 
  Try Statement 1 
 
  Try Statement 2 
 
  ... 
 
  Try Statement M 
 
END TRY 
 
BEGIN CATCH 
 
  Catch Statement 1 
 
  Catch Statement 2 
 
  ... 
 
  Catch Statement N 
 
END CATCH

2.獲得錯誤信息的函數(shù)表:

下面系統(tǒng)函數(shù)在CATCH塊有效.可以用來得到更多的錯誤信息:

函數(shù) 描述

ERROR_NUMBER() 返回導致運行 CATCH 塊的錯誤消息的錯誤號。
ERROR_SEVERITY() 返回導致 CATCH 塊運行的錯誤消息的嚴重級別
ERROR_STATE() 返回導致 CATCH 塊運行的錯誤消息的狀態(tài)號
ERROR_PROCEDURE() 返回出現(xiàn)錯誤的存儲過程名稱
ERROR_LINE() 返回發(fā)生錯誤的行號
ERROR_MESSAGE() 返回導致 CATCH 塊運行的錯誤消息的完整文本

簡單示例:

BEGIN TRY 
 
  SELECT GETDATE() 
 
  SELECT 1/0--Evergreen divide by zero example! 
 
END TRY 
 
BEGIN CATCH 
 
  SELECT 'There was an error! ' + ERROR_MESSAGE() 
 
  RETURN 
 
END CATCH;

3.try catch回滾/提交事務的示例

ALTER PROC usp_AccountTransaction 
 
  @AccountNum INT, 
 
  @Amount DECIMAL 
 
AS 
 
BEGIN 
 
  BEGIN TRY --Start the Try Block.. 
 
    BEGIN TRANSACTION -- Start the transaction.. 
 
      UPDATE MyChecking SET Amount = Amount - @Amount 
 
        WHERE AccountNum = @AccountNum 
 
      UPDATE MySavings SET Amount = Amount + @Amount 
 
        WHERE AccountNum = @AccountNum 
 
    COMMIT TRAN -- Transaction Success! 
 
  END TRY 
 
  BEGIN CATCH 
 
    IF @@TRANCOUNT > 0 
 
      ROLLBACK TRAN --RollBack in case of Error 
 
    -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception 
 
    RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1) 
 
  END CATCH 
 
END 
 
GO

三、實例講解

創(chuàng)建錯誤日志表:

CREATE TABLE ErrorLog(errNum INT,ErrSev NVARCHAR(1000),ErrState INT,ErrProc NVARCHAR(1000),ErrLine INT, ErrMsg NVARCHAR(2000))

創(chuàng)建錯誤日志記錄存儲過程:

CREATE PROCEDURE ErrorLog
AS 
   SELECT ERROR_NUMBER() AS ErrNum,ERROR_SEVERITY()AS ErrSev,ERROR_STATE() AS ErrState,ERROR_PROCEDURE() AS ErrProc,ERROR_LINE()AS ErrLine,ERROR_MESSAGE()AS ErrMsg 
   INSERT 
   INTO ErrorLog 
   VALUES(ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE())
GO

寫一個存儲過程吧!里面使用一下Try Catch:

USE [Your_Test]
GO
/****** Object: StoredProcedure [dbo].[getTodayBirthday]  
    Script Date: 05/17/2010 15:38:46 
    Author:jinho
    Desc:獲取當天生日的所有人
    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getTodayBirthday]
AS
BEGIN TRY
 declare @today datetime; 
 SET @today = GETDATE();--獲取今天的日期
 DECLARE @day VARCHAR(2);
 SET @day =REPLACE(DAY(@today),0,'');
 DECLARE @month VARCHAR(2) ;
 SET @month = REPLACE(month(@today),0,'');
 DECLARE @year VARCHAR(4);
 SET @year = YEAR(@today);
 SELECT * FROM dbo.UserInfo WHERE REPLACE(DAY(CONVERT(DATETIME,Birthday )),0,'') =@day AND REPLACE(MONTH(CONVERT(DATETIME,Brithday)),0,'')=@month AND Birthday IS NOT NULL 

 END TRY
 BEGIN CATCH
 ErrorLog --調用上面的存儲過程,保存錯誤日志
 END CATCH

說明:ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE() 這幾個函數(shù)只能用在Catch里面!

相關文章

最新評論