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

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

语法分析阶段。是指系统检查创建存储过程的语句的语法是否正确的过程。语法检查通过之后，系统将把存储过程的定义存储在当前数据库的sys.sql_modules目录视图中。

解析阶段。是指检查存储过程引用的对象名称是否存在的过程，该过程也被称为延迟称称解析阶段。当然，只有引用的表对象才适用于延迟名称解析。

编译阶段。是指分析存储过程和生成执行计划的过程。优化后的执行计划置于过程高速缓冲存储区中。

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

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

当存储过程引用的基表发生结构变化时，该存储过程的执行计划将会自动优化。但是当在表中添加了索引或更改了索引列中的数据后，该执行计划不会自动优化，此时应该重新编译存储过程。可以使用三种方式重新编译存储过程：

使用sp_recompile系统存储过程；

在EXECUTE语句中使用WITH RECOMPILE子句；

在CREATE PROCEDURE语句中使用WITH RECOMPILE子

句。

3 存储过程的创建与执行实例

例1：查找指定日期后签定的订单（创建有返回结果集的存储过程）use Northwind

go

if OBJECT_ID('p1','p')is not null

drop procedure p1

go

create procedure p1

@datex datetime

as

select*from orders where orderdate>=@datex

go

********************************************

use Northwind

execute p1'1997-1-1'

例2：求产品的平均单价（创建有返回参数的存储过程）

use Northwind

go

if OBJECT_ID('p2','p')is not null

drop procedure p1

go

create procedure p2

@avgprice money output

as

select@avgprice=avg(unitprice)from products

go

***********************************

use Northwind

declare@x money

execute p2@x output

select@x

例3：判断是否有1997年2月20号签定的订单，有返回0，没有返回1（创建有返回值的存储过程）

use Northwind

go

if OBJECT_ID('p3','p')is not null

drop procedure p1

go

create procedure p3

@datex datetime

as

if exists(select*from orders where

orderdate=@datex)

return 0

else

return 1

go

****************************************************

use Northwind

declare@x int

execute@x=p3'1997-2-20'

select@x

例4：根据最后姓名模糊查找职员信息（创建带有缺省值参数的存储过程）

use Northwind

go

if OBJECT_ID('p4','p')is not null

drop procedure p1

go

create procedure p4

@namex nvarchar(20)='D%'

as

select*from employees where lastname like

@namex+'%'

go

******************************************

use Northwind

execute p4

execute p4'B'

7.1.3 修改存储过程

在Microsoft SQL Server 2008系统中，可以使用ALTER PROCEDURE 语句修改已经存在的存储过程。修改存储过程，不是删除和重建存储过程，其目的是保持存储过程的权限不发生变化。简化语法如下：ALTER PROCE[DURE] procedure_name

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

AS

sql_statement […n]

7.1.4 删除存储过程

如果某个存储过程不再需要了，可以使用DROP PROCEDURE语句删除该存储过程。具体语法是：

DROP PROCEDURE procedure_name

7.2 触发器

7.2.1触发器概述

触发器是数据库服务器中发生事件时能自动执行的一种特种存储过程，主要用于强制规则和数据完整性。触发器是通过事件触发而自动执行的，不能被直接调用执行。

根据触发事件的不同，触发器分为两种类型。由DML触发的是DML 触发器，由DDL触发的是DDL触发器（DDL触发器是从SQL Server 2005版开始新增的，分为数据库级的DDL触发器和服务器级的DDL触发器）。

在SQL Server 2008系统中，除了可以使用Transact-SQL语言编写触发器外，也可以使用CLR方式编写触发器。

7.2.2 DML触发器

1 DML触发器概述

DML触发器是和数据库中的表相关的，当对表进行INSERT、UPDATE、DELETE操作时，将触发相应的触发器。在SQL Server中，按照触发器和触发事件的执行时间的先后顺序划分，有两类DML触发器，一类是标准（After）触发器，另一类是新的Instead-of触发器。

当触发事件（INSERT、UPDATE、DELETE）执行之后才执行触发器操作，这时的触发器类型是AFTER触发器。AFTER触发器只能在表上定义。如果想要使用触发器操作替代触发事件的操作，可以使用INSTEAD OF触发器。INSTEAD OF触发器可以建在表上，也可以建在视图上。

触发器的主要功能表现在：

