ＳＱＬ Ｓｅｒｖｅｒ存储过程使用剖析

摘要：文章介绍了存储过程的基本概念及其优点，对存储过程在数据库编程中的应用作了详细的论述，并讨论了存储过程的设计策略及使用策略。

关键词：SQL Server存储过程；数据库应用；应用策略

中图分类号：TP393文献标识码：A文章编号：1009-2374（2009）07-0118-03

一、SQL Server的存储过程概述

存储过程（Stored Procedure）是一组已被编辑好的，存储在服务器上的能够执行某种功能的预编译的Transact-SQL代码。它是一种封装重复任务操作的方法，支持用户提供的参数变量，具有强大的编程能力。存储过程通过参数传递、 进行判断、声明变量，以及返回信息来扩充标准SQL 语言的功能。可以把存储过程看成是以数据库对象形式存储在 SQL Server 中的一段程序或函数。当执行存储过程时，该存储过程是在 SQL Server上运行，而不是在客户端发送请求。存储过程可以是一个简单的 SQL 语句，如 select * from gz。存储过程也可以是由一系列用来对数据库表实现复杂商务规则的SQL 语句和控制流语言语句所组成。存储过程非常类似于DOS系统中批处理文件（*.bat）。在批处理文件中，可以包含一组经常执行的命令，这一组命令可以通过批处理文件的执行而执行。同样的，存储过程也是把要完成某项任务的许多SQL语句写在一起，组成命令集合的形式，然后，通过执行存储过程执行相应的任务。

存储过程与客户端SQL命令操作的应用程序相比，具有下列优点：

1．可以大大提高系统效率。对于客户端来说，只要调用已在服务器中存放的存储过程，并通过网络发送该过程名和少量入口参数，数据库服务器就可以执行该过程，在执行完成后只返回结果集数据给客户端应用程序，而无须再在网上传送大量的命令和中间结果数据，这样可以充分利用服务器的高性能来提高运算速度，并减轻网络负担。

2．提高系统的可维护性。由于存储过程是以代码形式存在的一种数据库对象，所以它的创建和删除都很简单，并且不会影响到库中的其他数据对象。例如在银行报表管理系统中，有些计算规则往往会随着时间和客户要求的改变而改变，如果将这些业务规则的处理直接放到客户应用程序中去做，在规则发生改变时需要修改大量的客户端源程序代码，然后重新进行编译和链接，这样不仅增加了客户程序的维护难度，而且降低了系统的效率。但如果将这些规则放在服务器的存储过程中，而由客户端应用程序来调用此过程，情况将大为改观。当某个业务的计算规则发生变化时，只需要修改或重写对应的服务器存储过程就可以了，从而提高了系统的可维护性。

3．增强系统的安全性。将存储过程用在安全性上就是利用其“授权”的特性，因为存储过程就像数据库中的其他对象，如“表”和“字段”那样可以被操作，所以只有当一个用户被授予某个权限后，才可以做与自身的权限相符合的动作。

4．增强SQL语言的功能和灵活性。由于SQL语言自身的限制，它不能声明变量，不能使用if/else结构，不能使用循环语句。存储过程利用流控制语句和内部函数实现了SQL语言本身所不能做到的事，等于从另一个方面提高了SQL语言的功能和灵活性。

二、存储过程在数据库编程中的应用

存储过程的应用具有很大的灵活性，在具体的开发过程中往往可以根据实际需要的不同而采用相应的技术与方法。

1．存储过程的嵌套调用。有时候可能要对一批数据进行重复的处理，或者在一个存储过程中要用到其他存储过程的处理结果，这时就可以使用存储过程的嵌套调用来实现。下面结合一个用于进行数据统计和分析的具体实例来讲述存储过程嵌套调用的实现方法。首先创建一个用于数据统计的子存储过程，把调用它的父存储过程要传给它的统计条件定义为输入参数，而把父存储过程想要得到的统计结果定义为输出参数，并在子存储过程中计算出它们的值作为返回结果。当然，这个子存储过程也可以作为一个独立的存储过程来使用。建立子存储过程如下：

CREATEPROCEDUREup_jsszl

@zsbmnoinchar（3），// 定义输入参数

@tjyfinint，

@ylfszlmoneyoutput// 定义输出参数

AS

BEGIN

DECLARE@ylfszmoney，

