存储过程

存储过程可以使用户对数据库的管理工作变得更容易。当开发一个应用程序时，为了易于修改和扩充，经常会将负责不同功能的语句集中起来，而且根据用途分别放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句，即是“过程”。

一、存储过程概述

SQL-server 2012的存储过程其实就是一个具有独立功能的子程序，以特定的名称存储在数据库中，可以在存储过程中声明变量、有条件的执行语句以及实现其他各项强大的程序设计功能。

存储过程以一个名称存储并作为一个单元进行处理（这样能够提高系统的应用效率和执行速度），由应用程序通过调用来执行。存储过程也可以由客户调用，也可以由另一个过程或触发器调用。

存储过程一经写完，就会立即被编译成可执行代码存储在系统表内，不需要重复编译。

存储过程的优势：

1. 模块化程序设计。只需创建一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。
2. 加快T-SQL语句的执行速度。如果某一操作需要大量T-SQL语句或需要重复执行多次，存储过程将会比批处理代码执行要快。
3. 减少网络流量。一个需要几百条T-SQL语句才嫩实现的操作，可以只由一条执行过程代码的语句来实现。
4. 更高的安全性。首先，数据库用户可以通过得到权限来执行存储过程，而不必给予用户直接访问数据库对象的权限；另外，存储过程可以加密，这样用户就无法阅读存储过程中的T-SQL语句了。

二、存储过程的类型

1.系统存储过程

系统过程主要存储在master数据库中，并以“sp_”为前缀。
尽管存储在master数据库中，但是仍可以被其他数据库调用，在调用时，不必在存储过程名前加上数据库名。
注：一个用户需要在所有数据库中拥有执行一个系统存储过程的许可权，否则在任何数据库中都不能执行系统存储过程。

2.本地存储过程

本地存储过程也就是用户自行创建的并存储在用户数据库中的存储过程。

3.临时存储过程

临时存储过程又可以分为以下两种。
1）本地临时存储过程
不论哪一个数据库是当前数据库，如果在创建存储过程时，其名称以“#”开头，则该存储将成为一个存放在tempdb数据库中的本地临时存储过程。

注意：本地临时存储过程只能由创建他的连接的用户才能够执行他，并且一但这位用户断开与SQL server的连接，本地临时存储过程就会自动删除。当然，也可以通过drop reocedure命令在连接期间删除创建的本地临时存储过程。

2）全局临时存储过程
不论哪一个数据库是当前数据库，如果在创建存储过程时，其名称以“##”开头，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程。全局临时存储过程一旦创建，以后连接到SQL server 2012的任意用户都能执行它。当创建全局临时存储过程的用户断开与SQL server 2012 的连接时，SQL server将检查是否有用户正在使用它，如果没有，则立即将全局临时存储过程删除；如果有，则会让正在执行的操作继续进行，但不允许任何用户再执行全局临时存储过程，等到所有未执行完的操作执行完毕后，全局临时存储过程就会自动删除。

4.远程存储过程

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

5.拓展存储过程

此过程是可以用外部程序语言（例如C语言）编写的存储过程。

注：拓展存储过程的名称一般以“XP_”开头。拓展存储过程一定要存储在系统数据库master中。

理论知识我们就介绍这么多，接下来，我们开始具体来讲如何进行创建、执行存储过程等等操作。在下面的介绍中，我们从T-SQL语方面进行介绍。

1.如何创建和执行存储过程

创建的语法格式：

1. 创建带参数的存储过程

create procedure 存储过程名 参数1 数据类型 ... 参数n 数据类型 [output]
[with  {recompile | encryption | recompile ,encryption}]
as 
SQL语句

recompile :表明SQL server不会缓存该过程的计划，该过程在运行时重新编译。
encryption ：表明SQL server加密用create procedure语句创建存储过程的定义。

2. 执行的语法格式：