强化约束。触发器能够实现比CHECK更为复杂的约束。

级联修改。当某张表中的数据发生变化时，通过触发器对与

之关联的表进行相应的更新。

实施数据完整性。使用触发器禁止或回滚违反数据完整性的

操作或其它不经许可的操作。

更新前后状态比较。触发器可以区对数据更新操作的前后状

态差别，并可以完成基于这种差别的特定动作。

对DML触发器的说明：

触发原因。每个修改语句（Insert、Update、Delete）都会引

发一次触发器的执行。甚至当修改语句影响了0条记录时也

会被引发。

触发时机。After触发器是在修改语句成功完成之后被引发，

而Instead-of触发器是在修改语句完成之前（Inserted和Deleted

表被建立之后），在所有约束之前被启动

视图触发器。对视图只能建立Instead-of触发器，不能建立

After触发器。

一个表的一个操作能定义多个AFTER触发器，根据定义的先

后顺序被触发，也可以通过sp_settriggerorder来改变触发顺

序。一个表或一个视图对于每种操作只能定义一个Instead-of

触发器。

2 DML触发器的定义

[1]DML触发器的创建

可以使用CREATE TRIGGER语句创建DML触发器。基本语法如下：CREATE TRIGGER trigger_name

ON {table_name | view}

{FOR | AFTER | INSTEAD OF}

{[INSERT][,][UPDATE][,][DELETE]}

AS

sql_statement[…n]

虽然在触发器中可以包括许多Transact-SQL语句，但仍有一些语句不能用在触发器中（参见教材p239）

创建触发器时，有关触发器的信息就记录在sys.triggers对象目录视图、sys.trigger_events对象目录视图以及sys.sql_modules目录视图中。可以使用sp_helptext系统存储过程查看触发器的定义信息。

例1：在employees表上创建一个delete触发器，显示被删除员工的个数信息。

use Northwind

go

if exists(select*from sys.triggers where

name='tr_employee')

drop trigger dbo.tr_employee

go

CREATE TRIGGER tr_employee ON employees

AFTER DELETE

AS

DECLARE@msg varchar(50)

SELECT@msg=STR(@@ROWCOUNT)+'were deleted'

SELECT@msg

RETURN

go

*************************************************

use Northwind

go

insert into dbo.Employees(LastName,FirstName) values('aa','xx')

insert into dbo.Employees(LastName,FirstName) values('bb','yy')

go

delete from dbo.Employees where EmployeeID>=10 go

例2：当用户向a表插入数据时，b表也插入一条记录。

use mydb

go

if OBJECT_ID('a')is not null

drop table a

go

create table a

(a1char(5)primary key,

a2varchar(20)

)

go

if OBJECT_ID('b')is not null

drop table b

go

create table b

(b1int identity(1,1)primary key, b2varchar(20)

)

go

if exists(select*from sys.triggers where name='tr_1')

drop trigger dbo.tr_1

go

CREATE TRIGGER tr_1ON a

AFTER insert

as

insert into b(b2)values('xx')

go

**************************************

use mydb

go

insert into a values('1001','dddd')

insert into a values('1002','eeeeee')

insert into a values('1003','cccc')

go

************************************

select*from b

例3：根据上例所建立的a表，建立一个INSTEAD OF触发器，当删除记录时，显示提示信息而不进行删除操作。

use mydb

go

if exists(select*from sys.triggers where

name='tr_2')

drop trigger dbo.tr_2

go

CREATE TRIGGER tr_2ON a

instead of delete

as

print'不能删除'

go

***************************************

use mydb

go

delete from a where a1='1000'

go

************************************

select*from a

[2]inserted和deleted虚拟表

在触发器的执行过程中，SQL Server要维护两个临时的虚拟表：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操作中的两个虚表。

use mydb

go

if exists(select*from sys.triggers where

name='test_insert')

drop trigger dbo.test_insert

go

create trigger test_insert

on a

for insert

as

select*from inserted

select*from deleted

select*from a

go

****************************************

insert into a values('1002','2222222')

例3：验证delete操作中的两个虚表。

use mydb

go

if exists(select*from sys.triggers where

name='test_delete')

drop trigger dbo.test_delete

go

create trigger test_delete

on a

for delete

as

select*from inserted

select*from deleted

select*from a

go

