存储过程

1、存储过程
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

简单来说存储过程可以说是一个记录集，它是由一些SQL语句组成的代码块，这些SQL语句代码像一个方法一样来实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

打个比方吧，SQL语句就相当于武术中的单个动作，比如出拳、或者出脚，而存储过程就相当于一套拳法，把各种动作连贯汇总到一起，形成一个有机的整体来达到一定的目的，各种基本动作通过不同的组合就形成了不同拳法，比如什么长拳、南拳、迷踪拳等等，可以有不同的效果。以这个道理来说，要达到对一个复杂业务流程的管理，靠单个一个动作是不能达到目的的，得靠多组动作，比如先查A表再插入B表，再修改C表才可以完成。就相当于说你遇到一个人，不可能一招制敌，得通过多套动作交手，才可能取胜。

2、SQL中的存储过程
2.1 存储过程语法

CREATE PROCEDURE [procedure_name.]存储过程名[; number]    [(参数#1,…参数#1024)@parameter data_type]    [WITH        {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}    ]    [FOR REPLICATION]AS sql_statement [ ...n ] 程序行

其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数。(SQL Server 7.0以上版本)，参数的使用语法如下：

@参数名数据类型[VARYING] [=内定值] [OUTPUT]

@：每个参数名前要有一个“@”符号，每一个存储过程的参数仅为该程序内部使用。
[内定值]：相当于我们在建立数据库时设定一个字段的默认值，这里是为这个参数设定默认值。
[OUTPUT]：是用来指定该参数是既有输入又有输出值的，也就是在调用了这个存储过程时，如果所指定的参数值是我们需要输入的参数，同时也需要在结果中输出的，则该项必须为OUTPUT，而如果只是做输出参数用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT这两个语句。例子：

CREATE PROCEDURE order_tot_amt    @o_id int,    @p_tot int outputAS    SELECT @p_tot = sum(Unitprice*Quantity)    FROM orderdetails    WHERE orderid=@o_idGO

该例子是建立一个简单的存储过程order_tot_amt，这个存储过程根据用户输入的订单ID号码(@o_id)，由订单明细表 (orderdetails)中计算该订单销售总额[单价(Unitprice)*数量(Quantity)]，这一金额通过@p_tot这一参数输出给调用这一存储过程的程序。

2.2 调用存储过程

EXECUTE order_tot_amt ‘ ’; //存储过程如果有参数，后面加参数格式为：@参数名=value，也可直接为参数值value

2.3 删除存储过程

drop procedure order_tot_amt; //在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

2.4 创建存储过程的参数
（1）procedure_name：存储过程的名称，在前面加#为局部临时存储过程，加##为全局临时存储过程。

（2）number：是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。

（3）@parameter： 存储过程的参数。可以有一个或多个。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有1024个参数。

（4）data_type：参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。

（5）VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。

（6）default：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。

（7）OUTPUT：表明参数是返回参数。该选项的值可以返回给 EXECUTE。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。

（8）RECOMPILE：表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。

（9）ENCRYPTION：表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明 在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。

（10）FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。:使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

（11）AS：指定过程要执行的操作。

（12）sql_statement：过程中要包含的任意数目和类型的 Transact-SQL 语句。:

2.5 存储过程使用实例展示

针对上面的表，我使用存储过程对它做一些操作：
2.5.1 只返回单一记录集的存储过程

//1.只返回单一记录集的存储过程create Procedure GetUserAccountasselect * from UserAccountgo;//2.执行上面的存储过程exec GetUserAccoun;//结果：相当于运行 select * from UserAccount;//结果为整个表的数据

2.5.2 没有输入输出的存储过程

//1.没有输入输出的存储过程create Procedure inUserAccount as insert into UserAccount (UserName,    [PassWord],RegisterTime,RegisterIP) values(9,9,    '2013-01-02',9)go;//2.执行上面的存储过程exec inUserAccount;//结果：相当于运行 insert into UserAccount(UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9);

2.5.3 有返回值的存储过程

//1.有返回值的存储过程create Procedure inUserAccountReas insert into UserAccount (UserName,    [PassWord],RegisterTime,RegisterIP) values(10,10,return @@rowcountgo//2.执行上面的存储过程exec inUserAccountRe;//这里的@@rowcount为执行存储过程影响的行数，执行的结果是不仅插入了一条数据，还返回了一个值即 return value =1，这个可以在程序中获取.

2.5.4 有输入参数和输出参数的存储过程

//1.有输入参数和输出参数的存储过程create Procedure GetUserAccountRe     @UserName nchar(20),    @UserID int output as     if(@UserName>5)        select @UserID=COUNT(*) from UserAccount         where UserID>25    Else        set @UserID=1000go//2.执行上面的存储过程exec GetUserAccountRe '7',null;//@UserName为输入参数，@UserID为输出参数。运行结果为@userID为COOUT（*）即1。

2.5.5 同时具有返回值、输入参数、输出参数的存储过程

//1.同时具有返回值、输入参数、输出参数的存储过程create Procedure GetUserAccountRe1@UserName nchar(20),@UserID int outputasif(@UserName>5)select @UserID=COUNT(*) from UserAccount where UserID>25elseset @UserID=1000return @@rowcountgo;//2.执行上面的存储过程exec GetUserAccountRe1 '7',null//结果：@userID为COOUT（*）即 =1，Retun Value=1。

2.5.6 返回多个记录集的存储过程

//1.返回多个记录集的存储过程create Procedure GetUserAccountRe3asselect * from UserAccountselect * from UserAccount where UserID>5go;//2.执行上面的存储过程exec GetUserAccountRe3;//结果：返回两个结果集，一个为 select? * from UserAccount;另一个为 select * from UserAccount where UserID>5

3、存储过程的好处
（1）存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
（2）当对数据库进行复杂操作时(如对多个表进行CRUD时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的SQL语句，可能要多次连接数据库。而换成存储过程，只需要连接一次数据库就可以了。
（尽可能少的连接数据库，可以减少时间损耗；事务方面在批量操作中非常重要，因为事务可以回溯，当出错时，可以进行回溯，保证数据的完整性）
（3）存储过程可以重复使用,可减少数据库开发人员的工作量。

4、分页利用存储过程

//分页利用存储过程例子create procedure [dbo].[sp_super_page]    @TableName varchar(5000),//要进行分页的表，也可以用联接，如dbo.employee或dbo.employee INNER JOIN dbo.jobs ON (dbo.employee.job_id=dbo.jobs.job_id)    @Fields varchar(5000),//表中的字段，可以使用*代替    @OrderField varchar(5000),//要排序的字段    @sqlWhere varchar(5000),//WHERE子句    @pageSize int, //分页的大小    @pageIndex int,//要显示的页的索引    @TotalPage int output,//页的总数    @TotalRecords int output//信息总条数asbegin    Begin Tran    Declare @sql nvarchar(4000);    Declare @totalRecord int;//记录总数    if (@sqlWhere IS NULL or @sqlWhere = '')       //在没有WHERE子句的情况下得到表中所有的记录总数       set @sql = 'select @totalRecord = count(*) from ' + @TableName    else       //利用WHERE子句进行过滤       set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere    //执行sql语句得到记录总数    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecordOUTPUT    select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)    //根据特定的排序字段为为行分配唯一ROW_NUMBER的顺序    if (@sqlWhere IS NULL or @sqlWhere = '')       set @sql = 'select * from (select ROW_NUMBER() over(order by ' +@OrderField + ') as rowId,' + @Fields + ' from ' + @TableName    else       set @sql = 'select * from (select ROW_NUMBER() over(order by ' +@OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where '+ @SqlWhere    //确保当前页的索引在合理的范围之内    if @PageIndex<=0       Set @pageIndex = 1    if @pageIndex>@TotalPage       Set @pageIndex = @TotalPage    //得到当前页在整个结果集中准确的ROW_NUMBER值    Declare @StartRecord int    Declare @EndRecord int    set @StartRecord = (@pageIndex-1)*@PageSize + 1    set @EndRecord = @StartRecord + @pageSize - 1    //输出当前页中的数据    set @Sql = @Sql + ') as t' + ' where rowId between ' +Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)    Exec(@Sql)    If @@Error <> 0       Begin           RollBack Tran           SET @TotalRecords=-1       End    Else       Begin           Commit Tran           SET @TotalRecords=@totalRecord       End   end

5、总结
（1）当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；
（2）当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；
（3）当对比较复杂的统计和汇总也要考虑存储过程，但是过多的使用存储过程会降低系统的移植性。