第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]ASsql_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语句提供参数值，可以不考虑存储过程的参数顺序，否则如果直接提供参数值，则必须考虑参数顺序。

存储过程创建之后，在第一次执行时需要经过语法分析阶段、解析阶段、编译阶段和执行阶段。

语法分析阶段。

是指系统检查创建存储过程的语句的语法是否正确的过程。

语法检查通过之后，系统将把存储过程的定义存储在当前数据库的sys.sql_modules目录视图中。

解析阶段。

是指检查存储过程引用的对象名称是否存在的过程，该过程也被称为延迟称称解析阶段。

当然，只有引用的表对象才适用于延迟名称解析。

编译阶段。

是指分析存储过程和生成执行计划的过程。

优化后的执行计划置于过程高速缓冲存储区中。

执行阶段。

是指执行驻留在过程高速缓冲存储区中的存储过程执行计划的过程。

在以后的执行过程中，如果现有的执行计划依然驻留在过程高速缓冲存储区中，那么SQL Server将重用现有执行计划。

当存储过程引用的基表发生结构变化时，该存储过程的执行计划将会自动优化。

但是当在表中添加了索引或更改了索引列中的数据后，该执行计划不会自动优化，此时应该重新编译存储过程。

可以使用三种方式重新编译存储过程：使用sp_recompile系统存储过程；在EXECUTE语句中使用WITH RECOMPILE子句；在CREATE PROCEDURE语句中使用WITH RECOMPILE子句。

3 存储过程的创建与执行实例例1：查找指定日期后签定的订单（创建有返回结果集的存储过程）use Northwindgoif OBJECT_ID('p1','p')is not nulldrop procedure p1gocreate procedure p1@datex datetimeasselect*from orders where orderdate>=@datexgo********************************************use Northwindexecute p1'1997-1-1'例2：求产品的平均单价（创建有返回参数的存储过程）use Northwindgoif OBJECT_ID('p2','p')is not nulldrop procedure p1gocreate procedure p2@avgprice money outputasselect@avgprice=avg(unitprice)from productsgo***********************************use Northwinddeclare@x moneyexecute p2@x outputselect@x例3：判断是否有1997年2月20号签定的订单，有返回0，没有返回1（创建有返回值的存储过程）use Northwindgoif OBJECT_ID('p3','p')is not nulldrop procedure p1gocreate procedure p3@datex datetimeasif exists(select*from orders whereorderdate=@datex)return 0elsereturn 1go****************************************************use Northwinddeclare@x intexecute@x=p3'1997-2-20'select@x例4：根据最后姓名模糊查找职员信息（创建带有缺省值参数的存储过程）use Northwindgoif OBJECT_ID('p4','p')is not nulldrop procedure p1gocreate procedure p4@namex nvarchar(20)='D%'asselect*from employees where lastname like@namex+'%'go******************************************use Northwindexecute p4execute p4'B'7.1.3 修改存储过程在Microsoft SQL Server 2008系统中，可以使用ALTER PROCEDURE 语句修改已经存在的存储过程。

修改存储过程，不是删除和重建存储过程，其目的是保持存储过程的权限不发生变化。

简化语法如下：ALTER PROCE[DURE] procedure_name[{@parameter data_type}[=default][output]][,...n]ASsql_statement […n]7.1.4 删除存储过程如果某个存储过程不再需要了，可以使用DROP PROCEDURE语句删除该存储过程。

具体语法是：DROP PROCEDURE procedure_name7.2 触发器7.2.1触发器概述触发器是数据库服务器中发生事件时能自动执行的一种特种存储过程，主要用于强制规则和数据完整性。