【SQL Server学习笔记】SQL Server中的异常处理

binghai03

　　系统定义错误消息是为响应标准SQL Server错误而引发的消息。用户定义错误消息经常在第三方应用程序中使用，应用程序为了在其内部使用而定义的一组错误消息，这些消息允许参数化，就是可以对消息进行自定义的消息。
　　这个实验很有意思：
declare @v nvarchar(100);  set @v = 'abc!'  --报错 RAISERROR(50002,@v)   --正常 raiserror 50002 'abc!'  /* 消息 18054，级别 16，状态 1，第 2 行 引发了错误 50002，严重性 16，状态 1， 但在 sys.messages 中找不到该错误号的消息。 如果错误号大于 50000，请务必使用 sp_addmessage 添加用户定义的消息。 */ raiserror(50002,16,1,'abc!')　　严重级别范围为1~25，使用下面的分类：
　　1、0-10：表示信息性消息。
　　2、11-16：是用户可以纠正的数据库引擎错误（当执行查询时丢失的数据库对象，不兼容的锁定提示，事务死锁、权限不足、语法错误）。比如，违反主键将返回错误14，被除数为0返回错误16.
　　3、17-19：需要系统管理员注意的错误。比如，内存资源不足，数据库引擎达到极限。
　　4、20-25：致命错误和系统问题，比如，硬件、软件损坏，完整性问题、媒体故障。
RAISERROR ( { msg_id | msg_str | @local_variable }     { ,severity ,state }     [ ,argument [ ,...n ] ] )     [ WITH option [ ,...n ] ]    参数
    msg_id 　　使用 sp_addmessage 存储在 sys.messages 目录视图中的用户定义错误消息号。用户定义错误消息的错误号应当大于 50000。如果未指定msg_id，则 RAISERROR 引发一个错误号为 50000 的错误消息。
msg_str 　　用户定义消息，格式与 C 标准库中的 printf 函数类似。该错误消息最长可以有 2,047 个字符。如果该消息包含的字符数等于或超过 2,048 个，则只能显示前 2,044 个并添加一个省略号以表示该消息已被截断。请注意，由于内部存储行为的缘故，代替参数使用的字符数比输出所显示的字符数要多。例如，赋值为 2 的代替参数%d 实际在消息字符串中生成一个字符，但是还会在内部占用另外三个存储字符串。此存储要求减少了可用于消息输出的字符数。
　　当指定 msg_str 时，RAISERROR 将引发一个错误号为 50000 的错误消息。
　　msg_str 是一个字符串，具有可选的嵌入转换规格。每个转换规格都会定义参数列表中的值如何格式化并将其置于 msg_str 中转换规格位置上的字段中。转换规格的格式如下：
　　% [[flag] [width] [. precision] [{h | l}]] type
　　可在 msg_str 中使用的参数包括：
　　flag
　　用于确定被替换值的间距和对齐的代码。

  代码  前缀或对齐  说明　　-（减号）
