Christa Carpentiere
Microsoft Corporation
适用范围：
Microsoft® SQL Server™
Microsoft Visual Studio® .NET 2003
Transact-SQL (T-SQL) 语言
摘要：向主要使用 .NET 编程语言的开发人员介绍 Microsoft SQL Server 存储过程。揭示使用存储过程的优缺点；概述 Microsoft Visual Studio .NET 2003 为帮助您使用存储过程而提供的工具；了解一些帮助您入门的最佳做法。
本页内容
 |
引言 |
|
考虑使用存储过程的理由 |
|
Visual Studio .NET 工具 |
|
入门提示 |
|
结论 |
引言
本文旨在介绍使用存储过程封装应用程序所需的 Transact-SQL (T-SQL) 的主要优缺点，以便您对如何在您的环境中使用存储过程做出明智的决策。对于那些可以利用存储过程的人员，本文还介绍了与在 .NET 应用程序中使用存储过程相关的工具和最佳做法。
在这方面，大家的看法并不一致，有些人认为业务逻辑只应被编码到中间层或数据库中；有些人认为内联查询是唯一的选择；有些人则认为存储过程应用于任何情况。所有这些方法都具有优点和缺点。重要的是应考虑，对您的应用程序和环境至关重要的是什么。因此，让我们了解一下存储过程的概念，并考虑使用它们封装 T-SQL 代码的某些原因。
考虑使用存储过程的理由
也许您曾经在多处编写过使用 SqlCommand 对象的 T-SQL，但却从未考虑过是否有一个比将它并入数据访问代码更好的位置。由于应用程序随着时间的推移增添了一些功能，因此其内部可能包含一些复杂的 T-SQL 过程代码。存储过程为封装此代码提供了一个替换位置。
大多数人可能对存储过程已有所了解，但对于那些不了解存储过程的人员而言，存储过程是指一组作为单个代码单元一起存储于数据库中的 T-SQL 语句。您可以使用输入参数传入运行时信息，并取回作为结果集或输出参数的数据。存储过程在首次运行时将被编译。这将产生一个执行计划 - 实际上是 Microsoft® SQL Server™ 为在存储过程中获取由 T-SQL 指定的结果而必须采取的步骤的记录。然后，执行计划在内存中得到缓存，以备以后使用。这样会改善存储过程的性能，因为 SQL Server 无需为确定如何处理代码而重新分析它，而只需引用缓存的计划即可。这个缓存的计划一直可用，直到 SQL Server 重新启动，或直到它由于使用率较低而溢出内存。
性能
缓存的执行计划曾使存储过程较之查询更有性能优势。但对于 SQL Server 的几个最新版本，执行计划已针对所有 T-SQL 批处理进行了缓存，而不管它们是否在存储过程中。因此，基于此功能的性能已不再是存储过程的卖点。任何使用静态语法，且提交频率足以阻止执行计划溢出内存的 T-SQL 批处理将会获得同样的性能好处。“静态”部分是关键；任何更改，即使像添加注释这样无关紧要的更改，也将导致无法与缓存的计划相匹配，从而将无法重复使用计划。
但是，当存储过程可以用于降低网络流量时，它们仍然能够提供性能好处。您只需在网络中发送 EXECUTE stored_proc_name 语句，而非整个 T-SQL 例程，这可以在复杂操作中广泛使用。设计良好的存储过程可以将客户端与服务器之间的许多往返过程简化为单个调用。
此外，使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。使用 StoredProcedure 的 SqlCommand.CommandType 时，存储过程通过 RPC 执行。RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。
考虑使用存储过程提高性能时，最后要考虑是否要充分利用 T-SQL 的优点。请考虑要如何处理数据。
| • |
是否要使用基于集合的操作，或执行 T-SQL 中完全支持的其他操作？那么存储过程就是一个选择，而内联查询也可以使用。 |
| • |
是否尝试执行基于行的操作，或复杂的字符串处理？那么可能要重新考虑在 T-SQL 中进行这种处理，这不包括使用存储过程，至少要到 Yukon 发布并且公共语言运行库 (CLR) 集成可用后，才能使用存储过程。 |
可维护性和抽象
要考虑的另一个潜在优势是可维护性。理想情况下，数据库架构从不更改，业务规则不被修改，但在现实环境中，情况则完全不同。既然情况如此，那么如果可以修改存储过程以包括新 X、Y 和 Z 表（为支持新的销售活动而添加了这些表）中的数据，而不是在应用程序代码中的某个位置更改此信息，则维护对您来说可能比较容易。在存储过程中更改此信息使得更新对应用程序而言具有透明性 - 您仍然返回相同的销售信息，即使存储过程的内部实现已经更改。更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。
另外，通过抽象化实现并将此代码保存在存储过程中，任何需要访问数据的应用程序均可以获取一致的数据。您无需在多个位置维护相同的代码，用户便可获取一致的信息。
在存储过程中存储 T-SQL 的另一个可维护性优点是更好的版本控制。您可以对创建和修改存储过程的脚本进行版本控制，就像可以对任何其他源代码模块进行版本控制一样。通过使用 Microsoft Visual SourceSafe® 或某个其他源代码控制工具，您可以轻松地恢复到或引用旧版本的存储过程。
在使用存储过程提高可维护性时应值得注意的一点是，它们无法阻止您对架构和规则进行所有可能的更改。如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等。
要注意的另一个问题是，由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。
安全性
考虑使用存储过程的最终原因是它们可用于增强安全性。
就管理用户对信息的访问而言，通过向用户授予对存储过程（而不是基础表）的访问权限，它们可以提供对特定数据的访问。您可以将存储过程看成是 SQL Server 视图（如果您对它们熟悉的话），除非存储过程接受用户的输入以动态更改显示的数据。
存储过程还可以帮助您解决代码安全问题。它们可以防止某些类型的 SQL 插入攻击 - 主要是一些使用运算符（如 AND 或 OR）将命令附加到有效输入参数值的攻击。在应用程序受到攻击时，存储过程还可以隐藏业务规则的实现。这对于将此类信息视为知识产权的公司非常重要。
另外，使用存储过程使您可以使用 ADO.NET 中提供的 SqlParameter 类指定存储过程参数的数据类型。这为验证用户提供的值类型（作为深层次防御性策略的一部分）提供了一个简单方法。在缩小可接受用户输入的范围方面，参数在内联查询中与在存储过程中一样有用。
使用存储过程增强安全性时值得注意的是，糟糕的安全性或编码做法仍然会使您受到攻击。对 SQL Server 角色创建和分配如果不加注意将导致人们访问到不应看到的数据。同时，如果认为使用存储过程便可防止所有 SQL 插入代码攻击（例如，将数据操作语言 (DML) 附加到输入参数），后果将是一样的。
另外，无论 T-SQL 位于代码还是位于存储过程中，使用参数进行数据类型验证都不是万无一失的。所有用户提供的数据（尤其是文本数据）在传递到数据库之前都应受到附加的验证。
存储过程对我是否适用？
或许适合吧。让我们概括一下它们的优点：
| • |
通过降低网络流量提高性能 |
| • |
提供单点维护 |
| • |
抽象化业务规则，以确保一致性和安全性 |
| • |
通过将某些形式的攻击降至最低，以增强安全性 |
| • |
支持执行计划重复使用 |
如果您的环境允许利用存储过程提供的好处（如上所述），强烈建议使用它们。对于改进数据在环境中的处理方式而言，它们提供了一个很好的工具。另一方面，如果您的环境中存在可移植性、大量使用非 T-SQL 友好的进程或者不稳定的数据库架构等削弱这些优点的因素，则您可能要考虑其他方法。
另一个要注意的事项是机构内部所拥有的 T-SQL 专业人员的数量。您有足够的 T-SQL 知识吗？您愿意学习吗？或者，您有 DBA 或合适的人员帮您编写存储过程吗？掌握的 T-SQL 知识越多，存储过程就会越好，维护它们就会越容易。例如，T-SQL 主要用于基于集合的操作，而不是基于行的操作。依赖于光标（因为它们向您提示数据集）将导致性能降低。如果您不太了解 T-SQL，请将本文作为一次学习机会。无论您将它用在何处，本文介绍的知识都将改善您的代码。
因此，如果您认为存储过程会为应用程序增添特殊的效果，请继续阅读本文。我们将回顾一些简化存储过程使用的工具，并了解一些创建存储过程的最佳做法。
Visual Studio .NET 工具
Microsoft Visual Studio® .NET 提供了一些工具，使您能够查看和操作 SQL Server 存储过程（以及其他数据库对象）。让我们简要介绍一下您可能期望获得的功能。
查看存储过程
您可以使用服务器资源管理器查看现有的存储过程、查看它们所需的参数或查看它们的内部实现。如果您已经连接到安装了 SQL Server 的服务器，则可以依次展开“ServerName”、“SQL Server”、“SQLInstanceName”、“Northwind”、“存储过程”，并展开“CustOrdersDetail”。该资源管理器将显示存储过程所需的任何参数及其返回的任何列。如果签出以上任何项的属性，便会发现数据类型表示为 ADO 类型。框架文档提供了这些类型与 .NET 类型之间的简单映射。当然，在 ADO.NET 代码中使用参数时，可能要将它们的数据类型表示为 SqlDbType 枚举的成员。您会看到 .NET 类型与 SqlDbType 类型之间的映射。
如果双击存储过程，Visual Studio 将会在 SQL 编辑器中打开它，其中显示了便于查看的颜色编码和所有内容。注意，它并不显示 CREATE PROCEDURE 语法（该语法实际上在数据库中已存在），由于它假定您要修改存储过程，因此提供了 ALTER PROCEDURE 语法。创建和修改存储过程
您具有在相应数据库中创建存储过程的权限吗？如果没有，则在创建和修改存储过程之前应获取该权限。如果需要帮助，请与数据库管理员联系。
您可以创建一个新的存储过程，方法是在服务器资源管理器中右键单击“存储过程”节点（或任何其他存储过程）并选择“新建存储过程”。随即将在 SQL 编辑器中打开一个新窗口，其中提供了一些提纲式的 CREATE PROCEDURE 语法以帮助您开始编写代码。然后，您可以在存储过程的主体中键入代码。修改存储过程与查看存储过程的起始操作相同：在服务器资源管理器中找到存储过程，然后将其打开。
如果在创建用于存储过程的查询时需要帮助，请在存储过程窗口上右键单击，然后选择“插入 SQL”。或者，也可以选择一个 T-SQL 块，并右键单击它，然后选择“设计 SQL 块”。以上任一操作均会打开“查询生成器”窗口，该窗口提供用于构建或修改 T-SQL 语句的图形界面。完成后，可以将 T-SQL 剪切并粘贴到存储过程中。
遗憾的是，该编辑器中没有 IntelliSense，因此还要打开“SQL Server 联机丛书”以供随时参考。尝试保存存储过程时，系统将向您提示任何需要更正的语法错误。注意，只有修复了语法错误，才可以保存存储过程，因此在开始编码之前，应确保有时间完成该过程。您可以随时在 SQL Server 查询分析器中进行最初的编码和测试，但这不在本文介绍的范畴之内。
完成所需的代码后，可以通过单击右键并选择“运行存储过程”选项来测试存储过程。
入门提示
如果要开始创建与应用程序一起使用的存储过程，应记住下面这些提示，以便两者正常运行并良好地配合工作。
使用 SET NOCOUNT ON
默认情况下，存储过程将返回过程中每个语句影响的行数。如果不需要在应用程序中使用该信息（大多数应用程序并不需要），请在存储过程中使用 SET NOCOUNT ON 语句以终止该行为。根据存储过程中包含的影响行的语句的数量，这将删除客户端和服务器之间的一个或多个往返过程。尽管这不是大问题，但它可以为高流量应用程序的性能产生负面影响。
create procedure test_MyStoredProc @param1 int as set nocount on
不要使用 sp_ prefix
sp_ prefix 是为系统存储过程保留的。数据库引擎将始终首先在主数据库中查找具有此前缀的存储过程。这意味着当引擎首先检查主数据库，然后检查存储过程实际所在的数据库时，将需要较长的时间才能完成检查过程。而且，如果碰巧存在一个名称相同的系统存储过程，则您的过程根本不会得到处理。
尽量少用可选参数
在频繁使用可选参数之前，请仔细考虑。通过执行额外的工作会很轻易地影响性能，而根据为任意指定执行输入的参数集合，这些工作时不需要的。您可以通过对每种可能的参数组合使用条件编码来解决此问题，但这相当费时并会增大出错的几率。
在可能的情况下使用 OUTPUT 参数
通过使用 OUTPUT 参数返回标量数据，可以略微提高速度并节省少量的处理功率。在应用程序需要返回单个值的情况下，请尝试此方法，而不要将结果集具体化。在适当的情况下，也可以使用 OUTPUT 参数返回光标，但是我们将在后续文章中介绍光标处理与基于集合的处理在理论上的分歧。
提供返回值
使用存储过程的返回值，将处理状态信息返回给进行调用的应用程序。在您的开发组中，将一组返回值及其含义标准化，并一致地使用这些值。这会使得处理调用应用程序中的错误更加容易，并向最终用户提供有关问题的有用信息。
首先使用 DDL，然后使用 DML
将 DML 语句放在数据定义语言 (DDL) 语句之后执行（此时 DML 将引用 DDL 修改的任意对象）时，SQL Server 将重新编译存储过程。出现这种情况，是由于为了给 DML 创建计划，SQL Server 需要考虑由 DDL 对该对象所作的更改。如果留意存储过程开头的所有 DDL，则它只需重新编译一次。如果将 DDL 和 DML 语句混合使用，则将强制存储过程多次进行重新编译，这将对性能造成负面影响。
始终使用注释
您可能不会始终维护此代码。但其他人员将来可能想要了解它的用途。'Nuff 曾经这样说。
结论
希望您已经认识到存储过程对您是否有用。它们是 SQL Server 免费提供的工具，因此如果这些工具确实可以帮助您开发和维护应用程序，请务必充分利用它们。本文介绍的信息将帮助您尽快入门。入门后要了解更多深层次的信息，请查阅 SQL Server 联机丛书、Visual Studio 文档以及 MSDN®。
原文：http://www.xmlchina.net/ShowPost.aspx?ForumID=29&PostID=147
不过好像也是转自msdn的