SQL Server中存储过程技术的研究

杨铭

1 存储过程的概念

當开发一个应用程序时，为了易于修改和扩充，经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句，即是“过程”（Procedure）。存储过程包含一些Transact-SQL语句并以特定的名称存储在数据库中（存储过程也是一种数据库对象）。存储过程中可以声明变量、有条件地执行以及其他各项强大的程序设计功能。

2 存储过程的分类

2.1 系统存储过程

系统存储过程（System Stored Procedures）主要存储在master数据库中，并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息。通过系统存储过程，许多管理性或信息性的活动（如了解数库对象、数据库信息）都可以被有效地完成。尽管这些系统存储过程被存储在master数据库中，但是仍可以在其他数据库中对其进行调用。当创建一个数据库时，一些系统存储过程会在新的数据库中被自动创建。

系统存储过程所能完成的操作多达千百项。例如，提供帮助的系统存储过程有sp_helpsql显示关于SQL语句、存储过程和其他主题的信息;sp_help提供关于存储过程或其他数据库对象的报告;sp_helptext显示存储过程和其他对象的文本;sp_depends列举引用或依赖指定对象的所有存储过程。事实上，在前面的学习中就已使用道不少的系统存储过程，例如，sp_tables取得数据库中关于表和视图的相关信息;sp_renamedb更改数据库的名称等。

当系统存储过程的参数是保留字或对象名，且对象名由数据库或拥有者名字限定时，整个名字必须包含在单引号中。一个用户可以在所有数据库中执行一个系统存储过程的许可权，否则在任何数据库中都不能执行系统存储过程。

2.2 本地存储过程

本地存储过程（Local Stored Procedures）也就是用户自行创建并存储在用户数据库中的存储过程。事实上一般所说的存储过程指的就是本地存储过程。

用户创建的存储过程是由用户创建并能完成某一特定功能（如查询用户所需的数据信息）的存储过程。

2.3 临时存储过程

临时存储过程（Temporary Stored Procedures）可分为以下两种：

2.3.1 本地临时存储过程

不论哪一个数据库是当前数据库，如果在创建存储过程时，以井字号（#）作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程（例如，CREATE  PROCEDURE #book_proc …）。本地临时存储过程只有创建它的连接的用户才能够执行它，而且一旦这位用户断开与SQL Server的连接（也就是注销SQL Server 2005），本地临时存储过程就会自动删除，当然，这位用户也可以在连接期间用DROP PROCEDURE命令删除他所创建的本地临时存储过程。

2.3.2 全局临时存储过程

不论哪一个数据库是当前数据库，只要所创建的存储过程名称是以两个井字号（##）开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程。全局临时存储过程一旦创建，以后连接到数据库的任意用户都能执行它，而且不需要特定的权限。因此，必须注意其名称不能和其他连接所采用的名称相同。

不论创建的是本地临时存储过程还是全局临时存储过程，只要服务器管理平台停止运行，它们将不复存在。

2.4 远程存储过程

远程存储过程（Remote Stored Procedures）是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。

2.5 扩展存储过程

扩展存储过程（Extended Stored Procedures）是用户可以使用外部程序语言编写的存储过程。为了区别，扩展存储过程的名称通常以xp_开头。扩展存储过程是以动态链接库（DLLS）的形式存在，它要存储在系统数据库master中。

3 存储过程的运行方式

（1）能够包含执行各种数据库操作的语句，并且可以调用其他的存储过程。

（2）能够接受输入参数，并以输出参数的形式将多个数据值返回给调用程序（Calling Procedure）或批处理（Batch）。

（3）向调用程序或批处理返回一个状态值，以表明成功或失败（以及失败的原因）。

（4）存储过程（Stored Procedures）是一组为完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字给出参数（如果该存储过程带有参数）来执行它。

4 存储过程的优点

（1）通过本地存储、代码预编译和缓存技术实现高性能的数据操作。

（2）通过通用编程结构和过程实现编程框架。如果业务规则发生变化，可以通过修改存储过程来适应新的业务规则，而不必修改客户端的应用程序。这样所有调用该存储过程的应用程序就会遵循新的业务规则。

（3）通过隔离和加密的方法提高数据库的安全性。数据庠用户可以通过得到权限来执行存储过程，而不必给予用户直接访问数据库对象的权限。这些对象将由存储过程来执行操作，另外，存储过程可以加密，这样用户就无法阅读存储过程中的Transact-SQL语句。

【参考文献】

[1]夏慧，李响，齐泉.存储过程在复杂的数据统计查询中的应用[J].医学信息， 2009（08）.

[2]曾毅，王玉萍.SQL Server数据库中存储过程的实现[J].科技信息·科学教研， 2008（25）.

[3]鄢爱兰，鹿江春.数据库存储过程应用研究[J].南华大学学报：自然科学版， 2006（02）.

[责任编辑：杨玉洁]