应用程序需要使用存储过程?

问题的提出

    当你在开发一个基于数据库的应用的时候，你可能会想这样一个问题：数据库有关的事务操作部分因该放在那里？是以存储过程（stored procedure）的形式放在数据库端呢，还是将查询以及相应运算嵌在应用程序当中呢？要回答这样一个问题，你首先要了解存储过程以及内嵌T-SQL这两种方案各自的优缺点以及他们分别适用的场合。尤其是在新的.NET开发环境中，如何选取正确的解决方案是非常关键的。

为什么要使用存储过程（Stored Procedures）?

    你可能对数据库编程已经有一些经验了，对SqlCommand对象也比较熟悉了。但是你是否想过你的这些数据库相关的操作是不是符合优化原则呢？那些相对复杂的数据处理是应当嵌在应用程序中呢，还是把它封装在存储过程中，放置在数据库端？
    再展开讨论之前，让我们先简要的回顾一下存储过程的概念。
    存储过程是一组T-SQL语句，它们存放在一起形成一段T-SQL程序。在运行的时候，你可以传入一些参数；你得到的可以是结果集合（result set），也可以是输出参数（output parameters），甚至是返回值（return value）。存储过程在第一次被执行的时候，数据库系统要首先对它进行分析和编译。编译后得到了一个执行计划（execution plan）。所谓执行计划就是数据库具体执行这个存储过程的先后步骤的过程纪录。这个编译得到的执行计划被放置到数据库的缓存池中以备以后再次使用。如果这个存储过程今后再次被调用，那么数据库将从缓存池中取出这个执行计划来运行。这样就避免了重复对该存储过程进行再次分析和编译，从而提高了数据库的性能。（这些缓存池中的执行计划将一直被保存着，直到数据库重新启动或是系统内存不够用而被清除出缓存池）。
    是否使用存储过程，我们可以从以下三个方面来进行分析。

一．性能（Performance）
    在以前，存储过程比查询（Query）有性能上的优势。原因是数据库会缓存存储过程的执行计划（execution plan）。但是在最近的SQL数据库7.0以后的版本中，查询的执行计划也被数据库放到缓存池中。这样一来存储过程的传统优势就不再存在了。只要你的查询语句是静态的（static）并且你经常使用它，这样他就不会被数据库清理出缓存池去。如果它的执行计划得到重用，那么理论上讲查询和存储过程没有性能上的区别。要注意的一点是，查询的语句必须保持静态，如果你更动了一些，哪怕是很不重要的一个部分，那么这个查询很可能在缓存池里找不到匹配的执行计划。那么查询只好被数据库重新编译，这将导致性能上的损失。
    不过在网络传输方面，存储过程比查询仍然占有优势。因为使用存储过程只需要向数据库传递存储过程的名字和必要的参数，而不是像查询那样要传输全部查询语句。如果查询逻辑复杂的话，那么查询语句的大小也将会比较可观。另外，设计合理的存储过程可以减少客户端和数据库端之间的往返，甚至减少到一次。
    另外，通过使用远端过程调用（remote procedure calls, RPC）来执行数据库端的存储过程可以加强执行计划的重用性，从而提高性能。当你指定SqlCommand.CommandType为StoredProcedure的时候，存储过程是通过RPC来执行的。RPC包装整理参数然后调用数据库的存储过程的方式使得数据库引擎非常容易发现匹配的执行计划。你在调用该存储过程时可以使用了不同的参数，数据库系统将会使用同一个执行计划的。
    在决定是否使用存储过程的时候，你还要判断你的特定操作是不是利用了存储过程的长处。总体来说：

" 基于集合的运算（Set-Based）是T-SQL的强项
" 基于行的运算（Row-Based）以及基于字符串的运算（String manipulation）不是T-SQL的强项。至少在下一个版本Yukon数据库出来之前，你应当避免这样的操作。

    也就是说，有些操作由应用程序的高级语言来完成往往会比数据库来往成更有效。比如比较复杂的字符串处理。这时候，将所有的操作全部放到存储过程中就不是一个最优化的办法。你可能要合理的划分任务，让数据库和应用程序各自完成其擅长的任务。

