SQL Server存储过程

使用SQL Server创建应用程序时，T-SQL语言是应用程序和SQL Server数据库之间的主要编程接口。

使用T-SQL程序时，可以将程序存储在本地，然后创建向SQL Server发送命令并处理结果的应用程序。

也可以将T-SQL程序作为存储过程存储在SQL Server中，创建执行存储过程并处理结果的应用程序。

1. 存储过程概述

存储过程是T-SQL语句的预编译集合，这些语句在一个名称下存储并作为一个单元进行处理，经编译后存储在数据库中。

用户通过指定存储过程的名字并给出参数(如果带有参数)来执行存储过程。

存储过程由参数、编程语句和返回值组成。

可以通过输入参数向存储过程中传递参数值，也可以通过输出参数向调用者传递多个输出值。

存储过程中的编程语句可以是T-SQL的控制语句、表达式、访问数据库的语句，也可以调用其他的存储过程。

存储过程只能有一个返回值，通常用于表示调用存储过程的结果是成功还是失败。

利用SQL Sevrer创建一个应用程序时，使用T-SQL进行编程有如下两种方法。

一是在本地存储T-SQL语句，并创建应用程序向SQL Server发送命令来对结果进行处理；

二是可以把部分使用T-SQL编写的程序作为存储过程存储在SQL Server中，然后创建应用程序来调用存储过程，对数据结果进行处理。

SQL Server推荐使用第二种方法，原因在于存储过程具有以下优点。

执行速度快、效率高：因为SQL Server会事先将存储过程编译成二进制可执行代码。在运行存储过程时不需要再对存储过程进行编译，从而加快了执行的速度。
模块化编程：存储过程在创建完毕之后，可以在程序中被多次调用，而不必重新编写该T-SQL语句。也可以对其进行修改，而且修改之后，所有调用的结果都会改变，提高了程序的可移植性。
减少网络流量：由于存储过程是保存在数据库服务器上的一组T-SQL代码，在客户端调用时，只需要使用存储过程名及参数即可，从而减少网络流量。
安全性高：存储过程可以作为一种安全机制来使用，当用户要访问一个或多个数据表，但没有存取权限时，可以设计一个存储过程来存取这些数据表中的数据。而当一个数据表没有设置权限，而对该数据表的操作又需要进行权限控制时，也可以使用存储过程来作为一个存取通道，对不同权限的用户使用不同的存储过程。同时，参数化存储过程有助于保护应用程序不受SQL Injection攻击。

2. 存储过程的类型

在SQL Server中，存储过程分为5类：

系统存储过程
用户自定义存储过程
临时存储过程
远程存储过程
扩展存储过程

2.1 系统存储过程

SQL Server中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程。

系统存储过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为数据库系统管理员SQL Server提供支持。

通过系统存储过程，SQL Server的许多管理性或信息性的活动都可以被顺利、有效地完成。

从物理意义来讲，系统存储过程存储在源数据库中，并且带有sp_前缀；

从逻辑意义上讲，系统存储过程出现在每个系统定义数据库和用于定义数据库的sys架构中。

2.2 用户自定义存储过程

用户自定义存储过程是由用户创建并能完成某一特定功能的存储过程，是封装了可重用代码的T-SQL语句模块。

存储过程可以接收输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言和数据操作语言语句，以及返回输出参数。

在SQL Server中，用户自定义的存储过程有两种类型：T-SQL存储过程或CLR存储过程。

2.3 临时存储过程

临时存储过程又分为局部临时存储过程和全局临时存储过程。

局部临时存储过程的名称以#开头，存放在tempdb数据库中，只允许创建并连接的用户使用，当该用户断开连接时将自动删除局部临时存储过程。

全局临时存储过程名称以##开头，也存放在tempdb数据库中，允许所有连接的用户使用，在所有用户断开连接时自动被删除。

2.4 远程存储过程

远程存储过程是位于远程服务器上的存储过程。

2.5 扩展存储过程

扩展存储过程允许使用高级编程语言创建应用程序的外部例程，从而使SQL Server的实例可以动态地加载和运行DLL。

扩展存储过程直接在SQL Server实例的地址空间中运行。

3. 创建存储过程

SQL Server中创建存储过程主要有两种方式：

一种是使用图形化界面来创建
另一种是使用T-SQL语句来创建

使用图形化界面创建存储过程

在SSMS中打开要创建存储过程的数据库，展开”可编程性”选项，在”存储过程”选项上右击，在弹出的快捷菜单中选择”新建”->”存储过程”命令，出现创建存储过程的T-SQL命令，编辑相关的命令即可，完成后，单击”运行”按钮，从而创建一个存储过程。

使用T-SQL语句创建存储过程

SQL Server使用create procedure语句创建存储过程的语法格式如下：

SQL

