存储过程简介

存储过程是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
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

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/polobolomi/archive/2008/09/14/2906570.aspx