二．可维护性和抽象能力（Maintainability and Abstraction）
    使用存储过程另外一个潜在的好处就是可维护性好。尽管我们希望数据库结构永远不要变动，事务处理规则也永远保持不变，但事实上这是不大可能发生的。对于好多更动，你也许只需要更改存储过程的具体实现就可以完成。所有使用它的客户端程序就不需要重新修改，调试和编译。这样很多变动对于客户程序来说就是透明的（transparent）。在大多情况下，这种办法往往是最有效和最简单的。
    另外，通过抽象具体实现（implementation）和将T-SQL语句放在存储过程中，可以使任何客户端调用者以一个统一的形式来访问数据。从全局上看，一种数据操作运算只有一种实现，放在一个地方。这样更改和维护都将非常方便。不同的用户也将永远得到同一样的结果。
    使用存储过程的另外一个维护性方面的好处是你可以有更好的程序版本控制。你可以使用版本控制的软件来帮助你维护存储过程，就象你维护其它源程序那样。比如，你可以使用微软的Visual SourceSafe?来帮你做到这一点。这样你可以很方便的找回以前任何一个版本的存储过程。
    需要指出的一点是使用存储过程不能防止你修改数据库结构和事务处理规则。如果更动比较大，需要重新设计传入的参数或者返回值，那么你将需要修改客户端调用这些存储过程的程序段。
    你应该考虑到使用存储过程来封装你的事务处理逻辑将影响应用的可移植性。存储过程是和SQL数据库捆绑在一起的，如果你想更换数据库平台，你可能要重写这些存储过程。如果可移植性对你的应用的是非常关键的，那么将事务处理逻辑放在数据库系统中立（RDBMS-neutral）的中间层（middle-tier）比较好。

三．安全性（Security）

    最后一个使用存储过程的原因是它可以增强数据库系统的安全性。
    从管理用户访问信息角度来讲，它可以通过让用户访问一定的存储过程来保证用户可以访问特定的数据，这是一种间接的数据访问，而不是直接对用户开放式据库表格。其实我们可以将存储过程假想为数据库系统的View。唯一的区别就是存储过程可以变更参数而使得结果动态变化。
    存储过程还可以让你在程序安全性方面有所改进。它可以防备一种叫做SQL注入式的攻击（SQL injection attacks）- 这种攻击主要是用AND或是OR运算符将命令拼接在有效的输入参数之后。存储过程还可以隐藏事务处理规则于数据库端，而不是放在客户程序端。在有些情况下（比如涉及到知识产权等等），这种隐藏是非常重要的。
    此外，使用存储过程可以让你使用ADO.NET提供的SqlParameter类。你可以使用这个类来说明具体的参数类型。这使得你更加容易来验证用户输入的参数是否合法。参数对于存储过程和in-line查询来说是同样重要的，它可以将用户的输入降低到一个很小的范围内。
    当然使用存储过程并不意味着安全方面你可以高枕无忧了。事实上，不好的程序以及数据库管理方面的漏洞仍然可以将你置于可能的攻击之中。如果对SQL数据库的角色（Role）创建以及授权不当，那么将导致用户可以访问一些他们本不应该访问的数据。同样，仅仅使用存储过程并不能完全保证不受SQL注入式攻击。
    另外使用SqlParameter类来校验用户输入也不是绝对安全的，不管是在后台T-SQL写的存储过程中还是嵌在应用中的查询，所有用户的输入，尤其是字符串类型的数据，一定要在交给数据库引擎处理之前进行有效性的校验。

存储过程适用于你吗？

    综上所述，使用存储过程有如下几个突出优点：

