第7章 存储过程触发器和用户自定义函数.docx
《第7章 存储过程触发器和用户自定义函数.docx》由会员分享，可在线阅读，更多相关《第7章 存储过程触发器和用户自定义函数.docx（25页珍藏版）》请在冰豆网上搜索。
第7章存储过程触发器和用户自定义函数
第7章存储过程、触发器和用户自定义函数（6课时）
主要内容：
1存储过程（概述、创建与执行、修改与删除）
2触发器（概述、DML触发器、DDL触发器）
3用户自定义函数（概述、标量函数的建立与调用、内嵌表值函数的建立与调用、多语名表值函数的建立与调用）
存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。
触发器是一种特殊类型的存储过程，可以实现自动化的操作。
用户定义函数是由用户根据应用程序的需要而定义的可以完成特定操作的函数。
这三种数据库对象都可以通过两种方法来定义：
●SQLServerManagementStudio工具
●命令
这里只讨论通过命令的方式定义相应对象。
7.1存储过程
7.1.1存储过程概述
1存储过程概念
当使用SQLServer创建应用时，TRANSACT-SQL语言是应用程序与SQLServer数据库之间的主要编程接口。
使用TRANSACT-SQL语言进行程序设计时，有两种方式：
一种方式是在应用程序中直接使用T-SQL语句向SQLServer发送命令；另一种方式就是使用存储过程。
存储过程是一种数据库对象，由一组预编译的T-SQL语句组成，这些语句在一个名称下存储，并作为一个单元进行处理。
存储过程类似于其他编程语言中的函数或过程：
能够使用传递给它的参数，能够调用其它存储过程甚至本身，能够返回一个状态码来表示是否成功执行。
在SQLServer2008系统中，除了可以使用Transact-SQL语言编写存储过程外，也可以使用CLR方式编写存储过程。
【CLR，公用语言运行时（CommenLanguageRuntime），.NET提供了一个运行时环境，它负责资源管理（内存分配和垃圾收集），并保证应用和底层操作之间必要的分离。
是一种多语言执行环境，支持众多的数据类型和语言特性。
他管理着代码的执行，并使开发过程变得更加简单。
】
SQLServer中有三类存储过程：
系统存储过程（sp_为前缀）、用户自定义存储过程和扩展存储过程（xp_为前缀，扩展了SQLServer的功能，使得用户能调用外部例程（自已编写的程序或系统提供的命令），从SQLServer2005版本开始，将逐步删除扩展存储过程类型，因为使用CLR存储过程可以可靠和安全地替代扩展存储过程的功能）。
2存储过程功能
在SQLServer中，存储过程是一种非常强有力的数据库对象，利用它能够显著提高应用程序的性能。
主要功能表现在：
●接收输入参数并以输出参数的形式为调用过程或批处理返回多个值；
●包含对数据库操作的多条语句，可以调用其他存储过程；
●为调用存储过程或批处理返回一个状态值，以表示执行状态。
3存储过程的特点
●模块化编程。
一旦创建了一个存储过程，就可以在应用程序中多次调用它，而且由于存储过程独立于应用程序，所以可以在不影响应用程序源代码的前提下修改它。
●加快执行速度。
在创建一个存储过程时，SQLServer要对它进行分析和优化，以获得最好的执行性能；当一个存储过程被首次执行后，它就会驻留内存，当再次调用时，就不必再加载了，从而提高了整个系统的执行速度。
●减少网络通信量。
使用存储过程，客户端的应用程序可以通过一条简单的执行命令来执行存放在服务器端的存储过程，而不必传输成百上千行的SQL语句代码，因此可以大大减少网络阻塞。
●提供安全机制。
可以通过存储过程来间接将某些权限赋给用户。
●复杂业务规则和约束的一致性实现。
存储过程足够强大，甚至能够实现最复杂的业务规则，这是因为存储过程可以同时合并过程语句和面向集合的语句。
4存储过程的数据返回方式
存储过程可以通过四种方式把数据返回到调用处：
●输出参数。
既可以返回数据（数值型或字符值等），也可以返回游标变量（游标是可以逐行检索的结果集）。
●反回值。
始终是整型值。
●结果集。
这些语句包含在该存储过程内或该存储过程所调用的任何其它存储过程内。
●全局游标。
可从存储过程外引用的全局游标。
7.1.2创建与执行存储过程
1创建存储过程
简化语法：
CREATEPROCE[DURE]procedure_name
[{@parameterdata_type}[=default][output]][,...n]
AS
sql_statement[…n]
其中，@parameterdata_type存储过程参数表，可以定义输入参数（默认）、输出参数output（即可输入数据，也可输出数据），也可以指明参数的默认值，默认值必须是常量或NULL。
参数表中可以有0个或多个参数，多个参数之间用豆号分开。
【输入参数：
允许调用程序为存储过程传送数据值。
输出参数：
输出参数允许存储过程将数据值或游标变量传回调用程序，在定义时和调用时均要使用OUTPUT关键字。
当然，也可以将变量的值通过输出参数输入到存储过程中。
】
sql_statement指定存储过程要执行的操作。
创建存储过程也要遵守一些规则，参见教材（p230-231）。
2执行存储过程
在SQLServer2008系统中，可以使用EXECUTE语句执行存储过程。
简化语法：
[{EXEC[UTE]}]
{
[@return_status=]
{procedure_name}
[[@parameter=]{value
|@variable[OUTPUT]
|[DEFAULT]
}
]
[,...n]
}
如果要执行带有参数的存储过程，需要在执行过程中提供存储过程参数的值。
如果使用@parameter_name=value语句提供参数值，可以不考虑存储过程的参数顺序，否则如果直接提供参数值，则必须考虑参数顺序。
存储过程创建之后，在第一次执行时需要经过语法分析阶段、解析阶段、编译阶段和执行阶段。
语法分析阶段。
是指系统检查创建存储过程的语句的语法是否正确的过程。
语法检查通过之后，系统将把存储过程的定义存储在当前数据库的sys.sql_modules目录视图中。
解析阶段。
是指检查存储过程引用的对象名称是否存在的过程，该过程也被称为延迟称称解析阶段。
当然，只有引用的表对象才适用于延迟名称解析。
编译阶段。
是指分析存储过程和生成执行计划的过程。
优化后的执行计划置于过程高速缓冲存储区中。
执行阶段。
是指执行驻留在过程高速缓冲存储区中的存储过程执行计划的过程。
在以后的执行过程中，如果现有的执行计划依然驻留在过程高速缓冲存储区中，那么SQLServer将重用现有执行计划。
当存储过程引用的基表发生结构变化时，该存储过程的执行计划将会自动优化。
但是当在表中添加了索引或更改了索引列中的数据后，该执行计划不会自动优化，此时应该重新编译存储过程。
可以使用三种方式重新编译存储过程：
●使用sp_recompile系统存储过程；
●在EXECUTE语句中使用WITHRECOMPILE子句；
●在CREATEPROCEDURE语句中使用WITHRECOMPILE子句。
3存储过程的创建与执行实例
例1：
查找指定日期后签定的订单（创建有返回结果集的存储过程）
useNorthwind
go
ifOBJECT_ID（'p1','p'）isnotnull
dropprocedurep1
go
createprocedurep1
@datexdatetime
as
select*fromorderswhereorderdate>=@datex
go
********************************************
useNorthwind
executep1'1997-1-1'
例2：
求产品的平均单价（创建有返回参数的存储过程）
useNorthwind
go
ifOBJECT_ID（'p2','p'）isnotnull
dropprocedurep1
go
createprocedurep2
@avgpricemoneyoutput
as
select@avgprice=avg（unitprice）fromproducts
go
***********************************
useNorthwind
declare@xmoney
executep2@xoutput
select@x
例3：
判断是否有1997年2月20号签定的订单，有返回0，没有返回1（创建有返回值的存储过程）
useNorthwind
go
ifOBJECT_ID（'p3','p'）isnotnull
dropprocedurep1
go
createprocedurep3
@datexdatetime
as
ifexists（select*fromorderswhereorderdate=@datex）
return0
else
return1
go
****************************************************
useNorthwind
declare@xint
execute@x=p3'1997-2-20'
select@x
例4：
根据最后姓名模糊查找职员信息（创建带有缺省值参数的存储过程）
useNorthwind
go
ifOBJECT_ID（'p4','p'）isnotnull
dropprocedurep1
go
createprocedurep4
@namexnvarchar（20）='D%'
as
select*fromemployeeswherelastnamelike@namex+'%'
go
******************************************
useNorthwind
executep4
executep4'B'
7.1.3修改存储过程
在MicrosoftSQLServer2008系统中，可以使用ALTERPROCEDURE语句修改已经存在的存储过程。
修改存储过程，不是删除和重建存储过程，其目的是保持存储过程的权限不发生变化。
简化语法如下：
ALTERPROCE[DURE]procedure_name
[{@parameterdata_type}[=default][output]][,...n]
AS
sql_statement[…n]
7.1.4删除存储过程
如果某个存储过程不再需要了，可以使用DROPPROCEDURE语句删除该存储过程。
具体语法是：
DROPPROCEDUREprocedure_name
7.2触发器
7.2.1触发器概述
触发器是数据库服务器中发生事件时能自动执行的一种特种存储过程，主要用于强制规则和数据完整性。
触发器是通过事件触发而自动执行的，不能被直接调用执行。
根据触发事件的不同，触发器分为两种类型。
由DML触发的是DML触发器，由DDL触发的是DDL触发器（DDL触发器是从SQLServer2005版开始新增的，分为数据库级的DDL触发器和服务器级的DDL触发器）。
在SQLServer2008系统中，除了可以使用Transact-SQL语言编写触发器外，也可以使用CLR方式编写触发器。
7.2.2DML触发器
1DML触发器概述
DML触发器是和数据库中的表相关的，当对表进行INSERT、UPDATE、DELETE操作时，将触发相应的触发器。
在SQLServer中，按照触发器和触发事件的执行时间的先后顺序划分，有两类DML触发器，一类是标准（After）触发器，另一类是新的Instead-of触发器。
当触发事件（INSERT、UPDATE、DELETE）执行之后才执行触发器操作，这时的触发器类型是AFTER触发器。
AFTER触发器只能在表上定义。
如果想要使用触发器操作替代触发事件的操作，可以使用INSTEADOF触发器。
INSTEADOF触发器可以建在表上，也可以建在视图上。
触发器的主要功能表现在：
●强化约束。
触发器能够实现比CHECK更为复杂的约束。
●级联修改。
当某张表中的数据发生变化时，通过触发器对与之关联的表进行相应的更新。
●实施数据完整性。
使用触发器禁止或回滚违反数据完整性的操作或其它不经许可的操作。
●更新前后状态比较。
触发器可以区对数据更新操作的前后状态差别，并可以完成基于这种差别的特定动作。
对DML触发器的说明：
●触发原因。
每个修改语句（Insert、Update、Delete）都会引发一次触发器的执行。
甚至当修改语句影响了0条记录时也会被引发。
●触发时机。
After触发器是在修改语句成功完成之后被引发，而Instead-of触发器是在修改语句完成之前（Inserted和Deleted表被建立之后），在所有约束之前被启动
●视图触发器。
对视图只能建立Instead-of触发器，不能建立After触发器。
●一个表的一个操作能定义多个AFTER触发器，根据定义的先后顺序被触发，也可以通过sp_settriggerorder来改变触发顺序。
一个表或一个视图对于每种操作只能定义一个Instead-of触发器。
2DML触发器的定义
[1]DML触发器的创建
可以使用CREATETRIGGER语句创建DML触发器。
基本语法如下：
CREATETRIGGERtrigger_name
ON{table_name|view}
{FOR|AFTER|INSTEADOF}
{[INSERT][,][UPDATE][,][DELETE]}
AS
sql_statement[…n]
虽然在触发器中可以包括许多Transact-SQL语句，但仍有一些语句不能用在触发器中（参见教材p239）
创建触发器时，有关触发器的信息就记录在sys.triggers对象目录视图、sys.trigger_events对象目录视图以及sys.sql_modules目录视图中。
可以使用sp_helptext系统存储过程查看触发器的定义信息。
例1：
在employees表上创建一个delete触发器，显示被删除员工的个数信息。
useNorthwind
go
ifexists（select*fromsys.triggerswherename='tr_employee'）
droptriggerdbo.tr_employee
go
CREATETRIGGERtr_employeeONemployees
AFTERDELETE
AS
DECLARE@msgvarchar（50）
SELECT@msg=STR（@@ROWCOUNT）+'weredeleted'
SELECT@msg
RETURN
go
*************************************************
useNorthwind
go
insertintodbo.Employees（LastName,FirstName）values（'aa','xx'）
insertintodbo.Employees（LastName,FirstName）values（'bb','yy'）
go
deletefromdbo.EmployeeswhereEmployeeID>=10
go
例2：
当用户向a表插入数据时，b表也插入一条记录。
usemydb
go
ifOBJECT_ID（'a'）isnotnull
droptablea
go
createtablea
（a1char（5）primarykey,
a2varchar（20）
）
go
ifOBJECT_ID（'b'）isnotnull
droptableb
go
createtableb
（b1intidentity（1,1）primarykey,
b2varchar（20）
）
go
ifexists（select*fromsys.triggerswherename='tr_1'）
droptriggerdbo.tr_1
go
CREATETRIGGERtr_1ONa
AFTERinsert
as
insertintob（b2）values（'xx'）
go
**************************************
usemydb
go
insertintoavalues（'1001','dddd'）
insertintoavalues（'1002','eeeeee'）
insertintoavalues（'1003','cccc'）
go
************************************
select*fromb
例3：
根据上例所建立的a表，建立一个INSTEADOF触发器，当删除记录时，显示提示信息而不进行删除操作。
usemydb
go
ifexists（select*fromsys.triggerswherename='tr_2'）
droptriggerdbo.tr_2
go
CREATETRIGGERtr_2ONa
insteadofdelete
as
print'不能删除'
go
***************************************
usemydb
go
deletefromawherea1='1000'
go
************************************
select*froma
[2]inserted和deleted虚拟表
在触发器的执行过程中，SQLServer要维护两个临时的虚拟表：
inserted和deleted。
Inserted表包含引发触发器操作过程中被插入的所有记录，deleted表包含引发触发器操作过程中被删除的所有记录（update操作可理解为先删除，后插入）。
向表中插入数据时，insert触发器触发执行，新插入的记录加到inserted表中。
Inserted表是一个逻辑表，保存所插入记录的备份，允许用户参考新插入的数据。
在inserted表中的记录总是触发器表中的一行或多行记录的冗余。
从表中删除数据时，delete触发器触发执行，被删除的数据放在deleted表中。
Deleted表是一个罗辑表，保存已经从表中删除的记录。
当删除的记录放入deleted表中时，该记录就不会存在触发器表中了。
修改一条记录等于删除一条旧记录的同时插入一条新记录。
也就是表中原来的记录移动到deleted表中，修改过的记录插入到了inserted表中。
Transact-SQL语句
Inserted表
Deleted表
INSERT
新添加的行
空（N/A）
UPDATE
新行
旧行
DELETE
空
被删除的行
例4：
验证insert操作中的两个虚表。
usemydb
go
ifexists（select*fromsys.triggerswherename='test_insert'）
droptriggerdbo.test_insert
go
createtriggertest_insert
ona
forinsert
as
select*frominserted
select*fromdeleted
select*froma
go
****************************************
insertintoavalues（'1002','2222222'）
例3：
验证delete操作中的两个虚表。
usemydb
go
ifexists（select*fromsys.triggerswherename='test_delete'）
droptriggerdbo.test_delete
go
createtriggertest_delete
ona
fordelete
as
select*frominserted
select*fromdeleted
select*froma
go
**********************************************
deletefromawherea1='1001'
例4：
验证update操作中的两个虚表。
usemydb
go
ifexists（select*fromsys.triggerswherename='test_update'）
droptriggerdbo.test_update
go
createtriggertest_update
ona
forupdate
as
select*frominserted
select*fromdeleted
select*froma
go
******************************************
updateaseta2='vvvvvv'wherea1='1002'
[3]修改触发器
DML触发器是可以修改的，使用ALTERTRIGGER完成修改操作。
[4]禁用和删除触发器
通过DISABLETRIGGERtrigger_nameONtable_name命令或ALTERTABLEtable_nameDISABLETRIGGERtrigger_name来禁用触发器；通过ENABLETRIGGERtrigger_nameONtable_name命令或ALTERTABLEtable_nameENABLETRIGGERtrigger_name来禁用触发器
DML触发器是可以删除的，使用DROPTRIGGER完成修改操作。
7.2.3DDL触发器
1DDL触发器概述
DDL触发器是一种特殊的触发器，它在响应数据定义语言（DDL）语句时触发，可以用于在数据库中执行管理任务。
触发事件主要是CREATE、ALTER、DROP等语句，并且触发的时间只有AFTER。
一般地，DDL触发器主要用于下面这些操作：
●防止对数据库架构进行某些更改；
●希望数据库中发生某种情况以实现相应数据库架构中的更改；
●记录数据库架构中的更改或事件。
DDL触发器有服务器级的和数据库级的两种。
我们可以为服务器级的事件定义服务器级DDL触发器，如创建数据库、更改登录等。
数据库级触发器可以响应数据库
升级会员 