Sql Server的存储过程详解

1 存储过程概述

1.1 存储过程的概念

存储过程（Stored Procedure）是预编译SQL语句集合，这些语句存储在一个名称（存储过程的名称）下并作为单元来处理。存储过程代替了传统的逐条执行SQL语句的方式，一个存储过程中可以包含查询、插入、删除、更新等操纵的一系列SQL语句，当这个存储过程被调用执行时，这些操作也会同时执行。
存储过程与其他编程语言中的过程类似，它可以接受输入参数，并以输出参数的格式向调用过程或批处理返回多个值；包含用于在数据库中执行操作（包括调用其他过程）的变成语句；向调用过程或批处理返回状态值，以指明成功或者失败（以及失败的原因）。
SQL Server提供了三种类型的存储过程
1.系统存储过程：用来管理SQL Server与显示有关数据库和用户的信息的存储过程。
2.自定义存储过程：用户在SQL Server中通过采用SQL语句创建存储过程。
3.扩展存储过程：通过编程语言（例如C）创建外部例程，并将这个例程在SQL Server中作为存储过程使用。

1.2 存储过程的优点

存储过程的有点表现在以下几个方面：
（1）存储过程可以嵌套使用，支持代码重用；
（2）存储过程可以接受与使用参数动态执行其中的SQL语句；
（3）存储过程比一般的SQL语句执行速度快。存储过程在创建时就已经被编译，每次执行时，不需要重新编译。而SQL语句每次执行都需要编译；
（4）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加发到他们的证书。用户可以被授权来执行存储过程而不必直接对存储过程中引用的对象具有权限（有点像JAVA面向对象里面的封装）。
（5）存储过程允许模块化程序设计。存储过程一旦创建，以后即可在程序中调用任意多次。这可以改进应用程序的可维护性（有点像JAVA中的分层架构的思想），并允许应用程序同意访问数据库。
（6）存储过程可以减少网络通讯流量。一个需要数百行SQL语句代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。
（7）存储过程可以强制应用程序的安全性。参数化存储过程有助于保护应用程序不受SQL Injection攻击。
说明：SQL Injection是一种攻击方式，它可以将恶意的代码插入到以后将传递给SQL Server供分析和执行的字符串中。任何构成SQL语句的过程都应该进行注入漏洞检查，因为SQL Server将执行器接收到的所有语法有效的查询。

2 创建存储过程

2.1 使用可视化工具创建存储过程

每个可视化工具都可能有一定的差异，所以用什么工具创建存储过程，去百度找一下资源应该很多这里不做演示了

2.2 使用create proc语句创建存储过程

语法如下

create proc Procedure Proc_Student
  @Proc_Son int 
as 
select * from Student where Sno = @Proc_Son

3 管理存储过程

3.1 执行存储过程

存储过程创建完成之后，可以通过EXECUTE命令执行，可简写为EXEC。

1.EXECUTE

EXECUTEyonglai zhixing Transact-SQL 中命令字符串、字符串、或者执行下列模块之一：系统存储过程、用户自定义存储过程、标量值用户定义函数或者扩展存储过程。

2.使用EXECUTE执行存储过程

例如执行存储过程Proc_Stu

exec Proc_Stu

3.2 查看存储过程

1.使用 sys.sql_modules 查看存储过程定义；

select * from sys.sql_modules

2.使用 OBJECT_DEFINITION 查看存储过程定义

OBJECT_DEFINITION(object_id)

3.使用 sp_helptext 查看存储过程的定义
显示用户定义规则的定义、默认值、未加密的Transact-SQL 存储过程、用户定义Transact-SQL函数、触发器、计算列、CHECK约束、视图或系统对象（如系统存储过程）。语句如下：

sp_helptext 'Proc_Stu'

3.3 修改存储过程

修改存储过程可以改变存储过程当中的参数或者语句，可以通过SQL语句中的 ALTER PROCEDURE 语句实现。虽然删除并重新创建该存储过程，也可以达到修改存储过程的目标，但是将丢失与该存储过程关联的所有权限。

1.ALTER PROCEDURE 语句

ALTER PROCEDURE 语句用来修改通过执行 CREATE PROCEDUR语句创建过程。该语句修改存储过程时，不会更改权限，也不影响相关的存储过程或触发器。
例：修改名为“Proc_Stu”的存储过程。

ALTER PROCEDURE [dbo].[Proc_Stu]
@Son varchar(10)
as
select * from student

3.4 重命名存储过程

sp_rename 'Proc_Stu','Proc_StuInfo'

注意：更改对象名的任一部分都可能破坏脚本和存储过程。建议不要使用此语句来重命名存储过程、触发器、用户自定义函数或视图；而是删除该对象，然后使用新名称重新创建该对象。

3.5 删除存储过程

DROP PROCENDURE Proc_Student