正文
Sql Server系列：存储过程
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
1 存储过程简介
存储过程是使用T-SQL代码编写的代码段。在存储过程中，可以声明变量、执行条件判断语句等其他编程功能。在MS SQL Server 2012中存储过程主要分三类：系统存储过程、自定义存储过程和扩展存储过程。
存储过程的优点：
◊ 存储过程加快系统允许速度，存储过程只在创建时编译，以后每次执行时不需要重新编译。
◊ 存储过程可以封装复杂的数据库操作，简化操作流程。
◊ 可实现模块化的程序设计，存储过程可以多次调用，提供统一的数据库访问接口，改进应用程序的可维护性。
◊ 存储过程可以增强代码的安全性。
◊ 存储过程可以降低网络流量，存储过程代码直接存储在数据库中，在客户端与服务器的通讯过程中，不会产生大量的T-SQL代码流量。
存储过程的缺点：
◊ 数据库移植不方便，存储过程依赖于数据库管理系统，MS SQL Server 2012存储过程中封装的操作代码不能直接移植到其他数据库系统中。
◊ 不支持面向对象的设计，无法采用面向对象的方式将逻辑业务进行封装。
◊ 不易维护
◊ 不支持集群
1.1 系统存储过程
系统存储过程是有MS SQL Server 2012系统自身提供的存储过程，可以作为命令执行各种操作。系统存储过程主要用来从系统表中获取信息，使用系统存储过程完成数据库服务器的管理工作。系统存储过程位于数据库服务器中，并以sp_开头，系统存储过程定义在系统定义和用户定义的数据库中，在调用时不必在存储过程前加数据库限定名。
系统存储过程创建并存储于系统数据库master中。
1.2 自定义存储过程
自定义存储过程即用户使用T-SQL语句编写的、为了实现某一特定业务需求，在用户数据库中编写的T-SQL语句集合，用户存储过程可以接受输入参数、向客户端返回结果和信息、返回输出参数等。
创建自定义存储过程时，存储过程名前面加上##表示创建一个全局的临时存储过程；存储过程名前面加上#表示创建局部临时存储过程。局部临时存储过程只能在创建它的会话中使用，会话结束时，将被删除。这两种存储过程都存储在tempdb数据库中。
1.3 扩展存储过程
扩展存储过程是以在SQL Server 2012环境外执行的DLL来实现的。扩展存储过程以前缀xp_标识。
2 创建及执行存储过程
CREATE PROCEDURE语句的语法格式：
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
EXECUTE存储过程的语法格式：
[ { EXEC | EXECUTE } ]
{
[ @return_status = ]
{ module_name [ ;number ] | @module_name_var }
[ [ @parameter = ] { value
| @variable [ OUTPUT ]
| [ DEFAULT ]
}
]
[ ,...n ]
[ WITH <execute_option> [ ,...n ] ]
}
[;]
示例：
CREATE PROCEDURE USP_GetAllProducts
AS
SELECT [ProductID],[ProductName],[UnitPrice],[UnitsInStock],[CreateDate]
FROM [dbo].[Product]
EXECUTE USP_GetAllProducts
带输入参数的存储过程：
CREATE PROCEDURE USP_GetByProductID
(
@ProductID INT
)
AS
SELECT [ProductID],[ProductName],[UnitPrice],[UnitsInStock],[CreateDate]
FROM [dbo].[Product]
WHERE [ProductID] = @ProductID
EXECUTE USP_GetByProductID @ProductID = 1
带输出参数的存储过程：
CREATE PROCEDURE USP_GetTotalRecordsByCategoryID
(
@CategoryID INT,
@TotalRecords INT OUTPUT
)
AS
SELECT @TotalRecords = COUNT(1) FROM [dbo].[Product]
WHERE [CategoryID] = @CategoryID
DECLARE @TotalProducts INT
EXECUTE USP_GetTotalRecordsByCategoryID @CategoryID = 1, @TotalRecords = @TotalProducts OUTPUT
SELECT @TotalProducts
DECLARE @TotalProducts INT
EXECUTE USP_GetTotalRecordsByCategoryID 1, @TotalProducts OUTPUT
SELECT @TotalProducts
3 修改存储过程
修改存储过程语法格式：
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
4 查看存储过程
查看存储过程结构：
EXEC sp_help USP_GetTotalRecordsByCategoryID
查看存储过程文本：
EXEC sp_helptext USP_GetTotalRecordsByCategoryID
5 删除存储过程
删除存储过程语法：
DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ]
示例：
DROP PROCEDURE USP_GetTotalRecordsByCategoryID
6 使用WITH RECOMPILE选项
使用WITH RECOMPILE选项可以确保为特定的某次运行创建新的计划，使用该选项的两种方式：
◊ 在运行时包含WITH RECOMPILE
EXECUTE USP_GetAllProducts WITH RECOMPILE
◊ 在存储过程中包含WITH RECOMPILE选项。
在CREATE PROC或ALTER PROC语句中的AS语句前添加WITH RECOMPILE选项。
7 递归存储过程
SQL Server最多可以进行32次递归，超出则会引发错误并停止处理。
CREATE PROC USP_Factorial
(
@ValueIn INT,
@ValueOut INT OUTPUT
)
AS
DECLARE @InWorking INT
DECLARE @OutWorking INT IF @ValueIn >= 1
BEGIN
SELECT @InWorking = @ValueIn - 1
EXEC USP_Factorial @InWorking, @OutWorking OUTPUT
SELECT @ValueOut = @ValueIn * @OutWorking
END
ELSE
SET @ValueOut = 1
DECLARE @ValueOut INT
EXEC USP_Factorial 5, @ValueOut OUTPUT
SELECT @ValueOut
