WEB开发网
开发学院数据库MSSQL Server sql server 2005中新增加的try catch学习 阅读

sql server 2005中新增加的try catch学习

 2007-11-11 12:50:52 来源:WEB开发网   
核心提示:sql server(WINDOWS平台上强大的数据库平台) 2005中新增加的try catch,可以很容易捕捉异常了,sql server 2005中新增加的try catch学习,今天大概学习看了下,归纳下要点如下基本用法BEGIN TRY { sql_statement | statement_b

sql server(WINDOWS平台上强大的数据库平台) 2005中新增加的try catch,可以很容易捕捉异常了,今天大概学习看了下,归纳下要点如下

基本用法BEGIN TRY
   {  sql_statement |
 statement_block  }
END TRY
BEGIN CATCH
   {  sql_statement |
 statement_block }
END CATCH
,和普通语言的异常处理用法差不多,但要注意的是,sql server(WINDOWS平台上强大的数据库平台)只捕捉那些不是严重的异常,当比如数据库不能连接等这类异常时,是不能捕捉的一个例子:BEGIN TRY
  DECLARE @X INT
  -- Divide by zero to generate Error
  SET @X = 1/0
  PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
  PRINT 'Error Detected'
END CATCH
PRINT 'Command after TRY/CATCH blocks' 
另外try catch可以嵌套Begin TRY
  delete from GrandParent where Name = 'John Smith'
  print 'GrandParent deleted successfully'
End Try
Begin Catch
  Print 'Error Deleting GrandParent Record'
  Begin Try
   delete from Parent where GrandParentID =
   (select distinct ID from GrandParent where Name = 'John Smith')
   Print 'Parent Deleted Successfully'
  End Try
  Begin Catch
   print 'Error Deleting Parent'
   Begin Try
    delete from child where ParentId =
   (select distinct ID from Parent where GrandParentID =
   (select distinct ID from GrandParent where Name = 'John Smith'))
    print 'Child Deleted Successfully'
   End Try
   Begin Catch
    Print 'Error Deleting Child'
   End Catch
  End Catch
 End Catch
另外,sql server(WINDOWS平台上强大的数据库平台) 2005在异常机制中,提供了error类的方法方便调试,现摘抄如下,比较简单,不予以解释ERROR_NUMBER(): Returns a number associated with the error.ERROR_SEVERITY(): Returns the severity of the error.ERROR_STATE(): Returns the error state number associated with the error.ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger in which the error occurred.ERROR_LINE(): Returns the line number inside the failing routine that caused the error. ERROR_MESSAGE(): Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times. 最后举例子如下,使用了error类的方法BEGIN TRY
  DECLARE @X INT
  -- Divide by zero to generate Error
  SET @X = 1/0
  PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
  PRINT 'Error Detected'
  SELECT ERROR_NUMBER() ERNumber,
     ERROR_SEVERITY() Error_Severity,
     ERROR_STATE() Error_State,
     ERROR_PROCEDURE() Error_Procedure,
     ERROR_LINE() Error_Line,
     ERROR_MESSAGE() Error_Message
END CATCH
PRINT 'Command after TRY/CATCH blocks'
最后输出Error Detected
Err_Num Err_Sev Err_State Err_Proc       Err_Line  Err_Msg
------- ------- --------- -------------------- --------- --------------------------------
8134     16      1 NULL         4     Divide by zero error encountered.

Tags:sql server 中新

编辑录入:coldstar [复制链接] [打 印]
赞助商链接