" 提高新能，减少了网络流量
" 在数据库端一点的维护（single point of maintenance ）
" 抽象和概化业务逻辑，增强了一致性和安全性
" 减少了一些可能的恶意攻击的机会
" 鼓励执行计划的重用性（Encourage execution plan re-use ）

    如果你的应用程序能有效的利用存储过程的上述优点，那么你就应该尽量使用。但是如果你的应用要求有很高的可移植性，或者数据库的结构变动很大，不能相对稳定下来，那么你可能要试一试其他方法了。比如你现在在SQL数据库上为用户开发一个早期可行性验证程序，今后用户很可能使用MySQL或是Oracle等其它数据库，那么你就因该避免使用SQL数据库的存储过程，而使用程序内嵌的数据库操作语句。这样当你更换数据库平台的时候，可以极大的保证程序不受影响。
    另外，你还要考虑使用存储过程的技术问题。也许你和你的手下非常不熟悉存储过程编程，并且没有时间去很快掌握它。这些因素你也需要通盘考虑。另外如前所述，数据库存储过程擅长于基于集合（set-based）的操作，而不擅长基于行（row-based）的操作。如果你对存储过程没有很好的了解，而不正确的使用了它往往会导致很不好的执行性能。所以如果你决定使用存储过程，那么多花一些时间来学习它是很有必要的。

Visual Studio .NET 提供的工具

    微软的Visual Studio? .NET提供了一些工具来帮助你察看和操作SQL数据库端的存储过程（以及其它一些数据库对象）。现在就让我们大概来看一下这些常用的工具。

察看存储过程（Viewing Stored Procedures）
    你可以使用服务器浏览器（Server Explorer）来查看已有的存储过程，你可以看它们要求的参数，具体的实现细节等等。如果你已经连接到数据库上，你可以一级一级打开，直到看到你需要的存储过程（如下图1所示）。并且有趣的是，这些存储过程的数据类型已经被转换为ADO类型。你可以查看.NET的文档去搞清楚SQL数据库数据类型和.NET间数据类型的映射关系。当然了，如果你在ADO.NET使用参数，你应该使用SqlDbType提供的枚举了型，例如：

SqlDbType.DateTimeSqlDbType.NVarCharSqldbtype.MoneySqlDbType.Image

图 1

    如果你双击一个存储过程，Visual Studio就会打开它让你编辑。编辑窗口使用了颜色来显示T-SQL的关键字。另外，由于你是在编辑而不是创建一个存储过程，所以你看到的不是CREATE PROCEDURE语法，而是ALTER PROCEDURE。

创建和修改存储过程（Creating and Modifying Stored Procedures）
    你首先要弄清楚你是否有权限来创建和修改存储过程。如果没有的话，你需要向数据库管理员申请。
    在服务器浏览器的窗口之中，在存储过程节点上单击鼠标右键，然后选择New Stored Procedure就可以创建新的存储过程（如下图2所示）。同样你可以修改已有的存储过程。

图 2

    如果你需要一些帮助来创建你的存储过程，在存储过程窗口单击鼠标右键，然后选择Insert SQL；或者你选择一段T-SQL语句，然后右击，选择Design SQL Block。这样你就可以打开查询构造窗口（Query Builder window）。这个图形化的查询构造工具可以极大的方便你构造复杂的查询。在这里完成工作后，你可以把得到的查询语句拷贝到你的存储过程中去。

图 3

    在现在的Visual Studio 2003版中，存储过程的编写窗口还没有"智能（IntelliSense）"。所以你可能还要打开SQL数据库的在线帮助，来查询一些语法，函数等等。要注意的是，只有语法完全正确的存储过程才可以保存到数据库中。
    当你完成了存储过程的创建以后，你可以马上测试它。方法很简单，右击这个存储过程，然后选择Run Stored Procedure。

使用存储过程的一些经验

    这里有一些关于使用存储过程的最基本的常识。正确的使用这些常识可以帮助你避免一些常犯的错误，写出效率更高的程序来。

使用 SET NOCOUNT ON
    在缺省情况下，存储过程返回被该存储过程影响的表的行数（Rows），然而在大多情况下，这一信息是不重要的，大多数程序也不使用这一信息。使用SET NOCOUNT ON语句将停止这一行为。这将减少一次或多次客户端和数据库间的往返（具体数量取决于这一存储过程包含有多少语句影响行数（affect rows））。通常来说，这是一个小的开销，问题不大。但是对于流量很大的应用却可能会造成比较大的影响，所以建议使用SET NOCOUNT ON。具体语法如下所示：

　

