第8讲存储过程与触发器

第8讲存储过程与触发器大型数据库应用（SQLServer2008）本讲学习要点学会创建、查看、编辑、删除存储过程和触发器掌握存储过程和触发器在数据库中的应用大型数据库应用（SQLServer2008）本讲主要内容存储过程概述管理和使用存储过程

使用触发器

常用工具简介123

存储过程（StoredProcedure）是一组预先编译好的T-SQL

代码(程序对象).用户无需了解程序内部是如何撰写的，只需对其进行调用即可.

存储过程在服务器上运行，对数据库记录进行处理，然后把结果返回给客户端.存储过程可以查询表中的数据，可以向表中添加、修改和删除记录，还可以实现复杂的数据处理.

程序员撰写存储过程是为了避免用户自己查询数据，以达到安全灵活高效和易于管理等目的.大型数据库应用（SQLServer2008）一.

存储过程概述

●

执行速度快---存储过程只在创建时编译，以后每次执行时都不需再重新编译，而一般SQL

语句每执行一次就编译一次.因此在有大量批处理的T-SQL

代码要重复执行时,使用存储过程可以极大地提高运行效率.

●

模块化的程序设计---存储过程经过了一次创建以后，可以被无数次调用,也可以嵌套调用.用户可以独立于应用程序而对存储过程进行修改.

●

减少网络通讯量---调用存储过程只需要用一条语句.

●

增强系统的安全性---禁止用户用代码或SSMS访问数据.能够简化日常的数据管理，保持数据的一致性和安全性.使用存储过程的优点大型数据库应用（SQLServer2008）

存储过程从功能上可分为三种：

●

系统存储过程

在SQLServer

中将许多管理和查询活动都事先使用系统存储过程加以定义，用户或管理员只要执行它就可以获得系统信息.系统存储过程在SQLServer服务启动时就被加载到内存中.可查看其代码.大都是以SP开头.

●

扩展存储过程

管理员可以使用程序设计语言(如c语言)来建立自己的外部程序.扩展存储过程大都是以XP开头,放在Master

数据库中.用来调用操作系统提供功能.

●

用户存储过程

通过撰写T-SQL命令的方式实现.存储过程的分类大型数据库应用（SQLServer2008）

表13-1

常用的系统存储过程

系统存储过程功能sp_help查询对象的相关说明sp_helpdb查询数据库的相关属性sp_who列出目前连接信息sp_dboption设置数据库的属性sp_tableoption设置表的选项sp_configure配置SQLServer服务器sp_executesql执行SQL命令sp_helptext查看存储过程的内容sp_lock查询数据库的锁定状态sp_depends查询对象的依赖性状态sp_addmessage增加系统消息大型数据库应用（SQLServer2008）创建存储过程的简单语法如下：

CREATEPROC[EDURE]

<存储过程名称>[

WITH

RECOMPILE

]---重新编译

[WITHENCRYPTION]---加密AS

sql_statementRECOMPILE：在执行完存储过程以后不在高速缓存里保留存储过程的备份.所以每次执行存储过程时都要对存储过程进行重新编译和优化.ENCRYPTION：存储过程作为数据库对象将在系统中留下完整的代码等信息.使用该参数后将对访问这些数据的入口进行加密.

二.

管理和使用存储过程大型数据库应用（SQLServer2008）存储过程按参数可分为两类，一类是没有参数的，另一类是有参数的.下例用于创建一个简单的存储过程，AS下面有一段select

命令.USE

pubsGOCREATE

PROC

author_informationAS

SELECTau_fname,phone,addressFROM

authors

ORDERBY

City

GO大型数据库应用（SQLServer2008）

用户也可以搭配条件选择.USE

pubsGOCREATE

PROC

author_informationASIFUSER='dbo'

SELECT*FROMauthorsORDERBY

City

ELSESELECTau_id,CityFROM

authors

ORDERBY

CityGO大型数据库应用（SQLServer2008）EXEC[UTE]