　　左对齐
　　在给定字段宽度内左对齐参数值。
　　+（加号）
　　符号前缀
　　如果参数值为有符号类型，则在参数值的前面加上加号（+）或减号（-）。
　　0（零）
　　零填充
　　在达到最小宽度之前在输出前面加上零。如果出现 0 和减号 (-)，将忽略 0。
　　#（数字）
　　对 x 或 X 的十六进制类型使用 0x 前缀
　　当使用 o、x 或 X 格式时，数字符号 (#) 标志在任何非零值的前面分别加上 0、0x 或 0X。当 d、i 或 u 的前面有数字符号 (#) 标志时，将忽略该标志。
　　' '（空格）
　　空格填充
　　如果输出值有符号且为正，则在该值前加空格。如果包含在加号（+）标志中，则忽略该标志。
　　width
　　定义放置参数值的字段的最小宽度的整数。如果参数值的长度等于或大于 width，则打印该值，无需进行填充。如果该值小于 width，则将该值填充到width 中指定的长度。
　　星号 (*) 表示宽度由参数列表中的相关参数指定，该宽度必须为整数值。
　　precision
　　从字符串值的参数值中得到的最大字符数。例如，如果一个字符串具有五个字符并且精度为 3，则只使用字符串值的前三个字符。
　　对于整数值，precision 是指打印的最小位数。
　　星号 (*) 表示精度由参数列表中的相关参数指定，该精度必须为整数值。
　　{h | l} type
　　与字符类型 d、i、o、s、x、X 或 u 一起使用，用于创建 shortint (h) 值或 longint (l) 值。

  类型规范  表示　　d 或 i
　　带符号的整数
　　o
　　无符号的八进制数
　　s
　　字符串
　　u
　　无符号的整数
　　x 或 X
　　无符号的十六进制数
注意：  这些类型规范基于最初为 C 标准库中 printf 函数定义的规范。RAISERROR 消息字符串中使用的类型规范映射到 Transact-SQL 数据类型，而printf 中使用的规范映射到 C 语言数据类型。当 Transact-SQL 不具有与关联 C 数据类型类似的数据类型时，RAISERROR 不支持printf 中使用的类型规范。例如，RAISERROR 不支持用于指针的%p   规范，因为 Transact-SQL 不具有指针数据类型。      注意：  若要将值转换为 Transact-SQL bigint 数据类型，请指定 %I64d。    @ local_variable 　　是一个可以为任何有效字符数据类型的变量，其中包含的字符串的格式化方式与 msg_str 相同。@local_variable 必须为char 或varchar，或者能够隐式转换为这些数据类型。
severity 　　用户定义的与该消息关联的严重级别。当使用 msg_id 引发使用 sp_addmessage 创建的用户定义消息时，RAISERROR 上指定的严重性将覆盖sp_addmessage 中指定的严重性。

　　任何用户都可以指定 0 到 18 之间的严重级别。只有 sysadmin 固定服务器角色成员或具有>    注意：  20 到 25 之间的严重级别被认为是致命的。如果遇到致命的严重级别，客户端连接将在收到消息后终止，并将错误记录到错误日志和应用程序日志。      注意：  小于 0 的严重级别被解释为级别为 0。大于 25 的严重级别被解释为级别为 25。    state 　　0 到 255 的整数。负值或大于 255 的值会生成错误。
　　如果在多个位置引发相同的用户定义错误，则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。
argument 　　用于代替 msg_str 或对应于 msg_id 的消息中的定义的变量的参数。可以有 0 个或多个代替参数，但是代替参数的总数不能超过 20 个。每个代替参数都可以是局部变量或具有下列任一数据类型：tinyint、smallint、int、char、varchar、nchar、nvarchar、binary   或 varbinary。不支持其他数据类型。
option 　　错误的自定义选项，可以是下表中的任一值。

  值  说明　　LOG

　　在 Microsoft SQL Server 数据库引擎实例的错误日志和应用程序日志中记录错误。记录到错误日志的错误目前被限定为最多 440 字节。只有 sysadmin 固定服务器角色成员或具有>　　NOWAIT
　　将消息立即发送给客户端。
　　SETERROR
　　将 @@ERROR 值和 ERROR_NUMBER 值设置为 msg_id 或 50000，不用考虑严重级别。
  注释
  RAISERROR 生成的错误与数据库引擎代码生成的错误的运行方式相同。RAISERROR 指定的值由 ERROR_LINE、ERROR_MESSAGE、ERROR_NUMBER、ERROR_PROCEDURE、ERROR_SEVERITY、ERROR_STATE 以及 @@ERROR 等系统函数来报告。当 RAISERROR 在严重级别为 11 或更高的情况下在 TRY 块中运行，它便会将控制传输至关联的 CATCH   块。如果 RAISERROR 在下列情况下运行，便会将错误返回到调用方：

• 在任何 TRY 块的作用域之外运行。
  
• 在严重级别为 10 或更低的情况下在 TRY 块中运行。
  
• 在严重级别为 20 或更高的情况下终止数据库连接。
  

　　CATCH 块可以使用 RAISERROR 来再次引发调用 CATCH 块的错误，方法是使用 ERROR_NUMBER 和 ERROR_MESSAGE 之类的系统函数检索原始错误消息。对于严重级别为 1 到 10 的消息，@@ERROR 默认值为 0。有关详细信息，请参阅在 Transact-SQL 中使用 TRY...CATCH。
　　当 msg_id 指定 sys.messages 目录视图中可用的用户定义消息时，RAISERROR 按照与应用到使用msg_str 指定的用户定义消息文本的规则相同的规则处理文本列中的消息。用户定义消息文本可以包含转换规格，并且 RAISERROR 将参数值映射到转换规格。使用sp_addmessage 添加用户定义错误消息，而使用sp_dropmessage删除用户定义错误消息。
　　RAISERROR 可以代替 PRINT 将消息返回到调用应用程序。RAISERROR 支持类似于 C 标准库中 printf 函数功能的字符代替，而 Transact-SQL PRINT 语句则不支持。PRINT 语句不受 TRY 块的影响，而在严重级别为 11 到 19 的情况下在 TRY 块中运行的 RAISERROR 会将控制传输至关联的 CATCH 块。指定严重级别为 10 或更低以使用 RAISERROR 返回 TRY 块中的消息，而不必调用 CATCH 块。
　　通常，连续的参数替换连续的转换规格；第一个参数替换第一个转换规格，第二个参数替换第二个转换规格，以此类推。例如，在以下 RAISERROR 语句中，第一个参数N'number' 替换第一个转换规格%s，第二个参数5 替换第二个转换规格%d。
　　RAISERROR (N'This is message %s %d.', -- Message text.           10, -- Severity,           1, -- State,           N'number', -- First argument.           5); -- Second argument.-- The message text returned is: This is message number 5.GO
　　如果为转换规格的宽度或精度指定了星号 (*)，则要用于宽度或精度的值被指定为整数参数值。在这种情况下，一个转换规格最多可以使用三个参数，分别用作宽度、精度和代替值。
　　例如，下列两个 RAISERROR 语句都返回相同的字符串。一个指定参数列表中的宽度值和精度值；另一个指定转换规格中的宽度值和精度值。

　　RAISERROR (N'', -- Message text.           10, -- Severity,           1, -- State,           7, -- First argument used for>--1.创建自定义错误消息 sp_addmessage @msgnum = 50001,  --错误号               @severity = 16,   --严重级别               @msgtext ='procedure up_test occur a error. The error is %s number is %i,                          severity is %i,state is %i,procedure "%s" have a error.',               @lang = 'us_english',  --语言版本               @with_log= 'TRUE',     --记入日志,也可以在实际产生错误时指定是否记入日志               @replace = 'replace'   --替换已经存在的错误号                go   if OBJECT_ID('dbo.up_test') is not null    drop procedure up_test go  create procedure dbo.up_test as  begin try begin transaction delete from dbo.v  select 1/0     commit tran end try  begin catch     declare @error_message nvarchar(1000),             @error_number int,             @error_severity int,             @error_procedure nvarchar(200),             @error_line int          select  @error_message = error_message(),             @error_number  = error_number(),             @error_severity = error_severity(),             @error_procedure = ERROR_PROCEDURE(),             @error_line      = ERROR_LINE();            if(1=1)                 --2.1此消息是预先添加到系统中的用户自定义错误消息，规范错误  raiserror(50001,             16,   1,   @error_message,   @error_number,   @error_severity,   @error_line,   @error_procedure   ) with nowait;     --直接返回错误到客户端  --也可用选项seterror来设置@@error全局变量            else  --2.2直接产生自定义错误，不需要预先添加进消息         raiserror('The error is %s,number is %i,severity is %i,             line is %i,procedure name is %s!', --由于事先没有定义，所以这里是错误消息    16,    1,    @error_message,    @error_number,    @error_severity,    @error_line,    @error_procedure,    ) with log;   --with log:错误会写入sql server错误日志和windows事件日志                  --with nowait:立即把错误消息发送到客户端                  --with seterror:将@error和error_number值设置为错误消息号  rollback; end catch  go  exec up_test    --3.不重新编写存储过程来使用try...catch create procedure proc_wc as  select 1/0 go  --可以这样调用存储过程 begin try  exec proc_wc end try begin catch select [错误号]=ERROR_number(),        [严重级别]=ERROR_SEVERITY(),        [错误行号]=ERROR_LINE(),        [错误消息]=ERROR_MESSAGE(),        [错误的存储过程]=ERROR_PROCEDURE(),        [事务的状态]=XACT_STATE()      -- 0：没有事务打开                                         -- 1：没有错误                                         -- -1：产生了错误，事务必须回滚      end catch    --4.显示所有消息 select * from sys.messages  where message_id = 8134       and language_id = 2052    --5.删除消息 sp_dropmessage @msgnum = 50001,                @lang = 'us_english'