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语法如下:
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
参数说明:
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
execute au_info 'Dull','Ann'
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