存储过程概述

存储过程(stored procedure)是存放在服务器上的预先编译好的SQL语句,用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。

1. 存储过程的分类

•系统存储过程：存储在master数据库中，由前缀sp标识，可以从系统表中获取信息，允许系统管理员在没有直接更新底层表的许可权下执行更新数据库中系统表的数据库管理工作。绝大部分的系统存储过程可以在任何数据库中执行；

•本地存储过程：这是用户在独立的用户数据库中为了完成某一特定功能而编写的存储过程；

•临时存储过程：它与临时表类似，通常又分为本地和全局临时存储过程两种，当临时存储过程为本地时，其名字以符号#开始，为全局时，以符号##开始；

• 远程存储过程：远程存储过程在分布式查询中使用；

•扩展存储过程：使SQLServer可动态装载并执行DLL（动态链接库文件）。这样用户可使用象C这样的编程语言创建自己的外部例程，扩展存储过程由前缀xp标识。

2.存储过程的优点

•提供了安全机制：

存储过程提供的安全机制可以让用户通过存储过程操作数据库中的数据，而不让用户直接操作于存储过程相关的表，从而保证数据库中数据的安全性；

•改进了执行性能：

存储过程在第二次执行时，无需预编译，从而改进系统的执行性能；

•减少了网络流量：

存储过程是存放在服务器上的预先编译好的单条或多条SQL语句并在服务器上运行，用户无需在网络上发送上百个SQL语句，或是将众多数据从服务器下载至客户端后再进行处理，从而大大减少了网络负载；

•允许模块化程序设计：

增强代码的可重用性，提高开发效率。存储过程可以视为为完成某特定功能而编写的功能模块，将来可以在其他的存储过程中引用该存储过程，从而实现代码的重用性，加快应用的开发速度，提高开发的质量和效率。

创建存储过程

1.创建简单的存储过程

    CREATE PROC[EDURE] 

    [owner.] procedure_name

    [;number]

    [{@parameter data_type}

    [VARYING][=default]

    [OUTPUT]][，…n]

    [WITH {RECOMPILE|ENCRYPTION|

    RECOMPILE,ENCRYPTION}]

    [FOR  REPLICATION]

    AS  sql_statements

•parameter：表示存储过程中包含的输入/输出参数

•RECOMPILE：表示重新编译

•ENCRYPTION：表示加密

•sql_statements：表示在存储过程中需要执行的操作

创建存储过程的步骤及注意事项：

•不能将CREATE PROCEDURE语句与其它SQL语句组合到单个批处理中；

•创建存储过程是有权限的，其默认权限为dbo，其他用户若要获得创建存储过程的权限，要由dbo授权；

•存储过程是数据库对象，在命名用户自定义的存储过程时应避免使用sp前缀，以免和系统存储过程混淆；

•尽量不要使用临时存储过程，以避免tempdb上造成的对系统表资源的争夺，从而影响系统的执行性能；

•存储过程最大尺寸被限制为128MB，存储过程最多允许嵌套至32级。

2. 查看存储过程信息

可以使用如下命令完成对存储过程信息的查看：

    sysobjects

    syscomments

    sysdepends

    sp_stored_procedures

3.创建和执行含参数存储过程信息

通过使用参数，可以多次使用同一存储过程并按指定要求查找数据库。

（1）创建带输入参数的存储过程：

输入参数是指由调用程序向存储过程传递的参数，为了定义接受输入参数的存储过程，需要在CREATE PROCEDURE语句中声明一个或多个变量作为参数。

如：

    @parameter_name datatype[=default]

（2）执行带输入参数的存储过程：

•使用参数名传送参数值：由如下语句给出传递值@parameter_name=value

•按位置传送参数值：不参照被传递的参数而直接给出参数的传递值

（3）创建带输出参数的存储过程：

在创建存储过程的语句中定义输出参数可以实现从存储过程中返回值为了使用输出参数。

在CREATE  PROCEDURE中指定OUTPUT

    @parameter_named atatype[=default]OUTPUT

（4）执行带输出参数的存储过程：

为了接收存储过程的返回，必须声明作为输出的传递参数，在EXECUTE语句中指定OUTPUT

（5）存储过程的重编译处理：SQL Server在创建存储过程时，需进行语法检查，若存在语法错误，将返回错误，并不创建该存储过程；若语法正确，则存储过程的文本将存储在syscomments系统表中。重编译处理共包含三种方法：

•建立存储过程时设定重编译选项

    CREATE PROCEDURE [WITH RECOMPILE]

•在执行存储过程时设定重编译选项

    EXECUTE PROCEDURE_NAME 

    [PARAMETER] [WITH RECOMPILE] 

•通过系统存储过程设定重编译选项

    EXEC sp_recompile OBJECT 

修改和删除存储过程

1. 修改存储过程

修改存储过程的语句如下：    

    ALTER  PROC 

    procedure_name[;number]

    [{@parameterdata_type}

    [VARYING][=default]

    [OUTPUT]][,…n]

    [WITH {RECOMPILE|ENCRYPTION|

    RECOMPILE,ENCRYTION}]

    [FOR REPLICATION]

    AS

    Sql_statement[,..n]

2. 删除存储过程

删除存储过程的语句如下

    DROP PROC procedure_name