author_information

执行没有参数的存储过程相当简单:

如果对存储过程的调用是批处理的第一条语句，则可以直接使用存储过程的名字调用：

没有参数的存储过程在实际使用时并不多见,因为存储过程在撰写时多作为函数使用,输入几个参数,经过存储过程运算后返回用户所要的数据.

存储过程也可以用SSMS可视化界面操作来执行。大型数据库应用（SQLServer2008）

输入参数是在调用这个存储过程之前必须输入的参数,而该参数仅在程序中使用,并不会再返回给用户.使用存储过程参数当程序执行存储过程时，可通过存储过程的参数向该存储过程传递值。参数分为输入参数、输出参数和返回值。输入输入参数是指由调用程序向存储过程传递的参数，输入参数可以有默认值。执行存储过程时，应该向输入参数提供相应的值。输出参数用于调用存储过程后，返回结果。

输入参数是在调用这个存储过程之前必须输入的参数,而该参数仅在程序中使用,并不会再返回给用户.USE

NorthwindGOCREATE

PROC

FindOrder@OrderIDintASSELECTOrderID,OrderDateFROM

Orders

where

OrderID=@OrderIDEXEC

FindOrder43670,10253输入参数大型数据库应用（SQLServer2008）

例1

创建一个存储过程proc_jsjstu，在mydb数据库中，实现查找“计算机系”学生的信息.注：在学生学号中的第5、6位是系部编号，系部编号与系部名称对应关系见表.USEmydb

GOCREATEPROCproc_jsjstu@xbbhvarchar(4)ASSELECT*FROMstuWHERE

SUBSTRING(id,5,2)=@xbbhGOEXECproc_jsjstu'02'例题大型数据库应用（SQLServer2008）

例2创建一个存储过程，该存储过程能够实现根据系部的编号查询出系部中男生、女生的人数，运行结果如图所示.USE

mydbGOCREATEPROCproc_countsex@xbbhvarchar(4)ASBEGIN

SELECT

sex

as

'性别',count(id)as

'人数合计'

FROM

stu

WHERE

substring(id,5,2)=@xbbh

GROUPBYsexENDEXECproc_countsex'02'例题大型数据库应用（SQLServer2008）

例3

在pubs数据库中，创建一个存储过程proc_testOutput，要求实现返回成绩在@p1与@p3之间的数量，其中，@p1与@p3是输入参数，用于指定royalty的值.CREATEPROCc_testOutput

(@p1int,@p2intOUTPUT,@p3int)ASBEGINselect@p2=count(*)fromtitleswhereroyaltybetween@p1and@p3PRINT@P2RETURN@@rowcountEND--这个存储过程返回两个值，一个是output型参数@p2，调用存储过程后，返回结果存放在@p2中。--另外一个是由return值@@rowcount（语句所影响的行数）例题Declare@aaint

Exec@aa=proc_testOutput10,null,40Select@aa大型数据库应用（SQLServer2008）Execproc_testOutput10,null,40执行带返回值和output的存储过程Declare@aaint

Execproc_testOutput10,@aaoutput,40

下例用于创建一个简单的存储过程，但这个存储过程中包含了复杂的查询语句：USE

pubsGOCREATE

PROC

author_informationAS

SELECTau_lname,au_fname,title,pub_nameFROM

authorsa

JOIN

titleauthortaON

a.au_id=ta.au_id

JOIN

titlestON

t.title_id=ta.title_idJOIN

publisherspON

t.pub_id=p.pub_idGO大型数据库应用（SQLServer2008）

但加入下面一段语句会更好一些.

USE

pubsGOIFEXISTS(SELECTnameFROMsysobjects

WHEREname='proc_testOutput')

DROPPROCEDUREproc_testOutputGO…

这个例子首先检查在表中是否存在名字相同的存储过程的记录，如果有则先删除原有的存储过程，再创建与之同名的存储过程.大型数据库应用（SQLServer2008）

USEpubsGO

