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