CREATE {PROC|PROCEDURE} PROCEDURE_NAME[;NUMBER]
[{@PARAMETER DATA_TYPE}
[VARYING][=DEFAULT][OUTPUT][,...N]
[WITH{RECOMPILE|ENCRYPTION|RECOMPOLE,ENCRYPTION}]
[FOR REPLICATION]
AS {SQL_STATEMENT[,..N]}

其中，各参数说明如下：

procedure_name：新存储过程的名称。必须遵循标识符命名规则，不建议在过程名称中使用前缀sp_，sp_表示的是系统存储过程。
number：是可选整数，用于对同名的过程分组。使用一个drop Procedure语句可将这些分组过程一起删除。
@parameter：是一个形参变量，是存储过程中的参数。在create procedure过程中可以声明一个或多个参数。必须在执行过程时提供每个所声明参数的值。存储过程最多可以有2100个参数。
data_type：是参数的数据类型。所有数据类型均可以用作存储过程的参数。
varying：指定作为输出参数支持的结果集。
default：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。
output：表明参数是返回参数。该选项的值可以返回给调用语句。
{recompile|encrtption|recompile，encryption}：recompile表名SQL Server不会缓存该过程被引用的对象，该过程将在运行时重新编译；encryption表示SQL Server加密用create procedure语句创建存储过程的定义，使用encryption可防止将过程作为SQL Server复制的一部分发布。
for replication：指定不能在订阅服务器上执行为复制创建的存储过程。
as：指定过程要执行的操作
sql_statement：过程要包含的T-SQL语句

举例：创建存储过程p1，查询每个学生的数学平均成绩。

SQL

USE ichi
GO
CREATE PROCEDURE P1
AS
SELECT ID,AVG(MathScore) FROM STUDENT GROUP BY ID

接下来，创建带参数的存储过程p2，根据给定姓名查询某个学生的基本信息。

SQL

USE ichi
GO
CREATE PROCEDURE P2 @name nvarchar(20)
AS
SELECT * FROM STUDENT WHERE Name=@name

4. 执行存储过程

执行存储过程即调用存储过程，T-SQL语句提供了execute语句来执行存储过程。

语法格式如下：

SQL

[EXEC[UTE]]{[@RETURN_STATUS=]PROCEDURE_NAME[;NUMBER]
{[[@PARAMETER=]VALUE|@VARIABLE[OUTPUT]|[DEFAULT]}][,...N]}
[WHIT RECOMPILE]}

各参数说明如下：

@RETURN_STATUS：是一个可选的整型变量，用于保存存储过程的返回状态。这个变量在用于EXECUTE语句时，必须已在批处理、存储过程或函数中声明。
PROCEDURE_NAME：存储过程名称。
NUMBER：是可选整数，用于对同名的过程分组。
@PARAMETER：是在创建存储过程时定义的参数。当使用该选项时，各参数的枚举顺序可以与创建存储过程时的定义顺序不一致，否则两者顺序必须一致。
VALUE：是存储过程中输入参数的值。如果参数名称没有指定，参数值必须按创建存储过程时的定义顺序给出。如果在创建存储过程时指定了参数的默认值，执行时可以不再指定。
@VARIABLE：用来存储参数或返回参数的变量。当存储过程中有输出参数时，只能用变量来接收输出参数的值，并在变量后加上output关键字。
OUTPUT：用来指定参数是输出参数。该关键字必须与@VARIABLE连用，表述输出参数的值由变量接收。
DEFAULT：表示参数使用定义时指定的默认值。
WITH RECOMPILE：表示执行存储过程时强制重新编译。

例子1：执行存储过程P1

SQL

USE ichi

GO

EXECUTE P1

运行结果如下图：

例子2：执行存储过程P2，查询ICHI的信息

SQL

USE ichi

GO

EXECUTE P2 'ICHI'

运行结果如图：

5. 查看存储过程

存储过程被创建之后，它的名字被存储在系统表sysobjects中，它的源代码被存储在系统表syscomments中。

用户可以使用系统存储过程来查看用户创建的存储过程的相关信息。

① sp_help用于显示存储过程的信息，如存储过程的参数、创建日期等。

语法格式如下：

SQL

EXEC[UTE]SP_HELP存储过程名

例子1：查看存储过程P2的相关信息。

SQL

USE ichi

GO

EXECUTE SP_HELP P2

运行结果如下图所示：

② SP_HELPTEXT用来查看存储过程的源代码。

语法格式如下：

SQL

EXEC[UTE] SP_HELPTEXT 存储过程名

例子2：查看存储过程P2的源代码

SQL

USE ichi

GO

EXECUTE SP_HELPTEXT P2

运行结果如图所示：

6. 修改和删除存储过程

可以通过T-SQL语句对存储过程进行修改和删除。

6.1 修改存储过程

用ALTER PROCEDURE语句修改存储过程的语法格式如下：

SQL

ALTER {PROC|PROCEDURE} PROCEDURE_NAME[;NUMBER]
[{@PARAMETER DATA_TYPE}
[VARYING][=DEFAULT][OUTPUT][,...N]
[WITH{RECOMPILE|ENCRYPTION|RECOMPOLE,ENCRYPTION}]
[FOR REPLICATION]
AS {SQL_STATEMENT[,..N]}

除了ALTER PROCEDURE之外，其他代码与创建存储过程的代码相同。

其中，各参数的含义与创建存储过程语句中对应参数的含义相同。

例子1：修改存储过程P2，根据给定学号查询某个学生的基本信息。

SQL

USE ichi

GO

ALTER PROCEDURE P2 @ID CHAR(11)

AS

SELECT * FROM STUDENT WHERE Id = @ID

单击”执行”按钮，显示命令已成功完成。

6.2 删除存储过程

不再需要的存储过程，可以删除。

DROP PROCEDURE语句用于从当前数据库中删除一个或多个存储过程。

语法格式如下：

SQL

DROP {PROC|PROCEDURE} {PROCEDURE} [,..N]

其中 PROCEDUCE为要删除的存储过程或存储过程组的名称。

例子1：删除存储过程P1

SQL

USE ichi

GO

DROP PROC P1

单击”执行”按钮，显示命令已成功完成。

浏览量 3

1. 存储过程概述

2. 存储过程的类型

2.1 系统存储过程

2.2 用户自定义存储过程

2.3 临时存储过程

2.4 远程存储过程

2.5 扩展存储过程

3. 创建存储过程

使用图形化界面创建存储过程

使用T-SQL语句创建存储过程

4. 执行存储过程

5. 查看存储过程

6. 修改和删除存储过程

6.1 修改存储过程

6.2 删除存储过程

相关文章：