SQL Server存储过程与其他开发对象的比较研究与应用

欧阳艳阶，祝丰菊

摘 要： 在数据库项目开发过程中，如何避免应用程序在客户端频繁地执行数据访问操作，提高程序的运行效率，是软件工程师需要考虑的重要问题。本文分析并阐述了SQL Server数据库系统中存储过程的执行过程及优势，并通过案例介绍了SQL Server存储过程在开发过程中的具体应用。

关键词： SQL Server；数据库；存储过程

中图分类号： TP311.13 文献标识码： A 文章编号： 2095-8153（2017）01-0110-03

SQL Server是目前企业普遍应用的关系型数据库系统之一，它功能强大，性能稳定。它还提供众多数据处理工具，包括存储过程、规则、约束、事务、锁等。这些开发工具对提高开发人员的开发效率提供了重要帮助。因此SQL Server成为管理信息系统、数据库系统、网络集成等项目中的主要数据库平台。

为了提高开发效率，微软公司在SQL Server系统中提供了存储过程、视图、规则、约束、触发器等开发工具，其中存储过程因其高效的执行过程而深受数据库开发人员的青睐。SQL Server系统中存储过程（Procedure）能将商业逻辑，比如固定的功能模块、计算流程等封装其中，大大提高整个软件系统的可维护性。因此在大型管理信息系统开发过程中尤其注重存储过程的开发与使用[1]。

1 存储过程的分类及执行过程

存储过程是由SQL语句和流控制语句组成的过程性程序。微软公司在SQL Server数据库系统中将存储过程分为五类，其中常用的开发与运行存储过程有三类，它们是：系统存储过程、用户存储过程和扩展存储过程[2]。

（1）系统存储过程：该类存储过程由微软公司设计，由SQL Server在安装过程中创建。其主要功能是获取数据库系统、操作系统及硬件等底层信息，也可为系统管理员和授权用户提供数据更新服务。系统存储过程均以sp_为名称前缀。

（2）用户存储过程：由用户为完成某一特定功能而编写的存储过程。管理信息系统开发过程中重要开发的也是该类存储过程。

（3）扩展存储过程，是对动态链接库DLL 函数的调用。一般涉及底层软件、硬件等开发时主要采用该类存储过程。

存储过程首次执行时，SQL Server数据库系统将其优化、编译，并存储在SQL Server数据库系统服务器端的高速缓存中。只要服务器持续运行，存储过程即一直存在，客户端和服务器端程序均可调用执行，且允许不同的用户访问同样的代码。

2 存储过程与其他开发对象的比较优势

存储过程的用途几乎无任何限制。返回DDL语句查询结果、执行复杂的数据计算、有效性检验、事务等均可用存储过程实现。在企业实际数据库系统项目开发过程中，笔者通过对存储过程、视图、规则、约束、触发器等开发工具的应用，总结了存储过程与SQL Server数据库其他开发对象的比较优势。

1.适合模块化程序开发

存储过程被创建后存储在SQL Server数据库服务器端，通過应用程序能反复调用，其代码也能被应用程序独立修改。因此存储过程可以增强系统可维护性和代码的共享性，提高开发的质量和效率，适合模块化程序开发。

2.提高执行效率

存储过程第一次执行后即被编译，并存储在高速缓冲中。其后续执行不需重新编译，因此能大大提高程序的执行效率。适合对系统运行效率较高的项目开发，比如计费系统。

3.降低网络通信流量

微软公司为存储过程设计了一套优化的执行方案。存储过程第一次执行后即被编译。只要服务器持续运行，存储过程即一直存在。后续执行时不需客户端频繁地将命令和数据传输到服务器端，只需发布调用命令，即可将存储过程执行后的返回结果传输至客户端进行本地计算，因而可以明显降低网络通信流量。

4.保证数据安全性

SQL Server进行安全性授权时，可以仅授予客户访问存储过程的权限，而不授予客户访问数据库中表、视图等涉及具体数据的权限。这样既保证了客户不能直接查看、修改、删除表或视图中的数据，又能让客户方便地通过存储过程操纵数据库中的数据，从而保证数据的安全性。

3 存储过程应用举例

下面案例程序代码的作用是通过存储过程从商品表AjaxGoods中进行查询。该案例展示了存储过程在与其他对象上的比较优势。其优势如下：

（1）涉及多语句，用存储过程将其模块化；

（2）在服务器端编译并存储与高速缓存，提高执行效率；

（3）执行时仅EXECUTE？MyGoods_Cursor一句话调用，将结果传至客户端，降低网络通信量；