[ [EXEC[UTE] ] [@return_status=] procedure_name[;number]
{[[@parameter=]value | [@ parameter=] @variable [OUTPUT]]}
[WITH RECOMPILE ]

注：
1、执行存储过程使用T-SQL 语中的EXECUTE命令。如果执行存储过程是批处理中的第一条语句，那么不使用EXECUTE关键字也可以。
2、对于存储过程的所有者或任何一名对此过程拥有EXECUTE权限的用户，都可以执行此存储过程。
3、输入参数在存储过程名后逐一给定， 用逗号隔开，不必使用括号。
4、如果没有使用@参数名= value这种方式传入值，则参数的排列必须和建立存储过程所定义的次序对应。
5、用来接受输出值的参数必须加上OUTPUT。

下面看下列例题。

【例】在teaching库中创建无参数存储过程，查询每个学生的平均成绩。

create proc proc_name
as
select sno, avg(score) from sc 
group by sno
go

执行：execute proc_name
【例】在teaching库中创建带参数的存储过程，查询每个学生的基本信息。

create proc GetStudent @sno char(9)
as
select * from student where sno=@sno
go

执行：exec GetStudent '201501001'

【例】在teaching库中创建带参数的存储过程，修改某个学生某门课的成绩。

create proc updateStudent @s_sno char(9), @s_cno char (4),@s_score int
as
update sc set score=@s_score where sno=@s_sno and cno=@s_cno

执行：execue updateStudent '123456789' 'X003' 98
【例】在bankcard库中使用流程控制语句创建存储过程。如果支出总金额超过3000，则奖励10。

create proc add_10 @Accno char(20)
with encryption
as
if(select sum(Expense) from Trecord where Accon=@Accon and TDay=getdate())>=3000
begin
update Account set Balance=Balance+10 where Accno=@Accno
insert Trecord(TDate,Accno,Income,Abstract) values (getdate(),@Accno,10,'银行活动奖励')
end

执行：execute add_10 '1234567891234567891'
【例】在bankcard数据库中创建带output参数的存储过程，用于计算指定的储户的总余额，存储过程中使用一个输入参数（身份证号）和两个输出参数（储户姓名和总余额）。

create proc s_bankcard @idno char(18),@sname varchar output,@sbalance money output
as
select @dname=Dname from depositor where idno=@idno
select @sbalance=sum(Balance) from account where idno=@idno
go

执行：

Declare @dname nvarchar(10),@sbalance money        //声明两个变量
EXECUTE s_balance '133***198708150101',@dname OUTPUT,@sbalance OUTPUT
Print '储户'+@dname+ '目前总余额'+str(@sbalance)    //输出

2、修改和删除存储过程

1. 修改存储过程

除了使用SSMS操作来完成之外，也可以通过T-SQL语句中的alter语句来完成。
语法格式如下。

ALTER { PROC | PROCEDURE } procedure_name [ ; number ]
    [ { @parameter data_type }
    [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ] 
[ WITH { RECOMPILE | ENCRYPTION  | RECOMPILE , ENCRYPTION } [ ,...n ] ]
 [FOR REPLICATION]
AS  sql_statement [ ...n ]

接下来看几个例题。

【例】修改存储过程add_10，将3000元和10元设置为两个参数的默认值，使存储过程应用更灵活。

alter proc add_10 @Accno char(20),@expense money=3000,@add int=10
with encryption
as
if(select sum(Expense) from Trecord where Accon=@Accon and TDay=convert(varchar(10), getdate(),120))>=@expense
begin
update Account set Balance=Balance+@add where Accno=@Accno
insert Trecord(TDate,Accno,Income,Abstract) values (convert(varchar(10), getdate(),120),@Accno,@add,'银行活动奖励')
end

2. 删除存储过程

对于不需要的存储过程可以在SQL Server Management Studio中删除；也可以使用T-SQL语句中的DROP PROCEDURE命令删除。
T-SQL语句的语法格式为：

DROP PROCEDURE {procedure_name} [,…n]

procedure_name：指要删除的存储过程或存储过程组的名称。

【例】删除存储过程s_balance。

drop  procedure s_balance

3、查看存储过程

1. 用SSMS查看存储进程

右击------>编写存储过程脚本为------>create 到C------>新查询编辑窗口

2. 用T-SQL语句查看存储进程

可以执行系统存储过程sp_helptext，用于查看创建存储过程的命令语句；
可以执行系统存储过程sp_help，用于查看存储过程的名称、拥有者、类型、创建时间等基本信息。
语法格式：

sp_helptext  存储过程名称
或
sp_help  存储过程名称