**********************************************

delete from a where a1='1001'

例4：验证update操作中的两个虚表。

use mydb

go

if exists(select*from sys.triggers where

name='test_update')

drop trigger dbo.test_update

go

create trigger test_update

on a

for update

as

select*from inserted

select*from deleted

select*from a

go

******************************************

update a set a2='vvvvvv'where a1='1002'

[3]修改触发器

DML触发器是可以修改的，使用ALTER TRIGGER完成修改操作。

[4]禁用和删除触发器

通过DISABLE TRIGGER trigger_name ON table_name命令或ALTER TABLE table_name DISABLE TRIGGER trigger_name来禁用触发器；通过ENABLE TRIGGER trigger_name ON table_name命令或ALTER

TABLE table_name ENABLE TRIGGER trigger_name来禁用触发器DML触发器是可以删除的，使用DROP TRIGGER完成修改操作。7.2.3 DDL触发器

1 DDL触发器概述

DDL触发器是一种特殊的触发器，它在响应数据定义语言（DDL）语句时触发，可以用于在数据库中执行管理任务。触发事件主要是CREATE、ALTER、DROP等语句，并且触发的时间只有AFTER。

一般地，DDL触发器主要用于下面这些操作：

防止对数据库架构进行某些更改；

希望数据库中发生某种情况以实现相应数据库架构中的更

改；

记录数据库架构中的更改或事件。

DDL触发器有服务器级的和数据库级的两种。我们可以为服务器级的事件定义服务器级DDL触发器，如创建数据库、更改登录等。数据库级触发器可以响应数据库级事件，如创建、修改或删除对象等。

2 DDL触发器定义

DDL触发器的定义和DML触发器的定义相似，包括创建、修改和删除操作。这里只讨论创建操作。

简化语法：

CREATE TRIGGER trigger_name

ON {ALL SERVER | DATABASE}

[WITH ENCRYPTION]

{ FOR | AFTER}{event_type}

AS

Sql_statement

其中，

ALL SERVER关键字表示该DDL触发器的作用域是整个服务器，即服务器级DDL触发器。

DATABASE关键字表示该DDL触发器的作用域是整个数据库，即数据库级DDL触发器。

event_type参数用于指定触发事件，数据库范围内的事件请参见教材（p247），服务器范围内的事件请参见孝材（p248）。

例5：防止删除或修改表的数据库级DDL触发器。

use mydb

go

if exists(select*from sys.triggers where

name='test_ddltrigger')

drop trigger test_ddltrigger on database

go

create trigger test_ddltrigger

on database

for drop_table,alter_table

as

print N'禁止删除或修改当前数据库中的表'

rollback

go

例6：建立服务级存储过程，当创建数据库时显示信息。

IF EXISTS(SELECT*FROM sys.server_triggers

WHERE name='ddl_trig_database')

DROP TRIGGER ddl_trig_database

ON ALL SERVER;

GO

CREATE TRIGGER ddl_trig_database

ON ALL SERVER

FOR CREATE_DATABASE

AS

PRINT'Database Created.'

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText) [1]','nvarchar(max)')—该函数用业捕获有关激发DDL触发器的事

件信息

GO

修改DDL触发器使用ALTER TRIGGER命令。

删除DDL触发器使用DROP TRIGGER命令，具体语法如下：

DROP TRIGGER trigger_name [ ,...n ] ON { DATABASE | ALL SERVER }

禁用DDL触发器的命令如下：

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }

ON { object_name | DATABASE | ALL SERVER }

启用DDL触发器的命令如下：

ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }

ON { object_name | DATABASE | ALL SERVER }

7.3 用户自定义函数

7.3.1 用户自定义函数概述

用户自定义函数是用于封装经常执行的逻辑的子例程。任何代码想要执行函数所包含的逻辑，都可以调用该函数，而不必重复所有的函数逻辑。用户定义函数接受零个或多个输入参数，并返回单值，可以是单个标题值，也可以是table类型的值。输入参数可以是除timestamp，cursor和table类型之外的任何数据类型。返回值可以是除timestamp，cursor，text，ntext和image类型之外的任何数据类型。

使用用户定义函数可以带来许多好处：

实现模块化设计；

可以独立于源代码进行修改；

加快执行速度；

减少网络流量。