（4）通过对存储过程MyGoods_Cursor授权，不对AjaxGoods表授权，保证数据安全性。

案例如下：

某超市进行货物盘存，现欲统计出各商品大类剩余商品的数量，如家电类剩余商品数量、日化类剩余商品数量，并将结果组成一个字符串显示。如“151，209，28”。采用存储过程开发代码如下：

CREATE PROCEDURE MyGoods_Cursor //定义存储过程

@MyResult varchar（255） output //声明输出变量

AS

Declare Goods_Cursor cursor //声明商品游标变量

For

Select Goods_ID from AjaxGoods

Set @MyResult=

Declare @Field int //声明临时存放GoodsID的变量

Open Goods_Cursor //打开游标

Fetch Next From Goods_Cursor Into @Field //將实际ID赋给变量

While（@@Fetch_Status=0） //循环开始

Begin

If @MyResult =

Select @MyResult = Convert（nvarchar（2），Count（*））

From GoodsCounty

Where GoodsID=@Field

Else

Select @MyResult = @MyResult + '，' + convert（nvarchar（2），count（*））

From AjaxGoods

Where GoodsID=@Field

Fetch Next From Goods_Cursor Into @Field //下一个GoodsID

End

Close Goods_Cursor//关闭游标

Deallocate Goods_Cursor //释放游标引用

GO

执行存储过程：EXECUTE MyGoods_Cursor4 存储过程的应用场所及应用策略总结

通过对存储过程与其他开发对象的比较分析可以看出，存储过程具有提高系统的执行效率、保证数据安全、增强系统可维护性、降低网络通信量等诸多优势。下面结合笔者开发经验，对存储过程的应用场所及应用策略作如下总结。

1.需要反复调用的计算模块与商业逻辑采用存储过程

虽然客户端也能通过代码取代存储过程的部分功能，但存储过程运行过程中，首次执行即已在服务器端进行了编译与优化，而客户端代码的每条SQL 语句都要通过前台开发工具临时送入服务器进行编译和优化执行，因而存储过程运行效率远高于在客户端的处理。而且若客户端代码包含多条SQL 语句，客户端则要通过网络与数据库服务器多次通信才能完成任务，运行效率会进一步降低。因此需要反复调用的计算模块与商业逻辑采用存储过程

2.经常变化的业务规则采用存储过程

假如要开发一个统计销售利润的计算程序，而统计销售利润的方法经常会根据实际情况进行变化调整，此时应把统计销售利润程序编写成存储过程，让客户端代码调用该存储过程来计算出销售利润数据。当统计销售利润的方法发生变化时，只需修改存储过程即可，而客户端应用程序代码不需作任何改变，这样大大降低了应用程序的维护难度和维护工作量。因此经常变化的业务规则采用存储过程。

3.保证表数据安全采用存储过程

假如一个客户对数据表没有任何访问权限，系统仍然可以授予该客户调用存储过程的权限，以执行修改数据表的行、列及子集。这样做可保证表数据的安全性。因此保证表数据安全建议采用存储过程。

4.保证表数据完整采用存储过程

数据完整性可通过规则、约束等对象来操作。但事务级的完整性最好通过存储过来来实现。首先在存储过程中定义事务，再将SQL语句包裹在事务中，通过触发器或其他对象调用，可使相关的表数据操作按事务要求一起发生或一起回滚，从而保证数据的完整性。

5.复杂的数据操作采用存储过程

在应用程序需要对基本表进行复杂的DDL、DML操作才能得到最终结果情况下，若将复杂的DDL、DML操作放在客户端，其效率是非常低下的。若采用存储过程实现，诸多问题都可迎刃而解。包括效率、简单、方便等。

5 结语

存储过程作为 SQL Server 数据库中重要的技术与工具，在项目开发过程中如能用合理运用，不仅能有效地提高了系统性能，大大提高系统的可维护性，降低通信流量，还可通过存储过程对客户的权限进行限制，保证数据的安全。

[参考文献]

[1]陈畅亮.SQL Server性能调优实战[M].北京：机械工业出版社，2015：70.

[2]卫 琳.SQL Server 2012数据库应用与开发教程[M].北京：清华大学出版社，2014：159.

Research and Application on SQL Server Database Stored Process and

Other Development Objects

OUYANG Yan-jie，ZHU Feng-ju

Abstract： In the database project development process，how to avoid the applications access the client data frequently and improve the operational efficiency of the program is the important issue that software engineers need to consider. This paper analyzes the implementation process and advantages of SQL Server database system stored procedure，introduces the application of SQL Server stored procedure in the development process based on the case analysis.

Key words： SQL Server；database；stored procedure