SQL Server(七)-存储过程

--《21天 学通SQL Server》

1 存储过程简介
存储过程的运用情况比较广，可以包含几乎所有的Transact-SQL语句，比如数据存取语句、流程控制语句、错误处理语句等，使用起来非常有弹性。
1.1 什么是存储过程
首先，存储过程（Stored Procedure）是使用Transact-SQL语言编写的一段能实现指定功能的程序。其次，这种程序被SQL Server编译好后，存放在SQL Server数据库中。用户可以通过存储过程的名称和参数传递调用这些具有指定功能的存储过程。存储过程也是数据库对象。人们通常使用存储过程提高数据库的安全性和减少网络通信数据量。
1.2 存储过程的优点
● 执行速度快效率高：因为SQL Server 2008会事先将存储过程编译成二进制可执行代码，所以在运行存储过程时，SQL Server 2008不需要再对存储过程进行编译，可以加快执行的速度。
● 模块式编程：存储过程在创建完毕之后，可以在程序中多次被调用，而不必重新编写该T-SQL语句。在存储过程创建之后，也可以对存储过程进行修改，而且一次修改之后，所有调用该存储过程的程序所得到的结果都会被修改，提高了程序的可移植性。
● 减少网络流量：由于存储过程是存在数据库服务器上的一组Transact-SQL，在客户端调用时，只需要使用一个存储过程名及参数即可，在网络上传送的流量比传送这一组完整的Transact-SQL程序小得多，所以可以减少网络流量，提高运行速度。
● 安全性：存储过程可以作为一种安全机制来使用，当用户要访问一个或多个数据表，但没有存取权限时，可以设计一个存储过程来存取这些数据表中的数据。而当一个数据表没有设权限，而对该数据表操作又需要进行权限控制时，也可以使用存储过程来作为一个存取通道，对不同权限的用户使用不同的存储过程。

1.3 存储过程的种类
在SQL Server 2008中，存储过程可以分为三大类。
● 系统存储过程（System Stored Procedures）：系统存储过程一般是以“sp_”为前缀的，是由SQL Server 2008自己创建、管理和使用的一种特殊的存储过程，不要对其进行修改或删除。从物理意义上来说，系统存储过程存储在Resource数据库中，但从逻辑意义上来说，系统存储过程出现在系统数据库和用户定义数据库的sys架构中。
● 扩展存储过程（Extended Stored Procedures）：扩展存储过程通常以“xp_”为前缀。扩展存储过程允许使用其他编辑语言（如C#等）创建自己的外部存储过程，其内容并不存在SQL Server 2008中，而是以DLL形式单独存在。不过该功能在以后的SQL Server版本中可能会被废除，所以尽量不要使用。
● 用户自定义存储过程（User-defined Stored Procedures）：由用户自行创建的存储过程，可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出参数。在SQL Server 2008中，用户自定义存储过程又分为Transact-SQL存储过程和CLR存储过程两种：Transact-SQL存储过程，保存Transact-SQL语句的集合，可以接受和返回用户提供的参数；CLR存储过程，该存储过程是针对微软的.NET Framework公共语言运行时（CLR）方法的引用，可以接受和返回用户提供的参数。CLR存储过程在.NET Framework程序中是作为公共静态方法实现的。
2 创建和使用存储过程
在SQL Server 2008中，可以用SQL Server Management Studio和Transact-SQL语言来创建存储过程，在创建存储过程时，要确定存储过程的三个组成部分。
● 输入参数和输出参数。
● 在存储过程中执行的Transact-SQL语句。
● 返回的状态值，指明执行存储过程是成功还是失败。
2.1 使用CREATE PROCEDURE语句创建存储过程

 CREATE { PROC｜PROCEDURE }
     [schema_name.] procedure_name [ ; number ]          --架构名。存储过程名[;分组]
     [ { @parameter [ type_schema_name. ] data_type }     --参数
       [ VARYING ] [ = default ] [ [ OUT [ PUT ]         --作为游标输出参数
     ] [ ,...n ]
 [ WITH <procedure_option> [ ,...n ]
 [ FOR REPLICATION ]                    --不能在订阅服务器上执行为复制创建的存储过程
 AS { <sql_statement> [;][ ...n ]       --存储过程语句
｜<method_specifier> }
 [;]
 <procedure_option> ::=
     [ ENCRYPTION ]                      --加密
     [ RECOMPILE ]                       --不预编译
     [ EXECUTE_AS_Clause ]      --执行存储过程的安全上下文
 <sql_statement> ::=
 { [ BEGIN ] statements [ END ] }                        --存储过程语句
 <method_specifier> ::=
 EXTERNAL NAME assembly_name.class_name.method_name      --指定程序集方法

其参数解释如下。
● schema_name：架构名。
● procedure_name：存储过程名。
● number：对同名过程进行分组的选项，使用drop procedure语句可以将这些分组过程一起删除。
● @parameter：存储过程的参数。
● [ type_schema_name. ] data_type：参数的架构及类型。
● VARYING：指定作为输出参数支持的结果集，仅适用于cursor参数。
● default：参数的默认值，如果定义了default值，则无须指定此参数的值也可执行存储过程。
● OUTPUT：输出参数，此选项的值可以返回给调用存储过程的语句。
● ENCRYPTION：加密存储过程。
● RECOMPILE：指明该存储过程在运行时才编译，不预编译。
● EXECUTE_AS_Clause：指定执行存储过程的安全上下文。
● FOR REPLICATION：不能在订阅服务器上执行为复制创建的存储过程。
● <sql_statement>语法块：存储过程执行的T-SQL语句。
● <method_specifier>语法块：指定.NET Framework程序集的方法，以便CLR存储过程引用。
【例1】首先，在test数据库中创建一个名为procGetStudent的存储过程，用于查询stu_info表中的所有记录。
 

/* 创建存储过程procGetStudent */
CREATE PROC procGetStudent
AS
SELECT  *
FROM  stu_info
GO
/* 调用存储过程procGetStudent */
EXEC procGetStudent

2.2 使用EXECUTE语句调用存储过程

 [ [ EXEC [ UTE ] ]
{
[ @return_status = ]
{ procedure_name [ ;number ]｜@procedure_name_var
}
[ [ @parameter = ] { value｜@variable [ OUTPUT ]｜[ DEFAULT ] }
[ ,...n ]
[ WITH RECOMPILE ]

【语法说明】
● @return_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在用于EXECUTE语句前，必须在批处理、存储过程或函数中声明过。
● @procedure_name_var：是局部定义变量名，代表存储过程名称。
2.3 创建带输入参数的存储过程
参数的作用是在存