存储过程

好久没用过存储过程了，今天整理一下。

一、定义：存储过程是一组为了完成特定功能的SQL语句的集合，它经编译后存储在数据库中，用户通过指定的调用方法执行之。存储过程具有名称，参数及返回值，并且可以嵌套调用。

         存储过程是经过编译的，以可执行格式永久保存在数据库的SQL程序。

二、分类：系统存储过程、扩展存储过程、用户自定义存储过程

三、优点：快速执行、安全性好、访问统一、命名代码，允许延迟绑定、减少网络通信流量

四、存储过程与函数的区别：

1.存储过程是预编译的，执行效率比函数高。
2.存储过程可以不返回任何值，也可以返回多个输出变量，但函数有且必须有一个返回值。
3.存储过程必须单独执行，而函数可以嵌入到表达式中，使用更灵活。

4.存储过程主要是对逻辑处理的应用或解决，函数主要是一种功能应用。

SQL Server
（一）使用CREATE PROCEDURE语句创建存储过程

CREATE PROC[EDURE] procedure_name

[ {@paramenter data_type} [=default]

   [OUT|OUTPUT] [READONLY] [,...n] ]

[WITH[ENCRYPTION [,...n] ]

AS {<spl_statement> [ ; ] [...n] } [ ; ]

procedure_name：存储过程的名称
@ parameter：存储过程中的参数
data_type：参数的数据类型
Default：参数的默认值
OUTPUT：指示该参数是输出参数    
READONLY：指示该参数是只读的
ENCRYPTION：指示加密存储
sql_statement:包含在过程中的一个或多个 T-SQL 语句

通过检索数据库的系统表sysobjects以及syscomments，查看存储过程的代码
SELECT text FROM syscomments where id IN
    (SELECT id FROM sysobjects
     where name =’usp_Lend_Info’)
使用系统存储过程sp_helptext 来显示代码
sp_helptext usp_Lend_Info

1)如果在存储过程定义中使用了ENCRYPTION指示符则无法显示代码。

2)通过检索数据库的系统表sysobjects以及syscomments，查看存储过程的代码
SELECT text FROM syscomments where id IN
    (SELECT id FROM sysobjects
     where name =’usp_Lend_Info’)
使用系统存储过程sp_helptext 来显示代码
sp_helptext usp_Lend_Info

3)如果在存储过程定义中使用了ENCRYPTION指示符则无法显示代码。

4)使用 sysobjects 表查询法
IF NOT EXISTS (SELECT name FROM sysobjects
WHERE name ='procname' AND type='P')
CREATE PROCEDURE procname…

5）使用OBJECT_ID函数
IF OBJECT_ID('storename','P') IS NULL
CREATE PROCEDURE storename…

（二）用T-SQL的 EXECUTE 语句执行存储过程。
[EXEC[UTE]][@return_status=] procedure_name
[[@parameter=]{value|@variable[OUT[PUT]] [,…n ]]
[WITH RECOMPILE][;]
@return_status：保存存储过程的返回状态。
procedure_name：是要调用的存储过程名称。
value：传递给存储过程的参数值。可以按名称调用，也可以按在模块中定义的顺序提供。
@variable：是用来存储输入参数或输出参数的变量。
OUTPUT：指定存储过程将值送入输出参数。
WITH RECOMPILE：执行该存储过程时强制重新编译。
参数及保留字含义与CREATE PROCEDURE相同

（三）删除

使用DROP PROCEDURE 语句删除存储过程
DROP PROC[EDURE]  procedure_name

常用OBJECT_ID 函数检测存储过程存在后删除
IF OBJECT_ID('proceduere_name','P') IS NOT NULL
DROP PROCEDURE proceduere_name

（四）参数

1.输入参数：通过输入参数，调用程序可以将数据传送到存储过程中供存储过程使用，输入参数需要定义变量名及变量类型也可以根据需要设定其默认值，输入参数既可以将它们的值设置为常量，也可以使用变量的值。
2.输出参数：允许存储过程将数据或者游标变量传回给调用程序，输出参数使用OUTPUT关键字声明。
3.参数传递
（1）按参数位置传递
（2）按参数名字传递
* 参数执行可以由位置标识，也可以由名字标识，如果以位置标识，执行时按照参数的顺序依次填入；如果以名字传递参数，则参数的顺序是任意的。

4.使用常量调用
EXEC usp_Query_LendHistByPatronID 'T0101'或
EXEC usp_Query_LendHistByPatronID @PatronID ='T0101'
5.使用变量调用
--声明变量类型
DECLARE  @InputPatronID  VARCHAR（20）
--给变量赋值
SELECT @InputPatronID ='T0101 '
--执行
EXEC usp_Query_LendHistByPatronID @InputPatronID

例：如查询作者为姓周的图书信息，可以通过下列方法调用，未赋值的参数会启用默认值。
（1）按参数位置传递
EXEC usp_Query_BookInfo '','','周'
（2）按参数名字传递
EXEC usp_Query_BookInfo @Author='周'
按名字传递参数比按位置具有更大的灵活性，但是按位置传递参数速度更快。
例子：创建存储过程，通过输入读者证号，输出该读者的姓名，读者部门及读者类别。
CREATE PROCEDURE usp_Get_Patron_Info
  @PatronID VARCHAR(20),
  @Name VARCHAR(30) OUTPUT,
  @Department VARCHAR(40) OUTPUT,
  @Type VARCHAR(20) OUTPUT
AS
SELECT @Name=Name,@Department=department,@Type =Type
FROM Patron WHERE PatronID=@PatronID

调用该存储过程，查询读者证号为“T0101”读者的相关信息。
DECLARE @Name VARCHAR(30)
DECLARE @Department VARCHAR(40)
DECLARE @Type VARCHAR(20)
EXECUTE usp_Get_Patron_Info 'T0101',@Name OUTPUT,
                 @Department OUTPUT,@Type OUTPUT
SELECT  @Name,@Department,@Type --显示执行结果