CREATEFUNCTIONfun_titles(@vartypevarchar(30))

RETURNS

table

ASRETURN(selecttitle_id,title,type,

price

from

titles

where

type=@vartype)GOselect*fromdbo.fun_titles('business')

大型数据库应用（SQLServer2008）函数与存储过程的区别

自定义函数和存储过程的区别比较如下：

(1)存储过程实现的功能比自定义函数复杂，而自定义函数的实现的功能针对性比较强.

(2)存储过程可以返回参数，而自定义函数只能返回值或者表对象.

(3)存储过程一般是作为一个独立的部分来执行，而自定义函数可以作为查询语句的一个部分来调用，由于自定义函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面.

与其他数据库对象一样,修改和删除存储过程的语法是ALTERPROC<存储过程名称>

DROPPROC

<存储过程名称>

还可以使用sp_helptext

命令查看创建存储过程的文本信息,可用sp_rename修改存储过程的名字.可用sp_help

查看存储过程的一般信息.可用sp_depends

查看对象的依赖关系.

大型数据库应用（SQLServer2008）USEpubsGO

sp_helptextbyroyaltyGOsp_dependstitlesGO大型数据库应用（SQLServer2008）注意从形式看，

ALTERPROC语句与CREATE

PROC语句的主要差别仅在开头的关键字不同，但ALTERPROC语句不会更改权限和启动属性，即如果我们先删除某存储过程，再用CREATE

PROC

语句创建与它同名的存储过程，虽然两者的语句一致，但原先该存储过程的权限及启动属性将失去.另外，如果原来的存储过程定义是用WITHENCRYPTION或WITHRECOMPILE创建的，那么只有在ALTERPROC中也包含这些选项时，这些选项才有效.

可以使用SSMS

创建存储过程：（1）在树型结构上展开相应的数据库（2）展开〖可编程性〗节点（3）右击〖存储过程〗，选〖新建存储过程〗

（4）在模板中,输入正确的T-SQL

语句

（5）单击工具栏上〖!执行(X)〗按钮系统允许创建临时存储过程：局部或全局临时存储过程，只要在存储过程前加“#”“##”.大型数据库应用（SQLServer2008）使用SSMS管理存储过程

1触发器概述

触发器（trigger）是一种特殊的存储过程,它与表紧密相连，可以看作是表定义的一部分.触发器是表上的程序,主要提供数据添加、修改与删除后的程序处理方法,可以用来检查数据及进行数据更新,也可以分担一些前端应用程序撰写的逻辑规则.大型数据库应用（SQLServer2008）三.

使用触发器

触发器的英文名称是Trigger,也就是扳机的意思.与枪支操作原理类似,只有扣动扳机,子弹才会飞出.

将该词用在SQLServer

环境中,表示当表发生了添加、删除、修改操作之后,才会执行的程序,这样的程序就称为触发器.大型数据库应用（SQLServer2008）

触发器的内容,也是T-SQL命令的集合,但具有特性:

●

触发器本身存于表中,当表被删除时,存于表中的触发器也一起被删除.

●

触发器本身无法单独执行.对于存储过程或函数来说,用户可以单独执行.但触发器则不同,只有当表被添加、修改或删除时,系统才会执行触发操作.

因此,执行触发器的方法对表进行添加、修改或删除操作.大型数据库应用（SQLServer2008）

触发器也难以调试,很多可能的逻辑错误都因触发器而引起.

●

触发器本身参与事务.触发器当表被添加、修改或删除操作发生后才会被执行,所以如果数据都被删除了才去执行触发器,不是已经太晚了吗?其实不然，因为触发器本身会加入事务，可以回滚操作.这也会使事务时间延长.

●

一个表的任何操作可以有多个触发器.但同一个操作的触发器不能有先后的顺序的关系.大型数据库应用（SQLServer2008）

使用触发器的主要原因如下:

●

对输入的数据进行进一步的检查.可以对表设置条件约束,进行数据检查.但更复杂的验证就可以使用触发器.

