SQL Server 2008中SQL应用系列--目录索引在SQL Server 2000中我们一般使用RaiseErrorhttp://msdn.microsoft.com/zh-cn/library/ms177497.aspx来抛出错误交给应用程序来处理。看MSDN示例http://msdn.microsoft.com/zh-cn/library/aa238452%28vsql.80%29.aspx自从SQL Server 2005集成Try…Catch功能以后我们使用时更加灵活到了SQL Server 2012更推出了强大的THROW处理错误显得更为精简。本文对此作一个小小的展示。首先我们假定两个基本表如下--创建两个测试表 IF NOT OBJECT_ID(Score) IS NULL DROP TABLE [Score] GO IF NOT OBJECT_ID(Student) IS NULL DROP TABLE [Student] GO CREATE TABLE Student (stuid int NOT NULL PRIMARY KEY, stuName Nvarchar(20) ) CREATE TABLE Score (stuid int NOT NULL REFERENCES Student(stuid),--外键 scoreValue int ) GO INSERT INTO Student VALUES (101,胡一刀) INSERT INTO Student VALUES (102,袁承志) INSERT INTO Student VALUES (103,陈家洛) INSERT INTO student VALUES (104,张三丰) GO SELECT * FROM Student /* stuid stuName 101 胡一刀 102 袁承志 103 陈家洛 104 张三丰 */我们从一个最简单的例子入手例一/********* 调用运行时错误 ***************/ /********* 3wlive.cn 邀月***************/ SET XACT_ABORT OFF BEGIN TRAN INSERT INTO Score VALUES (101,80) INSERT INTO Score VALUES (102,87) INSERT INTO Score VALUES (107, 59) /* 外键错误 */ -----SELECT 1/0 /* 除数为0错误 */ INSERT INTO Score VALUES (103,100) INSERT INTO Score VALUES (104,99) COMMIT TRAN GO先不看结果我想问一下该语句执行完毕后Score表会插入几条记录估计可能有人说是2条有人说0条也可能有人说4条。实际上我希望是0条但结果是4条/* (1 row(s) affected) (1 row(s) affected) Msg 547, Level 16, State 0, Line 5 The INSERT statement conflicted with the FOREIGN KEY constraint FK__Score__stuid__01D345B0. The conflict occurred in database testDb2, table dbo.Student, column stuid. The statement has been terminated. (1 row(s) affected) (1 row(s) affected) */ SELECT * from Score /* stuid scoreValue 101 80 102 87 103 100 104 99 */我对这个结果也有点惊讶我希望它出错回滚于是修改例二/********* 调用运行时错误 ***************/ /********* 3wlive.cn 邀月***************/ TRUNCATE table Score GO SET XACT_ABORT OFF BEGIN TRAN INSERT INTO Score VALUES (101,80) INSERT INTO Score VALUES (102,87) INSERT INTO Score VALUES (107, 59) /* 外键错误 */ ----SELECT 1/0 --INSERT INTO Score VALUES (103,100) --INSERT INTO Score VALUES (104,99) PRINT ERROR是:cast(ERROR as nvarchar(10)) IF ERROR0 ROLLBACK TRAN ELSE COMMIT TRAN GO我先提示一下大家这个语句中的ERROR值是547那么此时Score表中有几条记录?答案是2条可能有人开始摇头了那么问题的关键在哪儿呢?对就是这个“XACT_ABORT ”开关查MSDNSET XACT_ABORT Transact-SQL - SQL Server | Microsoft Learn官方解释它用于指定当 Transact-SQL 语句出现运行时错误时SQL Server 是否自动回滚到当前事务。当 SET XACT_ABORT 为 ON 时如果执行 Transact-SQL 语句产生运行时错误则整个事务将终止并回滚。当 SET XACT_ABORT 为 OFF 时有时只回滚产生错误的 Transact-SQL 语句而事务将继续进行处理。如果错误很严重那么即使 SET XACT_ABORT 为 OFF也可能回滚整个事务。 OFF 是默认设置。编译错误如语法错误不受 SET XACT_ABORT 的影响。对于大多数 OLE DB 访问接口包括 SQL Server必须将隐式或显示事务中的数据修改语句中的 XACT_ABORT 设置为 ON。 唯一不需要该选项的情况是在提供程序支持嵌套事务时。这里红色的一句话是关键那么“有时”究竟是指什么时候呢查资料知数据库引擎错误严重性 - SQL Server | Microsoft Learn大致分为以下四个级别当等级SEVERITY为0-10时为“信息性消息”最轻。当等级为11-16时为“用户可以纠正的数据库引擎错误”。如除数为零等级为16当等级为17-19时为“需要DBA注意的错误”。如内存不足、数据库引擎已到极限等。当等级为20-25时为“致命错误或系统问题”。如硬件或软件损坏、完整性问题、媒体故障等。用户也可以自定义错误级别和类型。根据以上解释我们最保险的方式是Set XACT_ABORT ON。当然使用Try…Catch在Set XACT_ABORT OFF时也能按照我们的意愿回滚。例三/********* 使用Try Catch 构造一个错误记录 ***************/ /********* 3wlive.cn 邀月 ***************/ SET XACT_ABORT OFF BEGIN TRY BEGIN TRAN INSERT INTO Score VALUES (101,80) INSERT INTO Score VALUES (102,87) INSERT INTO Score VALUES (107, 59) /* 外键错误 */ INSERT INTO Score VALUES (103,100) INSERT INTO Score VALUES (104,99) COMMIT TRAN PRINT 事务提交 END TRY BEGIN CATCH ROLLBACK PRINT 事务回滚 --构造一个错误信息记录 SELECT ERROR_NUMBER() AS 错误号, ERROR_SEVERITY() AS 错误等级, ERROR_STATE() as 错误状态, DB_ID() as 数据库ID, DB_NAME() as 数据库名称, ERROR_MESSAGE() as 错误信息; END CATCH GO这个返回结果比较另类它其实是一条拼凑起来的记录。记录并没有新增因为Catch到错误而事务回滚了。使用RaiseError也可以把出错的信息抛给应用程序来处理。例四/********* 使用RaiseError 提交一个错误信息***************/ /********* 3wlive.cn 邀月 ***************/ SET XACT_ABORT OFF BEGIN TRY BEGIN TRAN INSERT INTO Score VALUES (101,80) INSERT INTO Score VALUES (102,87) INSERT INTO Score VALUES (107, 59) /* 外键错误 */ INSERT INTO Score VALUES (103,100) INSERT INTO Score VALUES (104,99) COMMIT TRAN PRINT 事务提交 END TRY BEGIN CATCH ROLLBACK PRINT 事务回滚;--构造一个错误信息记录 DECLARE ErrorMessage NVARCHAR(4000); DECLARE ErrorSeverity INT; DECLARE ErrorState INT; SELECT ErrorMessage ERROR_MESSAGE(), ErrorSeverity ERROR_SEVERITY(), ErrorState ERROR_STATE(); RAISERROR (ErrorMessage, -- Message text. ErrorSeverity, -- Severity. ErrorState -- State. ); END CATCH GO或者直接使用Throw也能达到RaiseError同样的效果而且这是微软推崇的方式其官方解释为“THROW 语句支持 SET XACT_ABORT但 RAISERROR 不支持。 新应用程序应该改用 THROW而不使用 RAISERROR。”其实可能是微软在忽悠因为其实RaiseError也支持Set XACT_ABORT。例五/********* SQL 2012新增的Throw ***************/ /********* 3wlive.cn 邀月***************/ SET XACT_ABORT OFF BEGIN TRY BEGIN TRAN INSERT INTO score VALUES (101,80) INSERT INTO score VALUES (102,87) INSERT INTO score VALUES (107, 59) /* 外键错误 */ INSERT INTO score VALUES (103,100) INSERT INTO score VALUES (104,99) COMMIT TRAN PRINT 事务提交 END TRY BEGIN CATCH ROLLBACK; PRINT 事务回滚; Throw; END CATCH GO不过说实话Throw好像很简练。说到这里我有一个疑问例四和例五的查询结果相同/* (1 row(s) affected) (1 row(s) affected) (0 row(s) affected) 事务回滚 Msg 547, Level 16, State 0, Line 13 The INSERT statement conflicted with the FOREIGN KEY constraint FK__Score__stuid__18B6AB08. The conflict occurred in database testDb2, table dbo.Student, column stuid. */虽然因为回滚而没有插入数据但是两个“(1 row(s) affected) ”还是让我吃了一惊哪位高手能告诉我一下这影响的两行SQL Server究竟是怎么处理的先谢过了。既然错误已经被捕获那么有两种处理方式一是直接在数据库中记录到表中。比如我们可以建立一个数据库DBErrorLogs,/********* 生成错误日志记录表 ******/ /********* 3wlive.cn 邀月***************/ CREATE database DBErrorLogs GO USE DBErrorLogs GO CREATE TABLE [dbo].[ErrorLog]( [nId] [bigint] IDENTITY(101,1) NOT NULL PRIMARY KEY, [dtDate] [datetime] NOT NULL, [sThread] [varchar](100) NOT NULL, [sLevel] [varchar](200) NOT NULL, [sLogger] [varchar](500) NOT NULL, [sMessage] [varchar](3000) NOT NULL, [sException] [varchar](4000) NULL ) GO ALTER TABLE [dbo].[ErrorLog] ADD DEFAULT (getdate()) FOR [dtDate] GO在出错时直接插入相应信息到该表中即可。另外一种思路是交给应用程序来处理比如下例中我们用C#捕获错误并用log4net记录回数据库中。C#中有相应的SQLException类封装了相应的Error的等级、编号、出错信息等真心方便。using System; using System.Text; using System.Data.SqlClient; using System.Data; namespace RaiseErrorDemo_Csharp { public class Program { #region Define Members private static log4net.ILog myLogger log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); static string conn Data SourceAP4\\Net2012;Initial CatalogTestdb2;Integrated SecurityTrue; static string sql_RaiseError /********* 使用RaiseError 提交一个错误信息***************/ /********* 3wlive.cn 邀月 ***************/ SET XACT_ABORT OFF BEGIN TRY BEGIN TRAN INSERT INTO Score VALUES (101,80) INSERT INTO Score VALUES (102,87) INSERT INTO Score VALUES (107, 59) /* 外键错误 */ INSERT INTO Score VALUES (103,100) INSERT INTO Score VALUES (104,99) COMMIT TRAN PRINT 事务提交 END TRY BEGIN CATCH ROLLBACK PRINT 事务回滚;--构造一个错误信息记录 DECLARE ErrorMessage NVARCHAR(4000); DECLARE ErrorSeverity INT; DECLARE ErrorState INT; SELECT ErrorMessage ERROR_MESSAGE(), ErrorSeverity ERROR_SEVERITY(), ErrorState ERROR_STATE(); RAISERROR (ErrorMessage, -- Message text. ErrorSeverity, -- Severity. ErrorState -- State. ); END CATCH ; static string sql_Throw SET XACT_ABORT OFF BEGIN TRY BEGIN TRAN INSERT INTO score VALUES (101,80) INSERT INTO score VALUES (102,87) INSERT INTO score VALUES (107, 59) /* 外键错误 */ INSERT INTO score VALUES (103,100) INSERT INTO score VALUES (104,99) COMMIT TRAN PRINT 事务提交 END TRY BEGIN CATCH ROLLBACK; PRINT 事务回滚; Throw; END CATCH ; #endregion #region Methods /// summary /// 主函数 /// /summary /// param nameargs/param static void Main(string[] args) { CatchSQLError(sql_RaiseError); Console.WriteLine(-----------------------------------------------); CatchSQLError(sql_Throw); Console.ReadKey(); } /// summary /// 捕获错误信息 /// /summary /// param namestrSQL/param public static void CatchSQLError(string strSQL) { string connectionString conn; SqlConnection connection new SqlConnection(connectionString); SqlCommand cmd2 new SqlCommand(strSQL, connection); cmd2.CommandType CommandType.Text; try { connection.Open(); cmd2.ExecuteNonQuery(); } catch (SqlException err) { string strErr GetPreError(err.Class); //显示出错信息 Console.WriteLine(错误等级: err.Class Environment.NewLine strErr err.Message); //记录错误到数据库中 myLogger.Error(strErr, err); } finally { connection.Close(); } } /// summary /// 辅助函数 /// /summary /// param nameb/param /// returns/returns public static string GetPreError(byte b) { string strErr string.Empty; if (b 0 b 10) { strErr 信息性信息:; } else if (b 11 b 16) { strErr 用户可以纠正的数据库引擎错误:; } else if (b 17 b 19) { strErr 需要DBA注意的错误:; } else if (b 20 b 25) { strErr 致命错误或系统问题:; } else { strErr 地球要毁灭了快跑啊:; } return strErr; } #endregion } }文后附有C#源码。执行效果小结1、SQL Server处理错误时有一个重要的开关XACT_ABORT没事的时候记得把它打开。2、SQL Server提供的错误信息很丰富请区分等级采取相应的对策当然还可以自己增加更为实用贴切的自定义错误类型。下载源码邀月注本文版权由邀月和CSDN共同所有转载请注明出处。助人等于自助! 3wlive.cn