用户自定义函数可以在选择和赋值语句中使用；可以用作选择条件的一部分；可以在表达式中使用；可以作为check约束和default约束；表值函数可以用在T-SQL语句中任何期望使用表的地方。

用户定义函数可以使用Transact-SQL语言编写，也可以使用.NET编程语言来编写。

在SQL Server中，用户定义函数分为两类，即用户定义标量函数和用户定义表值函数。用户定义标量函数只返回单个数据值。用户定义表值函数返回table类型数据，又分为内嵌表值函数和多语句表值函数。

7.3.2 标量函数的建立与调用

简化语法如下：

CREATE FUNCTION [ owner_name.] function_name

( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS scalar_return_data_type

[ WITH < function_option> [ [,] ...n] ]

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END

在可使用标量表达式的位置可唤醒调用标量值函数，包括计算列和CHECK 约束定义。当唤醒调用标量值函数时，至少应使用函数的两部分名称。

[database_name.]schema_name.function_name ([argument_expr][,...])例1：编写函数，判断一个整数的奇偶性，偶数返回0，奇数返回1。use mydb

go

if OBJECT_ID('f1')is not null

drop function f1

go

create function f1(@m int)

returns int--偶数返回，奇数返回

as

begin

declare@x int

if@m%2=0

set@x=0

else

set@x=1

return@x--最后一条语句必须是return

end

************************************************

select dbo.f1(9)--调用时,必须指明架构,括号不能省略例2：编写一个求1+2+3+…+n的和的函数

use mydb

go

if OBJECT_ID('f2')is not null

drop function f2

go

create function f2(@n int)

returns int

as

begin

declare@sum int

declare@i int

set@sum=0

set@i=1

while@i<=@n

begin

set@sum=@sum+@i

set@i=@i+1

end

return@sum

end

*******************************************************

select dbo.f2(10)

例3：编写函数完成两个字符串的连接运算，要求结果串取第一个字符串的前四个字符，取第二个字符串的后四个字符。

use mydb

go

if OBJECT_ID('f3')is not null

drop function f3

go

create function f3(@str1nvarchar(100),@str2 nvarchar(100))

returns nchar(8)

as

begin

return left(@str1,4)+right(@str2,4)

end

****************************************************

select dbo.f3('abcdefg','1234567')

7.3.3 内嵌表值函数的建立与调用

也叫内联用户定义的表值函数，是一种特殊类型的用户自定义的表值函数，它的目的是实现参数化的视图。

简化语法：

CREATE FUNCTION [ owner_name.] function_name

( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS TABLE

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

RETURN [ ( ] select-stmt [ ) ]

函数不必定义返回值的格式，只要指定Table关键字就足够了。在return子句中，一个select语句创建一个结果集。

可以使用由一部分组成的名称唤醒调用表值函数。

[database_name.][owner_name.]function_name ([argument_expr] [,...])

例4：例1：编写函数查询指定年份的订单信息。

use northwind

go

if OBJECT_ID('f4')is not null

drop function f4

go

create function f4(@year int)

returns table

as

return select*from orders where year(orderdate)=@year

********************************************************** select*from f4(1996)

7.3.4多语句表值函数的建立与调用

可以在使用表（或视图）的地方使用用户自定义的表值函数，从这个方面来说，用户自定义的表值函数实现了视图的功能，可以带参数，是动态的。

简化语法：

CREATE FUNCTION [ owner_name.] function_name

( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS @return_variable TABLE < table_type_definition >

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

BEGIN

function_body

RETURN

END

可以使用由一部分组成的名称唤醒调用表值函数。

[database_name.][owner_name.]function_name ([argument_expr] [,...])

例5：编写函数，将一个整数拆分成2的幂的和。

use mydb

go

if OBJECT_ID('f5')is not null

drop function f5

go

create function[dbo].f5(@M integer)

returns@tableX table(X int not null)

as

begin

declare@x as int

set@x=1

while@x<=@m

begin

if@m&@x=@x

insert into@tableX values(@x)

set@x=@x*2

end

return--不能省略

end

******************************************************

select*from f5(15)

多语句表值函数和内联用户自定义的表值函数的区别：

1)多语句表值函数在returns子句中定义了一个表变量，并定义了结构

2)在函数体中存在填充表变量内容的语句

3）函数末尾必须有一个空的return语句

2.1 XXX