比如,身份证号码字段,用条件约束能确定一些字符,但并没有办法确定这是一个正确的身份证号码.

●

使用触发器来维护关联更新的操作.如级联删除.大型数据库应用（SQLServer2008）

使用触发器的主要原因如下:

●

维护一个不具规范性的数据库.有时,为了提高效率,需要对表进行反规范化设计.但规范化是使用关系数据库的特性来维护数据间的一致性,若没有达到第三范式,则必须由用户自己使用程序来维护数据的一致性.

●

商业逻辑规则的撰写.一般所谓的商业逻辑规则,主要是指程序执行的流程顺序.例如,当添加订单时,则要将相对的产品库存减去销货数量,这就是商业逻辑规则.可放前端或数据库端.大型数据库应用（SQLServer2008）

例如，在pubs

数据库里，存放着出版商(publishers)的信息、出版物的信息(titles)、出版物与作者关联的信息(titleauthor)以及作者信息(authors).现在有一条出版商的信息被删除了,则所有由该出版商出版的出版物都应该将pub_id

修改为NULL，或者删除有关的出版物信息.同样titleauthor表中的信息也应该相应的得到修改.这样关联到三张表的一致性维护问题，可以使用触发器来实现.设置

DELETE

触发器大型数据库应用（SQLServer2008）

又比如公司库存量（stock）必须保持在一定水平以内.用户可以设置一个触发器.这样当库存量超过标准时，就会向数据库管理员发送信息以示警告.

SQLServer

在有关触发器方面的功能非常灵活、强大，这是吸引数据库工程师选择使用SQLServer的重要原因之一.

在SQLServer

中一张表可以有多个触发器.触发器可以容纳非常复杂的T-SQL

语句.它是一个独立的单元，作为一个事物来执行.大型数据库应用（SQLServer2008）触发器的主要功能：

●

级联修改数据库的所有相关表.

●

撤销或回滚违反引用完整性的操作.

●

执行比检查约束（check）更复杂的约束操作.

●

查找在数据修改前后的表状态之间的差别，并根据差别分别采取相应的措施.

●

在一张表的同一类型的操作(INSERT、

UPDATE或

DELETE)

上设置多个触发器，从而可以针对同样的修改语句执行不同的多种操作.

大型数据库应用（SQLServer2008）●

表的拥有者处理触发器的权限不许转授.

●

可以在触发器中引用视图或临时表，但不能在视图、临时表或系统表上创建触发器.

●

在执行修改语句时，触发器的执行是修改语句事物的一部分.所以，如果触发器执行不成功，则整个修改事物将会回滚.

●

当使用约束、规则和默认值就可以实现预定的数据完整性时，应优先考虑使用这三种措施.

●

TRUNCATETABLE

虽然在功能上与删除操作类似,但是它不会触发DELECT触发器运行.

大型数据库应用（SQLServer2008）注意大型数据库应用（SQLServer2008）触发器的分类

触发器按照触发事件可分为3类，分别是数据操纵语言(DML)触发器、数据定义语言(DDL)触发器和数据库事件触发器，触发器作用见下表.

触发器用

CREATE创建，DROP

删除,用ALTER

修改.

相应的操作也可以由SSMS

进行.

创建触发器的语句必须写在批处理的第一行.触发器是特殊的存储过程，所以适合于存储过程的管理方式，都是适用于触发器.所以可以使用

sp_helptext,sp_help,sp_depends等系统存储过程.

大型数据库应用（SQLServer2008）2.触发器的管理和使用

触发器的撰写方式相当简便,但在撰写时需要注意将触发器与表相连,因此需要指定表与表的操作才会执行触发器.触发器的简单语法如下.CREATETRIGGER<触发器名称>

ON

<表名称>AFTER(FOR)INSERT,UPDATE,DELETEAS