@ylfyzmoney//声明局部变量

......

SELECT@ylfszl=@ylfsz/@ylfyz，// 得到并返回结果

RETURN

END

然后创建一个父存储过程，在父存储过程中通过使用特定的参数值去调用子存储过程来得到相应的统计结果。

CREATEPROCEDUREup_bjszl

@zsbmnoinchar（3），// 定义输入参数

@tjyfinint

AS

BEGIN

DECLARE

@zsbmnochar（3），// 声明局部变量

@tjyfint，

@ylfszlmoney

... ...

EXECUTEup_jsszl

@zsbmno，// 调用子存储过程

@tjyf，

@ylfszl

output

... ...

RETURN

END

由此可见，可以将那些实现不同功能的代码模块化，然后通过使用存储过程的嵌套调用将它们组合起来去实现一些更复杂的功能。这样既可以增强代码的可重用性，又可以使整个应用程序的结构更加清晰，而且能够有效地提高应用程序的开发效率和系统的可维护性。

2．在存储过程中使用游标。游标提供了一种处理结果集中记录的灵活手段，通过它可以根据需要对结果集中的各条记录进行一些相应的处理。在存储过程中需要对数据库中的表或表中满足一定条件的记录集中的各条记录进行处理时就可以利用游标来进行。游标的使用要经过声明游标、打开游标、读取游标、关闭游标和释放游标这五个步骤。下面的存储过程就是通过在满足特定条件的记录集上定义一个游标来逐条处理记录集中的每一条记录的。

CREATEPROCEDUREup_fxszl

@zsbmnoinchar（3），// 定义输入参数

@tjyfinint

AS

BEGIN

DECLARE@bmnochar（3），// 声明局部变量

@ylfszlmoney

......

DECLAREcur_bmnoCURSORFOR// 声明游标

SELECT JZSNO FROM JZS WHERE FJNO = @zsbmnoin

OPENcur_bmno// 打开游标

FETCHcur_bmnoINTO@bmno//用游标取一条记录

WHILE@@sqlstatus=0// 处理记录集中各记录

BEGIN

EXECUTEup_jsszl@bmno，// 调用子存储过程

@tjyfin，

@ylfszloutput

......

FETCHcur_bmnoINTO@bmno//用游标取记录

END

CLOSEcur_bmno// 关闭游标

DEALLOCATECURSORcur_bmno// 释放游标

......

END

在上面的存储过程中声明了一个游标来逐条取得从数据库表中得到的结果集中的记录，并在循环控制语句中将该记录作为入口参数去调用子存储过程来对每一条记录进行处理，然后再对处理结果作进一步的分析。可以看到通过将游标和存储过程调用结合起来，可以很方便地对结果集中的各条记录进行处理。

3．在存储过程中使用临时表。在使用存储过程时，一般是通过定义输出参数来得到它的计算结果。但是当进行一些复杂的数据处理要求返回的数据量比较大时，如果还是通过输出参数来返回计算结果，则会因为定义的参数过多而使存储过程显得很杂乱，而且在存储过程中对可以定义的参数个数也是有一定限制的。另外，有时可能有多个存储过程要用到一些相同的中间结果，而且连这些中间结果甚至也是由几个存储过程共同产生的。这时使用临时表将会带来很大的便利。在Sybase存储过程中，可以用CREATETABLE命令来创建临时表，并在表名前加上符号“#”作为临时表的标识，然后就可以像使用数据库中其他的表一样使用它。例如：

CREATEPROCEDUREup_cxszl

@zsbmnoinchar（3），// 定义输入参数

@tjyfinint

AS

BEGIN

DECLARE@bmnochar（3），// 声明局部变量

@ylfszlmoney

......

CREATETABLE#SFSZLCX（BMNOchar（3）null，//创建临时表

YLFSZLmoneynull）

......

DECLAREcur_bmnoCURSORFOR// 声明游标

SELECT JZSNO FROM JZS WHERE FJNO = @zsbmnoin

OPENcur_bmno// 打开游标

FETCHcur_bmnoINTO@bmno //用游标取一条记录

WHILE@@sqlstatus=0// 处理记录集中各记录

BEGIN

EXECUTEup_jsszl@bmno，// 调用子存储过程

@tjyfin

INSERT#SFSZLPX（BMNO，YLFSZL）// 结果存入临时表

