第7章 存储过程、触发器和用户自定义函数

第 7 章存储过程、触发器和用户自定义函数（6课时）

主要内容：

1 存储过程（概述、创建与执行、修改与删除）

2 触发器（概述、DML触发器、DDL触发器）

3 用户自定义函数（概述、标量函数的建立与调用、内嵌表值函数的建立与调用、多语名表值函数的建立与调用）

存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。触发器是一种特殊类型的存储过程，可以实现自动化的操作。用户定义函数是由用户根据应用程序的需要而定义的可以完成特定操作的函数。

这三种数据库对象都可以通过两种方法来定义：

SQL Server Management Studio工具

命令

这里只讨论通过命令的方式定义相应对象。

7.1 存储过程

7.1.1 存储过程概述

1 存储过程概念

当使用SQL Server创建应用时，TRANSACT-SQL语言是应用程序与SQL Server数据库之间的主要编程接口。使用TRANSACT-SQL语言进行程序设计时，有两种方式：一种方式是在应用程序中直接使用T-SQL 语句向SQL Server发送命令；另一种方式就是使用存储过程。

存储过程是一种数据库对象，由一组预编译的T-SQL语句组成，这些语句在一个名称下存储，并作为一个单元进行处理。存储过程类似于其他编程语言中的函数或过程：能够使用传递给它的参数，能够调用其它存储过程甚至本身，能够返回一个状态码来表示是否成功执行。

在SQL Server 2008系统中，除了可以使用Transact-SQL语言编写存储过程外，也可以使用CLR方式编写存储过程。【CLR，公用语言运行时（Commen Language Runtime），.NET提供了一个运行时环境，它负责资源管理（内存分配和垃圾收集），并保证应用和底层操作之间必要的分离。是一种多语言执行环境，支持众多的数据类型和语言特性。他管理着代码的执行，并使开发过程变得更加简单。】

SQL Server中有三类存储过程：系统存储过程（sp_为前缀）、用户

自定义存储过程和扩展存储过程（xp_为前缀，扩展了SQL Server的功能，使得用户能调用外部例程(自已编写的程序或系统提供的命令)，从SQL Server2005版本开始，将逐步删除扩展存储过程类型，因为使用CLR存储过程可以可靠和安全地替代扩展存储过程的功能）。

2 存储过程功能

在SQL Server中，存储过程是一种非常强有力的数据库对象，利用它能够显著提高应用程序的性能。主要功能表现在：

接收输入参数并以输出参数的形式为调用过程或批处理返回

多个值；

包含对数据库操作的多条语句，可以调用其他存储过程；

为调用存储过程或批处理返回一个状态值，以表示执行状

态。

3 存储过程的特点

模块化编程。一旦创建了一个存储过程，就可以在应用程序

中多次调用它，而且由于存储过程独立于应用程序，所以可

以在不影响应用程序源代码的前提下修改它。

加快执行速度。在创建一个存储过程时，SQL Server要对它

进行分析和优化，以获得最好的执行性能；当一个存储过程

被首次执行后，它就会驻留内存，当再次调用时，就不必再

加载了，从而提高了整个系统的执行速度。

减少网络通信量。使用存储过程，客户端的应用程序可以通

过一条简单的执行命令来执行存放在服务器端的存储过程，

而不必传输成百上千行的SQL语句代码，因此可以大大减少

网络阻塞。

提供安全机制。可以通过存储过程来间接将某些权限赋给用

户。

复杂业务规则和约束的一致性实现。存储过程足够强大，甚

至能够实现最复杂的业务规则，这是因为存储过程可以同时

合并过程语句和面向集合的语句。

4 存储过程的数据返回方式

存储过程可以通过四种方式把数据返回到调用处：

输出参数。既可以返回数据(数值型或字符值等)，也可以返

回游标变量（游标是可以逐行检索的结果集）。

反回值。始终是整型值。

结果集。这些语句包含在该存储过程内或该存储过程所调用

的任何其它存储过程内。

全局游标。可从存储过程外引用的全局游标。

7.1.2 创建与执行存储过程

1 创建存储过程

简化语法：

CREATE PROCE[DURE] procedure_name

[{@parameter data_type}[=default][output]][,...n]

AS

sql_statement […n]

其中，@parameter data_type 存储过程参数表，可以定义输入参数（默认）、输出参数output（即可输入数据，也可输出数据），也可以指明参数的默认值，默认值必须是常量或NULL。参数表中可以有0个或多个参数，多个参数之间用豆号分开。【输入参数：允许调用程序为存储过程传送数据值。输出参数：输出参数允许存储过程将数据值或游标变量传回调用程序，在定义时和调用时均要使用OUTPUT关键字。当然，也可以将变量的值通过输出参数输入到存储过程中。】sql_statement 指定存储过程要执行的操作。

创建存储过程也要遵守一些规则，参见教材（p230-231）。

2 执行存储过程

在SQL Server 2008系统中，可以使用EXECUTE语句执行存储过程。简化语法：

[ { EXEC[UTE ]} ]

{

[ @return_status = ]

{ procedure_name }

[ [ @parameter = ] { value

| @variable [ OUTPUT ]

| [ DEFAULT ]

}

]

[ ,...n ]

}

如果要执行带有参数的存储过程，需要在执行过程中提供存储过程参数的值。如果使用@parameter_name=value语句提供参数值，可以不考虑存储过程的参数顺序，否则如果直接提供参数值，则必须考虑参数顺序。