SQL Server存储过程总结

存储过程简介：

存储过程（Stored Procedure）是在大型数据库中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升

------来源于百度百科

存储过程的种类：

1系统存储过程

以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。

2本地存储过程

用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。

3临时存储过程

分为两种存储过程：

一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;

二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。

4远程存储过程

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

5扩展存储过程

扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。

------来源于百度百科

存储过程的优点：

①重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。

②减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。

③安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

简单讲：

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

3.存储过程可以重复使用,可减少数据库开发人员的工作量

4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

有一点需要注意的是，一些网上盛传的所谓的存储过程要比sql语句执行更快的说法，实际上是个误解，并没有根据，包括微软内部的人也不认可这一点，所以不能作为正式的优点，希望大家能够认识到这一点。

存储过程的缺点：

1：调试麻烦。

2：数据库移植不方便，存储过程依赖与数据库管理系统， SQL Server 存储过程中封装的操作代码不能直接移植到其他的数据库管理系统中。

3：重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

4：如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

5：不支持面向对象的设计，无法采用面向对象的方式将逻辑业务进行封装，甚至形成通用的可支持服务的业务逻辑框架。

存储过程实例：

创建books表

use Test;
--创建测试books表
create table Money (
    id int identity(1,1) primary key,
    money int
);

无参存储过程

--创建一个无参存储过程
if exists(select * from sysobjects where name='usp_getAllMoneyInfo')
drop proc usp_getAllMoneyInfo
go

创建存储过程

--存储过程相当于一个函数 可以有参数，输出参数 as后为方法体 使用return进行返回--
create proc usp_getAllMoneyInfo
--as前面为定义参数 相当于函数中的参数， 不用进行声明--
as
select * from Money
go

执行存储过程

--执行存储过程

exec usp_getAllMoneyInfo;

删除存储过程

--删除存储过程
drop proc usp_getAllMoneyInfo;
go

修改存储过程

alter proc usp_getAllMoneyInfo
as
select Money.money from Money
go

创建一个带参数的存储过程

if exists(select * from sysobjects where name='usp_getMoneyInfo')
drop proc usp_getMoneyInfo
go
--创建一个带参数的存储过程 并执行
create proc usp_getMoneyInfo
--as前面为定义参数 相当于函数中的参数 不用进行声明
@id int
as
select * from Money where id=@id
go
exec usp_getMoneyInfo 2 --在调用后面添加参数
go

创建一个带output参数的存储过程

--创建一个带output参数的存储过程 并执行
if exists(select * from sysobjects where name='usp_getAllMoneyCount')
drop proc usp_getAllMoneyCount
go
create proc usp_getAllMoneyCount
@count int output --外部参数在后面加output类似于c#的out
as
set @count = (select count(*) from Money)
go

declare @cnt int;
exec usp_getAllMoneyCount @count=@cnt output --在调用的时候 要加output的关键字
print @cnt

创建一个带有返回值的存储过程

--创建一个带有返回值的存储过程 并执行
--带有返回值的存储过程--
--return只能返回整型的数据，如果需要返回其他类型的数据，需要使用output输出参数--
if exists(select * from sysobjects where name='usp_getCount')
drop proc usp_getCount
go
create proc usp_getCount
as
declare @count int 
set @count = (select count(*) from Money)
return @count
go
declare @totalCount int
exec @totalCount = usp_getCount
print @totalCount

文章持续更新，如果喜欢，请拿起你们可爱的小手，给我点个赞吧！

点赞是一种积极的生活态度，赞一个吧！