首页 > 代码库 > Sql Server系列：存储过程
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_GetAllProductsAS 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 INTEXECUTE USP_GetTotalRecordsByCategoryID @CategoryID = 1, @TotalRecords = @TotalProducts OUTPUTSELECT @TotalProducts
DECLARE @TotalProducts INTEXECUTE USP_GetTotalRecordsByCategoryID 1, @TotalProducts OUTPUTSELECT @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_GetTotalRecordsByCategoryID5. 删除存储过程
删除存储过程语法：
DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ]
示例：
DROP PROCEDURE USP_GetTotalRecordsByCategoryID
Sql Server系列：存储过程