create procedure test_MyStoredProc @param1 intasset nocount on

不要使用 sp_ 前缀（sp_prefix）
    sp_前缀是SQL数据库的保留字，是专用于系统存储过程的。数据库的引擎会首先在主控数据库（Master Database）中寻找有这个前缀的存储过程。也就是说在搜寻完主控数据库后，如果没有发现该名称的存储过程才会搜寻用户具体适用的数据库，这显然增加了搜索的时间。如果主控数据库中有和你定义的存储过程同名的存储过程，那么主控数据库的那个将会被执行，而你的则会被忽略。

保守的使用条件参数（Optional Parameters）
    在使用条件参数的时候一定要考虑清楚。如果使用不当则很可能会严重的影响性能。如果执行不是根据传入的参数的组合的话，你就不必要使用它。你可以在存储过程中使用条件语句（conditional coding）来达到类似的效果。当然要列举所有可能的参数组合并一一给出对应的执行语句段是比较繁复和比较容易出错的。使用时一定要小心。

尽量使用输出（OUTPUT）参数
    如果你的存储过程只返回标量值（scalar data），那么使用输出参数会提高执行的效能。原因是使用使用输出参数避免了生成一个Result set对象，从而减少了开销，提高了速度。（另外你还可以使用输出参数来返回一个cursor。在什么情况下使用这个方案更有效是一个比较复杂的问题，留待以后有机会再具体讨论）

提供返回值（RETURN Value）使用返回值来回传存储过程的执行结果是一个比较好的做法。如果在你所在的开发小组中大家能事先商定一套返回值来代表不同的执行结果，那么将会极大的便利各种错误处理，并可能给前台用户显示一些更有意义的错误信息。

先使用DDL, 然后再使用DML
    如果数据库定义语句（data definition language，DDL）在数据库操作语句（data manipulation language, DML）之前，那么数据库将重新编译存储过程。这样保证DML使用的是DDL刚刚修改过的对象。所以说如果你将DDL语句统统放在DML语句之前，那么数据库只要重新编译一次存储过程。如果你将DDL和DML语句混杂在一起，那么数据库会多次编译存储过程，这样就降低了系统的效率。

使用注释（Comments）你应该注释你的T-SQL程序。这样将来不管是谁负责这些程序，他们都会更好的理解这些程序的意义和目的。写注释是一个程序员因该具备的最起码的素质。

结论

    通过这些讲解，希望你对存储过程有了一个更清晰地认识，更加明确了它是不是你当前应用开发的合适的选择。SQL数据库有不少有用的工具你可以好好利用，尤其是最新发布的 SQL Reporting Service。SQL数据库的在线帮助也是一个很好的帮手，你可以在那里找到你大部分问题的答案。

图 1

    如果你双击一个存储过程，Visual Studio就会打开它让你编辑。编辑窗口使用了颜色来显示T-SQL的关键字。另外，由于你是在编辑而不是创建一个存储过程，所以你看到的不是CREATE PROCEDURE语法，而是ALTER PROCEDURE。

创建和修改存储过程（Creating and Modifying Stored Procedures）
    你首先要弄清楚你是否有权限来创建和修改存储过程。如果没有的话，你需要向数据库管理员申请。
    在服务器浏览器的窗口之中，在存储过程节点上单击鼠标右键，然后选择New Stored Procedure就可以创建新的存储过程（如下图2所示）。同样你可以修改已有的存储过程。

图 2

    如果你需要一些帮助来创建你的存储过程，在存储过程窗口单击鼠标右键，然后选择Insert SQL；或者你选择一段T-SQL语句，然后右击，选择Design SQL Block。这样你就可以打开查询构造窗口（Query Builder window）。这个图形化的查询构造工具可以极大的方便你构造复杂的查询。在这里完成工作后，你可以把得到的查询语句拷贝到你的存储过程中去。

