SQL Server存储过程调试指南

SQLServer存储过程调试指南TT数据库技术专题之“SQLServer存储过程调试指南”Page2of31SQLServer存储过程调试指南存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。有时人们将存储过程称为“数据库中埋头苦干的老黄牛”，它是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。但是无论编写还是调试存储过程都是一项复杂的工作，因此在本次技术手册中，我们将对SQLServer存储过程的调试进行详细的介绍，包括了基础的调试方法和在调试过程中出现的T-SQL性能问题和解决方法。SQLServer存储过程调试基础本部分介绍了在SQLServer中使用异常处理调试存储过程的方法，并对怎样调试T-SQL存储过程进行了详细介绍，通过对基础的学习，相信您会对存储过程的基础有个更加深刻的了解。SQLServer中使用异常处理调试存储过程（一）SQLServer中使用异常处理调试存储过程（二）怎样调试T-SQL存储过程（一）怎样调试T-SQL存储过程（二）怎样调试T-SQL存储过程（三）T-SQL性能问题和解决方法当应用程序用户开始遇到性能问题时，一般他们会联系数据库管理员并询问是否数据库存在问题。导致严重性能问题的往往是编写不当的Transact-SQL（T-SQL）代码。因此，你必须找出确定性能糟糕的查询并对它们进行优化。TT数据库技术专题之“SQLServer存储过程调试指南”Page3of31SQLServer中使用游标进行行处理存储过程与嵌套查询优化SQLServer中的视图与UDF性能问题SQLServer不必要的记录锁解决SQLServer触发器滥用问题如何诊断和修复T-SQL问题SQLServer存储过程的修改与变更在对SQLServer存储过程进行修改和变更时，往往会遇到各种各样的错误和问题，这可能是由于与现有数据库函数冲突造成的。本部分介绍了如何对存储过程进行升级与批量修改，而不造成错误的方法。批量编辑SQLServer存储过程在SQLServer2005中升级存储过程TT数据库技术专题之“SQLServer存储过程调试指南”Page4of31SQLServer中使用异常处理调试存储过程（上）异常处理被普遍认为是T-SQL脚本编程中的最弱的方面。幸运的是，这一点在SQLServer2005中得到了改变，因为SQLServer2005支持结构化异常处理。本文首先关注新特性“TRY„„CATCH”的基本构成，然后在SQLServer2000和SQLServer2005中对照着看一些T-SQL的例子，这些例子中使用事务代码故意制造了一些违反约束限制的情况。将来的文章会继续探讨这一主题。在SQLServer之前的版本中，你需要在执行INSERT，UPDATE，DELETE之后立即检查全局变量“@@error”来处理异常，如果“@@error”变量不为零的话（表示有错误），就接着执行一些纠正动作。开发人员常常重复这种与业务逻辑无关的代码，这会导致重复代码块，而且需要与GOTO语句和RETURN语句结合使用。结构化异常处理为控制具有许多动态运行时特性的复杂程序提供了一种强有力的处理机制。目前，这种机制经实践证明是良好的，许多流行的编程语言（比如：微软的VisualBasic.Net和VisualC#）都支持这种异常处理机制。接下来你会在例子中看到，采用了这种健壮的方法以后，会使你的代码可读性和可维护性更好。TRY块包含了可能潜在失败的事务性代码，而CATCH块包含了TRY块中出现错误时执行的代码。如果TRY块中出现了任何错误，执行流程被调转到CATCH块，错误可以被处理，而出错函数可以被用来提供详细的错误信息。TRY„„CATCH基本语法如下：BEGINTRYRAISERROR（'Houston,wehaveaproblem',16,1）ENDTRYBEGINCATCHSELECTERROR_NUMBER（）asERROR_NUMBER,ERROR_SEVERITY（）asERROR_SEVERITY,ERROR_STATE（）asERROR_STATE,ERROR_MESSAGE（）asERROR_MESSAGEENDCATCHTT数据库技术专题之“SQLServer存储过程调试指南”Page5of31注意上面脚本中函数的用法，我们可以用它们代替局部变量和（或者）全局变量。这些函数只应该被用在CATCH块中，函数功能说明如下：ERROR_NUMBER（）返回错误数量。ERROR_SEVERITY（）返回错误严重等级。ERROR_STATE（）返回错误状态号。ERROR_PROCEDURE（）返回出错位置存储过程或者触发器的名称。ERROR_LINE（）返回程序中引起错误的行号。ERROR_MESSAGE（）返回错误信息的完整文本。错误内容包括可替换参数的值，比如：长度，对象名称或者时间。我会先用SQLServer2000演示一个简单例子，然后演示一个SQLServer2005异常处理的例子。下面是一个简单的存储过程示例，先用SQLServer2000编写，然后改用SQLServer2005实现。两者都从简单的表开始，我们在对这些表执行插入操作时会违反约束限制。下面是表结构：createtabledbo.Titles（TitleIDintPrimaryKeyidentity,TitleNamenvarchar（128）NOTNULL,PricemoneyNULLconstraintCHK_Pricecheck（Price0））createtabledbo.Authors（Authors_IDintprimarykeyidentity,au_fnamenvarchar（32）NULL,au_lnamenvarchar（64）NULL,TitleIDintconstraintFK_TitleIDforeignkeyreferencesTitles（TitleID）,CommissionRatingintconstraintCHK_ValidateCommissionRatingCheck（CommissionRatingbetween0and100））createtabledbo.Application_Error_Log（tablenamesysname,TT数据库技术专题之“SQLServer存储过程调试指南”Page6of31userNamesysname,errorNumberint,errorSeverityint,errorStateint,errorMessagevarchar（4000））(作者：SerdarYegulalp译者：冯昀晖来源：TT中国)TT数据库技术专题之“SQLServer存储过程调试指南”Page7of31SQLServer中使用异常处理调试存储过程（下）点击这里获取存储过程P_Insert_New_BookTitle_2K的源代码。你可以看到，这个存储过程包含了非结构化的错误处理代码，这是我们在SQLServer2005之前使用的方式。我们已经先看到了存储过程P_Insert_New_BookTitle_2K中使用的代码。你顶多能说：“至少我们有异常处理。”下面的语句执行这个SQLServer2000下的存储过程。execP_Insert_New_BookTitle_2K'RedStormRising',16.99,'Tom','Clancy',200在用指定的参数执行存储过程时，对Authors表的插入失败了，因为佣金费率值无效。我们的约束检查发现了该无效值，我们可以看到如下错误信息：Msg547,Level16,State0,ProcedureP_Insert_New_BookTitle,Line23TheINSERTstatementconflictedwiththeCHECKconstraintCHK_ValidateCommissionRating.TheconflictoccurredindatabaseAdventureworks2005,tabledbo.Authors,column'CommissionRating'.Thestatementhasbeenterminated.这里的问题是我们不能阻止这些消息被送到客户端。所以判断哪里出错的重担就放到了客户端的头上。令人遗憾的是，在有些情况下，这样的结果对于一些不使用约束限制的应用程序可能足够了。我们再来试一次，这次我们使用TRY„„CATCH代码块。点击这里获取存储过程P_Insert_New_BookTitle_2K5的源代码。在这段新改进的存储过程中，我们看到使用了TRY„„CATCH代码块的结构化错误处理：要注意SQLServer2005异常处理代码是经过简化的，因此具有更好的可读性和可维护性。不需要剪切和粘贴异常处理代码，也不需要使用GOTO语句。执行该存储过程时，你可以看到如下结果：TT数据库技术专题之“SQLServer存储过程调试指南”Page8of31execP_Insert_New_BookTitle_2K5'RedStormRising',16.99,'Tom','Clancy',200我们用指定的参数执行存储过程，同样因为佣金费率值无效，对Authors表的插入失败了。错误发生时，程序执行流程跳转到了CATCH代码块，在CATCH代码块中我们回滚了事务，然后用SQLServer2005自带的函数给Application_Error_Log表插入一行日志。新的TRY„„CATCH代码块无疑使编写处理错误代码更容易，它还可以在任何时候阻止错误信息发送到客户端。当然这可能需要T-SQL程序员的编程思维有一个转变，这是一个绝对有必要使用的特性。要记住迁移SQLServer2000代码到SQLServer2005时，如果程序的错误处理机制已经设计为旧的发送错误到客户端的方式，那你可能不得不修改应用程序了。从长远来看，我相信为这种潜在的问题付出努力重新设计是值得的。(作者：SerdarYegulalp译者：冯昀晖来源：TT中国)TT数据库技术专题之“SQLServer存储过程调试指南”Page9of31怎样调试T-SQL存储过程（一）我会执行（或者单步执行）一个T-SQL存储过程示例程序，并在调试过程中执行以下操作：给输入参赋值，监视变量内容，在运行过程中跟踪存储过程的逻辑流程，估算T-SQL表达式的值，查看储存过程输出内容，设置断点，大体检查环境状态。（后面的技巧文章会继续探讨这些主题）。我们演示示例存储过程时，不是在ManagementStudio中调试，而是在VisualStudio2005开发环境中调试。我提到这一点是因为在SQLServer2000下，我们可以使用QueryAnalyzer调试存储过程。可能将来ManagementStudio也会增加对调试功能的支持。T-SQL示例存储过程：P_DisplayProductDetails我们使用的存储过程示例使用函数来根据单价给每一个产品子类排序，从AdventureWorks数据库中查询产品明细信息。该存储过程接收产品类别名作为一个可选入参。几个输出参数会给调用它的分支返回有用的信息。最后，正如前面的文章讲到的，该存储过程使用了“结构化异常处理”。UseAdventureWorksGOIFEXISTS（SELECT*FROMsysobjectsWHEREtype='P'ANDname='P_DisplayProductDetails'）DROPProcedureP_DisplayProductDetailsGOCREATEProcedureP_DisplayProductDetails（@Categorynamevarchar（50）=NULL,@Ma