T-SQL命令大型数据库应用（SQLServer2008）创建储发器的语法在触发器的执行过程中，SQLServer建立和管理两个临时的虚拟表：Deleted表和Inserted表。这两个表包含了在激发触发器的操作中插入或删除的所有记录。在执行INSERT或UPDATE语句之后所有被添加或被更新的记录都会存储在Inserted表中。在执行DELETE或UPDATE语句时，从触发程序表中被删除的行会发送到Deleted表。对于更新操作，SQLServer先将要进行修改的记录存储到Deleted表中，然后再将修改后的数据复制到Inserted表以及触发程序表。Deleted表和Inserted表为表customer创建一个名为test_tr的触发器，当执行添加、更新或删除时，激活该触发器。创建test_tr触发器

CREATETRIGGERtest_trONcustomersFORINSERT,UPDATE,DELETEASSELECT*FROMinsertedSELECT*FROMdeletedcustomer表执行以下插入操作INSERTINTOcustomer(CustomerID,CompanyName,Phone)VALUES('12346','张三','1234567')USE

PubsGOCREATETRIGGERTRKehu_DeleteON

kehuAFTERDELETEASIF(SELECTcount(*)FROMDeleted)>1BEGINraiserror('一次只能删除一个',16,1)ROLLBACKTRANSACTIONEND大型数据库应用（SQLServer2008）例题deletefromkehuwherestate='CA'

下例创建了一个简单的触发器.

USE

NorthwindIFEXISTS(SELECTnameFROMsysobjectsWHEREname='tr_product_update')

DROPTRIGGERtr_product_updateGOUSE

Northwind

GO大型数据库应用（SQLServer2008）例题CREATETRIGGERtr_product_updateON

Products

AFTER

UPDATE

ASDECLARE@msgvarchar(100)SELECT@msg=str(@@rowcount)+‘employeesupdatedbythisstatement’PRINT@msg--RETURNGO/*它返回UPDATE操作所修改的行数.*/大型数据库应用（SQLServer2008）例题updateProductsSETProductName='bbb'whereProductID=1

通常存储过程和触发器可以用来维护数据库引用的行动完整性，也就是在与外键值相应的主键发生改变以后规范对外键可能执行的操作，约束外键值的变化.

用户应该记得SQLServer

提供的外键约束的特点.当存在外键引用时，用户不能删除或修改被引用的主键值或UNIQUE

列的值.

使用存储过程可以实现受外键约束限制的主键或UNIQUE

列的删除和修改.一般用级联操作.大型数据库应用（SQLServer2008）使用存储过程维护数据完整性

当使用存储过程作级联修改时，可执行步骤:

●

以新的主键值或UNIQUE

列值向主表插入新的数据行，重复现存行的所有其他列的值.

●

将依赖表中的外键值改为新值.

●

删除主表中的旧数据行.

当使用存储过程作级联删除时，可执行步骤:

●

删除外键所在的行，或将外键修改为默认值或NULL.

●

删除主表中的行.

使用存储过程来维护数据的完整性，并不是要替代原有的外键约束.只是外键约束的补充.大型数据库应用（SQLServer2008）

下例建立了一个存储过程实现级联删除.

USE

pubsGOCREATEPROCdelete_title@title_idint

ASBEGINDELETEsalesWHEREtitle_id=@title_idDELETEtitles

WHERE

title_id=@title_idENDGO大型数据库应用（SQLServer2008）例题

建立一个存储过程级联删除数据库pubs的表titles和titleauthor,当用户从表titles中删除记录时,将首先删除表titleauthor中的相关数据,两个表连接的条件是title_id相等.大型数据库应用（SQLServer2008）练习大型数据库应用（SQLServer2008）

实验指导1、存储过程的创建、执行和删除。(1)使用mydb数据库中的stu

表、course表；sc表创建一个带参数的存储过程proc_cjcx。该存储过程的作用是：当任意输入一个学生的姓名时，将从三个表中返回该学生的学号、选修的课程名称和课程成绩。(2)执行proc_cjcx存储过程，查询“万雪