存储过程

以前做《机房收费系统》时，和数据库连接的T—SQL程序都保存在本地，通过一段时间对数据库的学习后了解到数据库中的存储过程也可以完成相同的操作，且其创建的T—SQL程序保存在SQL Server中。它在服务器端对数据库中的数据进行处理，并将结果返回到客户端。

以前做《机房收费系统》时，和数据库连接的T—SQL程序都保存在本地，通过一段时间对数据库的学习后了解到数据库中的存储过程也可以完成相同的操作，且其创建的T—SQL程序保存在SQL Server中。它在服务器端对数据库中的数据进行处理，并将结果返回到客户端。这样就避免了从客户端多次连接并访问数据库的操作，从而提高客户端的工作效率。

1.存储过程的使用

存储过程是集中存储在SQLServer中的SQL语句和流程控制语句的预编译集合，用以实现某种任务（增删改查等）。任何一组Transact——SQL语句构成的代码块，都可以作为存储过程保存起来。

1）创建：

Create procedure存储过程名[;编号]

[{@参数数据类型}[varying][=默认值][output]][，…n]

With

{recompile|encryption|recompile,encryption}]

As

SQL语句[…n]

参数说明：

存储过程名对于数据库及其所有者必须唯一。创建局部临时存储过程时，可在存储过程名前加一个#号，创建全局临时存储过程则可加两个#，用以简单区分。

编号：可选整数，用来对同名的存储过程分组，以便用一条drop procedure语句即可将同组的存储过程一起删除

@参数：过程中的参数，每个过程的参数仅用于该过程本身。默认情况下，参数只能代替常量。

output：表明参数时返回值。使用output参数可将信息返回给调用过程。

recompile：表明不保存该存储过程的执行计划，该存储过程将在运行时重新编译。

encryption：对存储过程进行加密，即不能修改该存储过程中的SQL语句。

2）执行

使用excute语句执行存储过程，语法格式为：

[[exec[ute]]

{[@返回状态=]{存储过程名|@存储过程名变量}}

[[@参数名称=]{值|@变量[output]|[default]}]

[,…n]

[With recompile]

（Ps:SQL中的EXEC命令有两个用法：执行一个存储过程，或者执行一个动态批次。批次是一个内容为SQL语句的字符串）

3）查看和修改存储过程

比较简单一点的就是在企业管理器中对存储过程右击进行相应操作，再者就是用alter procedure语句修改和sp_rename进行重命名，删除时用dropprocedure语句即可

2.优缺点

在本文的开头已经提到存储过程的功能是可以由其它程序替代的，那么一个数据库是否有必要设计存储过程呢？

1）优点

a.安全性：可以防止SQL注入式攻击，可设定只有某些用户具有对存储过程的使用权

b.提高性能：存储过程只在创建时进行编译，一般SQL语句每执行一次就编译一次，所以存储过程允许程序更快地执行

c.减少网络流量：存储过程的代码直接存储在数据库中，可以减少SQL语句从客户端到服务器端的传输量

d.存储过程可以减少客户端代码的重复，且其可重复使用，从而减少数据库开发人员的工作量

2）缺点

a.移植问题：由于存储过程将应用程序绑定到了SQL Server，因此使用存储过程封装的业务逻辑将限制应用程序的可移植性。

b.重新编译问题：后端代码是运行前编译的，如果带有引用关系的对象发生改变，受影响的存储过程需要重新编译。

3.实例

以机房收费系统的数据库为例，在数据库表中查找指定的卡号并为该卡的金额增加10元。创建存储过程

	Create procedure addCash_proc		--创建一个存储过程，名为addCash_proc
		@CardNo integer			--声明一个参数
	with encryption				--对存储过程进行加密
	as
	begin
		--查找到相应卡号的金额并加上10
		update Student_Info set cash=cash+10 where CardNo =@CardNo 
	end

执行语句如下

	Exec addCash_proc @CardNo='2'

程序执行后卡号为2的金额增加10元。

小结：

存储过程非常便捷，一般熟悉了SQL语句就可以很容易的掌握存储过程的使用，但是如果大量使用可能会给系统维护带来麻烦。在实际应用中，有的人偏好于使用存储过程有的人则尽量避免，在此要考虑好是让数据库服务器负担更多的运算量还是交给客户端，权衡存储过程带来的利与弊再酌情使用。