Sql Server笔记之存储过程
摘要：本文从多个方面阐述了存储过程的内容，相信对大家更好地学好存储过程会有所帮助。
存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。
一、使用存储过程的优势：
1、模块化程序设计：只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。
2、执行效率的优化：大批量数据操作或重复执行时，存储过程的优势将得到很好的体现。只需在首次执行时对其编译即可，而执行Transact-SQL 语句却每次都需编译及优化，同时需要从客户端重复发送。
3、减少网络流量：一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。
4、安全性：即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。
二、存储过程的三种类型：
1、系统存储过程：以SP_开头，用来进行系统的各项设定。
例：sp_addlogin：创建新的 Microsoft® SQL Server™ 登录，使用户得以连接使用 SQL Server 身份验证的 SQL Server 实例。
sp_adduser:为当前数据库中的新用户添加安全帐户。
2、扩展存储过程：以XP_开头，用来调用操作系统提供的功能。
例：xp_deletemail： 删除 Microsoft® SQL Server™ 收件箱中的邮件。
3、用户自定义的存储过程。
三、存储过程的创建语法与参数说明：
1、存储过程的设计规则：
1、定义时存储过程中不能使用下列语句：Create Default、Create Trigger、Create View、Create Rule
2、存储过程中参数的最大数目为2100
3、存储过程中局部变量的最大数目仅受可用内存的限制。
4、根据可用内存的不同，存储过程的最大大小可达128MB.
5、远程存储过程不参与事务处理，即执行后不能进行回滚更改操作。
2、Create Procedure语法如下：
Create Proc[edure] Proc_Name [；number] [{ @parameter data_type} [=default][output] ] [,…n] [with {recompile | encryption | recompile , encryption }] [For Replication] As Sql_statement […n]参数说明：
1、Proc_Name：新建存储过程的名称。
2、 ；number：可选参数，用来对同名的过程分组，以便用一条Drop procedure语句将同组的过程一起删除。
3、@parameter 过程中的参数。
4、data_type：参数的数据类型。
5、default：参数的默认值。
6、output：表明参数是返回参数。使用output参数可将信息返回给调用过程。
7、Recompile表明Sql Server不会缓存该过程的计划，该过程每次运行时都会重新编译。
Encryption表示SQL Server加密存储过程的内容（加密以后任何人都看不到存储内容）。
8、For Replication：可用作存储过程筛选，且只能在复制过程中执行。不能与With Recompile选项一起使用。
9、AS 指定过程要执行的操作。
10、Sql_Statement:过程中要包含的任意数目和类型的Transact-SQL语句。
四、存储过程创建实例：
1、一个最简单的存储过程创建实例：
查询表Book的内容的存储过程：
Use pubs --指定数据库
go
Create Proc query_ authors --创建查询Book表的存储过程
As select * from authors
Go
Exec query_book --执行刚才创建的存储过程
2、使用带有复杂Select语句的简单过程
use pubs
go
--判断pubs数据库中是否存有此存储过程，如有则删除
if exists( select name from sysobjects
where name='au_info_all' and type='P')
--删除原有的存储过程
drop procedure au_info_all
Create procedure au_info_all
as
select au_lname,au_fname,title,pub_name
--多次进行内联操作
from authors a inner join titleauthor ta
on a.au_id=ta.au_id inner join titles t
on t.title_id=ta.title_id inner join publishers p
on t.pub_id=p.pub_id
go
exec au_info_all
3、 使用带有参数的简单过程
use pubs
go
if exists (select name from sysobjects
where name='au_info' and type ='P')
drop procedure au_info --删除原有存储过程
create procedure au_info
@lastname varchar(40), --定义参数1
@firstname varchar(20) --定义参数2
as
select au_lname,au_fname,title,pub_name
from authors a inner join titleauthor ta
on a.au_id=ta.au_id inner join titles t
on t.title_id=ta.title_id inner join publishers p
on t.pub_id=p.pub_id
--参数1与参数2在条件语句中得到使用
where au_fname=@firstname and au_lname=@lastname
go
4、 使用with Encryption选项
Use pubs
Go
If exists (select name from sysobjects
where name=’encrypt_this’ and type=’p’)
Drop procedure encrypt_this
With encryption --with加密存储过程内容
As
select * from book
Go
Exec sp_helptext encrypt_this
5、 使用参数默认值NULL创建存储过程
Create proc DeafNull @table varchar(30)=NULL
As IF @table is NULL
Print ‘Table name is not NULL’
Else
Select Table_Name=sysobjects.name,Index_Name=sysindexes.name,
Index_ID=indid From sysindexes inner join sysobjects
On sysobjects.id=sysindexes.id
Where sysobjects.name = @table
五、存储过程的修改与删除
存储过程的修改在语法上与创建存储过程差不多，最主要的差别是存储过程的修改是以Alter为标识，存储过程的创建是以Create作为标识。
存储过程的删除语法为：Drop proc过程名
下面用两个实际例子来说明存储过程的修改与删除：
1、针对上面演示的存储过程query_book,修改如下：
use pubs
go
Alter proc query_authors
As select au_id,au_lname from authors
where au_lname like 'S%'
go
exec query_authors
2、针对存储过程query_book,删除如下：
Use pubs
go
if exists( select * from sysobjects where name='query_authors' and type='P')
drop procedure query_authors
go
结束语：
关于存储过程要写的东西实在很多，在实际开发时应用也非常灵活，在这里我也只是起到一个抛砖引玉的作用。可能很多朋友对存储过程的语法不甚了解也不想去了解，但是在此我想说的是不管怎样，一定要让自己明白它所表达的意思。只有这样，才能灵活运用。最后是多练习了，多写sql 查询语句，在实践中提高。好了，就这些，与大家一起进步！