图 3

    在现在的Visual Studio 2003版中，存储过程的编写窗口还没有"智能（IntelliSense）"。所以你可能还要打开SQL数据库的在线帮助，来查询一些语法，函数等等。要注意的是，只有语法完全正确的存储过程才可以保存到数据库中。
    当你完成了存储过程的创建以后，你可以马上测试它。方法很简单，右击这个存储过程，然后选择Run Stored Procedure。

使用存储过程的一些经验

    这里有一些关于使用存储过程的最基本的常识。正确的使用这些常识可以帮助你避免一些常犯的错误，写出效率更高的程序来。

使用 SET NOCOUNT ON
    在缺省情况下，存储过程返回被该存储过程影响的表的行数（Rows），然而在大多情况下，这一信息是不重要的，大多数程序也不使用这一信息。使用SET NOCOUNT ON语句将停止这一行为。这将减少一次或多次客户端和数据库间的往返（具体数量取决于这一存储过程包含有多少语句影响行数（affect rows））。通常来说，这是一个小的开销，问题不大。但是对于流量很大的应用却可能会造成比较大的影响，所以建议使用SET NOCOUNT ON。具体语法如下所示：

　

create procedure test_MyStoredProc @param1 intasset nocount on

不要使用 sp_ 前缀（sp_prefix）
    sp_前缀是SQL数据库的保留字，是专用于系统存储过程的。数据库的引擎会首先在主控数据库（Master Database）中寻找有这个前缀的存储过程。也就是说在搜寻完主控数据库后，如果没有发现该名称的存储过程才会搜寻用户具体适用的数据库，这显然增加了搜索的时间。如果主控数据库中有和你定义的存储过程同名的存储过程，那么主控数据库的那个将会被执行，而你的则会被忽略。

保守的使用条件参数（Optional Parameters）
    在使用条件参数的时候一定要考虑清楚。如果使用不当则很可能会严重的影响性能。如果执行不是根据传入的参数的组合的话，你就不必要使用它。你可以在存储过程中使用条件语句（conditional coding）来达到类似的效果。当然要列举所有可能的参数组合并一一给出对应的执行语句段是比较繁复和比较容易出错的。使用时一定要小心。

尽量使用输出（OUTPUT）参数
    如果你的存储过程只返回标量值（scalar data），那么使用输出参数会提高执行的效能。原因是使用使用输出参数避免了生成一个Result set对象，从而减少了开销，提高了速度。（另外你还可以使用输出参数来返回一个cursor。在什么情况下使用这个方案更有效是一个比较复杂的问题，留待以后有机会再具体讨论）

提供返回值（RETURN Value）使用返回值来回传存储过程的执行结果是一个比较好的做法。如果在你所在的开发小组中大家能事先商定一套返回值来代表不同的执行结果，那么将会极大的便利各种错误处理，并可能给前台用户显示一些更有意义的错误信息。

先使用DDL, 然后再使用DML
    如果数据库定义语句（data definition language，DDL）在数据库操作语句（data manipulation language, DML）之前，那么数据库将重新编译存储过程。这样保证DML使用的是DDL刚刚修改过的对象。所以说如果你将DDL语句统统放在DML语句之前，那么数据库只要重新编译一次存储过程。如果你将DDL和DML语句混杂在一起，那么数据库会多次编译存储过程，这样就降低了系统的效率。

使用注释（Comments）你应该注释你的T-SQL程序。这样将来不管是谁负责这些程序，他们都会更好的理解这些程序的意义和目的。写注释是一个程序员因该具备的最起码的素质。

结论

    通过这些讲解，希望你对存储过程有了一个更清晰地认识，更加明确了它是不是你当前应用开发的合适的选择。SQL数据库有不少有用的工具你可以好好利用，尤其是最新发布的 SQL Reporting Service。SQL数据库的在线帮助也是一个很好的帮手，你可以在那里找到你大部分问题的答案。