VALUES（@bmno，@ylfszl）

FETCHcur_bmnoINTO@bmno// 用游标取记录

END

CLOSEcur_bmno// 关闭游标

DEALLOCATECURSORcur_bmno// 释放游标

RETURN

END

由于临时表的生命周期与创建它的存储过程相同，随着存储过程执行的结束，临时表也就不复存在了。所以如果想要在一个存储过程中利用其他存储过程产生的中间结果，则应该在父过程中创建临时表，在子过程中直接引用它，并把子过程所得的中间结果存入临时表中。

三、存储过程的应用策略

使用存储过程的目的是为了提高应用系统的运行效率，增强系统的可维护性，保证数据的完整性与一致性。下面给出了采用存储过程的一些基本策略：

1．重复调用的、需要一定运行效率的逻辑与运算处理宜采用存储过程实现。虽然客户端应用程序也能进行这样的逻辑与运算处理，但存储过程的运行效率高。因为它是编译与优化好的过程程序，而客户端应用程序的每个SQL语句都要临时送入数据库服务器进编译和优化执行。如果客户端应用程序包含多条SQL语句，客户端应用程序则要通过网络与数据库服务器多次通信才能完成任务，运行效率进一步降低。

2．易于变化的业务规则应放入存储过程中。例如，要编写一段计算奖金的处理程序，而奖金的发放办法会经常根据具体情况进行调整，此时应把奖金业务程序写成存储过程，让客户端应用程序调用此过程来得到奖金数据。当计算奖金的办法发生变化时，只需修改存储过程即可，而应用程序不用任何改动，这样增强了应用程序的可维护性。

3．需要集中管理和控制的逻辑与运算处理应放入存储过程中。存储过程只需在数据库服务器中保存一份拷贝，所有的应用子系统均可调用执行该存储过程，而无须每个应用子系统编写相同的处理逻辑程序，这样也便于应用程序的维护与版本的管理。

4．存储过程可作为保证系统数据安全性和数据完整性的一种实现机制。例如，一个用户可以被授予权限去调用存储过程执行修改某特定表的行列子集，即使他对该表没有任何其他权限，这样可保证系统数据的安全性。同样，通过特殊类型的存储过程——触发器还可使相关的表数据操作在一起发生，从而维护数据的完整性。

5．需要对基本表的数据进行较复杂的逻辑处理才能返回所需的结果数据集，应采用存储过程完成。在应用程序开发中，经常会遇到这样的情况，应用程序报表数据、统计分析数据等很难直接从基本数据表处理得到，而需要对基本表进行较复杂的逻辑操作处理或者需要建立若干过渡临时表，才能得到最终的结果数据。如果这种处理操作均在客户端应用程序完成，效率是低下的。相反若采用存储过程实现，诸多问题都可迎刃而解。

四、结语

存储过程的功能很强大，它可以根据用户的设置完成从简单的查询到应用程序逻辑控制的任何事情。存储过程在信息管理系统中的应用非常广泛，它为实现复杂的数据应用提供了很好的解决方法。它不仅能提高应用系统的运行效率增强系统的可维护性，还可以保证数据的完整性与可靠性。

参考文献

[1]施伯乐．数据库系统教程（第2版）[M].北京：北京高等教育出版社，2003．

[2]赵杰，李涛，朱慧．SQL Server数据库设计与实现教程[M].北京：北京大学出版社，2003．

[3]杜军平，黄杰．SQL Server 2000数据库开发[M]．北京：机械工业出版社，2001．

[4]温春明．SQL Server 存储过程研究[J]．福建电脑，2007．

[5]曹耀辉．SQL Server存储过程在系统开发中的应用[J]．现代电子技术，2005．

[6]刘独玉，罗彬．基于MS SQL Server的存储过程技术的研究[J]．四川轻化工学院学报，2001．

[7]张义德，于西俭．基于SQL Server的存储过程的研究[J]．航空计算技术，2001．

[8]刘云根，陈萌．存储过程在数据库编程中的应用[J]．科技广场，2005．

[9]王雪媛．存储过程研究[J]．内江科技，2006．

[10]陆鑫．存储过程及其应用方法[J]．计算机应用，1999．

作者简介：胡晓波（1979－），男（土家族），湘西民族职业技术学院教师，研究方向：计算机教学。