数据库系统14-存储过程和触发器.ppt

《数据库系统14-存储过程和触发器.ppt》由会员分享，可在线阅读，更多相关《数据库系统14-存储过程和触发器.ppt（42页珍藏版）》请在三一办公上搜索。

1、第 14 讲 存储过程和触发器,一、存储过程 1 存储过程概述 2 存储过程的使用和管理 二、使用触发器 1 触发器概述 2 触发器的使用和管理 3 使用存储过程维护数据完整性,练习 写出 SELECT 语句,1 从 titles 表的 title_id,type,advance列中查询所有的 advance 不大于￥5500 的business 类型的书.2 从 titles 表查询出版日期（pubdate）在1/1/1991 12/31/1991 之间的书号(title_id)和出版日期（pubdate）.,答案,1 select title_id,type,advance from ti

2、tles where advance=￥5500 and type=business 2 select title_id,pubdate from titles where pubdate between 1/1/1991 and 12/31/1991,上机:实验 5 存储过程和触发器,1 P324 试一试 P327 试一试 P330 试一试1 P330 试一试2 P337 试一试*P339 试一试*P340 试一试,一、存储过程 1 存储过程概述,对于开发前端程序的开发者来说，存储过程无疑是很重要的工具，它可以使开发者在不了解表架构的情况下使用存储过程对数据进行处理。同时，由于存储过程具有封

3、装的概念，因此当逻辑规则改变时，可以在不影响前端程序设计的情况下对后端程序进行修改。,存储过程（Stored Procedure）是一组预先编译好的 T-SQL 代码。用户无需了解程序内部是如何撰写的，只需对其进行调用即可，所以它的定义很简单，就是一个包含T-SQL命令的程序对象。对于撰写存储过程的开发者或数据库管理员而言，撰写存储过程是为了避免用户自己使用查询数据，从而达到安全灵活高效和易于管理等目的。,使用存储过程的优点：执行速度快 在有大量批处理的 T-SQL 代码要重复执行时,使用存储过程可以极大地提高运行效率.模块化的程序设计 存储过程经过了一次创建以后，可以被无数次调用.用户可以独

4、立于应用程序而对存储过程进行修改.减少网络通讯量 调用存储过程只需要用一条语句.增强系统的安全性 禁止用户用代码或企业管理器访问数据.,存储过程从功能上可分为三种：系统存储过程 在 SQL Server 中将许多管理和查询活动都事先使用系统存储过程加以定义，用户或管理员只要执行系统存储过程就可以获得系统信息.系统存储过程在 SQL Server 服务启动时就被加载到内存中.扩展存储过程 管理员可以使用程序设计语言(如c语言)来建立自己的外部程序.扩展存储过程大都是以 XP 开头,放在 Master 数据库中.用户存储过程 通过撰写 T-SQL命令的方式实现.,表13-1 常用的系统存储过程,创

5、建存储过程 创建存储过程的简单语法如下：CREATE PROC procedure_name WITH RECOMPILE|ENCRYPTION AS sql_statement procedure_name:存储过程的名字.其命名必须符合规范.RECOMPILE：在执行完存储过程以后不在高速缓存里保留存储过程的备份.所以每次执行存储过程时都要对存储过程进行重新编译和优化.,2 存储过程的使用和管理,ENCRYPTION：存储过程作为数据库对象将在 syscomment 表中留下完整的代码等信息.使用 ENCRYPTION 参数以后将对访问这些数据的入口进行加密.问题是这种方法在加密时并没有使

6、用密钥,所以很容易破解.dOMNAR 的dSQLSRVD 实用程序(一个免费软件)，可以使具有 SysAdmin 角色的用户对加密的对象进行解密.,CREATE PROC 存储过程名称AS T-SQL命令,最简单的存储过程语法是,存储过程分为两类，一类是没有参数的存储过程，另一类是有参数的存储过程.,下例用于创建一个简单的存储过程，AS下面有一段 select 命令.,USE pubsGOCREATE PROC author_informationAS SELECT au_fname,phone,addressFROM authors ORDER BY City GO,用户也可以搭配条件选择.

7、,USE pubsGOCREATE PROC author_informationASIF USER=dbo SELECT*FROM authors ORDER BY City ELSESELECT au_id,City FROM authors ORDER BY CityGO,EXEC author_information,执行没有参数的存储过程相当简单:,但是没有参数的存储过程在实际使用时并不多见,因为存储过程在撰写时多作为函数使用,输入几个参数,经过存储过程运算后返回用户所要的数据.存储过程主要由三种类型的参数:输入参数;输出参数;返回值.,输入参数是在调用这个存储过程之前必须输入的参数

8、,而该参数仅在程序中使用,并不会再返回给用户.,USE pubsGOCREATE PROC USP_FindOrder OrderID intASSELECT OrderID,OrderDate,Status FROM Order where OrderID=OrderID,EXEC USP_FindOrder 43670,下例用于创建一个简单的存储过程，但这个存储过程中包含了复杂的查询语句：USE pubsGOCREATE PROCEDURE author_informationAS SELECT au_lname,au_fname,title,pub_nameFROM authors a

9、JOIN titleauthor taON a.au_id=ta.au_id JOIN titles tON t.title_id=ta.title_id JOIN publishers pON t.pub_id=p.pub_idGO,但加入下面一段语句会更好一些.USE pubs GO IF EXISTS(SELECT name FROM sysobjects WHERE name=au_info AND type=P)DROP PROCEDURE au_info GO 这个例子首先检查在表中是否存在名字相同的存储过程的记录，如果有则先删除原有的存储过程，再创建与之同名的存储过程.,可以使用

10、企业管理器创建存储过程：（1）在树型结构上选择相应的数据库（2）在展开的数据库中选中存储过程节点（3）右击，选新建存储过程（4）在对话框中,输入正确的 T-SQL 语句（5）单击检查语法按钮（6）单击确定按钮 系统允许创建临时存储过程：局部或全局临时存储过程，只要在存储过程前加“#”“#”.,管理存储过程 可以使用 sp_helptext 命令查看创建存储过程的文本信息,如：USE pubs GO sp_helptext byroyalty GO 演示 可用 sp_help 查看存储过程的一般信息.可用 sp_rename 修改存储过程的名字.,可以使用企业管理器浏览存储过程的信息:（1）在树

11、型结构上选定数据库（2）在展开的数据库中选中存储过程节点（3）在展开的列表中,选定某一存储过程（4）右击，选属性 这种方法可以修改存储过程的内容，并保存修改.可用 sp_depends 查看某个表被存储过程引用的情况.若想知道某个存储过程引用表的情况呢？,USE pubsGOsp_depends titlesGO,执行存储过程 如果对存储过程的调用是批处理的第一条语句，则可以直接使用存储过程的名字调用：USE pubs GO byroyalty GO 但下面的语句就不行了：USE pubs byroyalty GO,删除存储过程 删除存储过程的语法是 DROP PROCEDURE proced

12、ure n 例如：USE pubs GO DROP PROC reptq1 GO,二、使用触发器 1 触发器概述,触发器（trigger）是一种特殊的存储过程,它与表紧密相连，可以看作是表定义的一部分.触发器是表上的程序,主要提供数据添加、修改与删除后的程序处理方法,可以用来检查数据及进行数据更新,也可以分担一些前端应用程序撰写的逻辑规则.,触发器的英文名称是 Trigger,也就是扳机的意思.与枪支操作原理类似,只有扣动扳机,子弹才会飞出.将该词用在 SQL Server 环境中,表示当表发生了添加、删除、修改操作之后,才会执行的程序,这样的程序就称为触发器.,触发器的内容,也是 T-SQL

13、 命令的集合,但具有特性:触发器本身存于表中,当表被删除时,存于表中的触发器也一起被删除.触发器本身无法单独执行.对于存储过程或函数来说,用户可以单独执行.但触发器则不同,只有当表被添加、修改或删除时,系统才会执行触发操作.因此,执行触发器的方法对表进行添加、修改或删除操作.,触发器也难以调试,很多可能的逻辑错误都因触发器而引起.触发器本身参与事务.触发器当表被添加、修改或删除操作发生后才会被执行,所以如果数据都被删除了才去执行触发器,不是已经太晚了吗?其实不然，因为触发器本身会加入事务，可以回滚操作.这也会使事务时间延长.一个表的任何操作可以有多个触发器.但同一个操作的触发器不能有先后的顺序

14、的关系.,使用触发器的主要原因如下:对输入的数据进行进一步的检查.可以对表设置条件约束,进行数据检查.但更复杂的验证就可以使用触发器.比如,身份证号码字段,用条件约束能确定一些字符,但并没有办法确定这是一个正确的身份证号码.使用触发器来维护关联更新的操作.如级联删除.,使用触发器的主要原因如下:维护一个不具规范性的数据库.有时,为了提高效率,需要对表进行反规范化设计.但规范化是使用关系数据库的特性来维护数据间的一致性,若没有达到第三范式,则必须由用户自己使用程序来维护数据的一致性.商业逻辑规则的撰写.一般所谓的商业逻辑规则,主要是指程序执行的流程顺序.例如,当添加订单时,则要将相对的产品库存减

15、去销货数量,这就是商业逻辑规则.可放前端或数据库端.,例如，在 pubs 数据库里，存放着出版商(publishers)的信息、出版物的信息(titles)、出版物与作者关联的信息(titleauthor)以及作者信息(authors).现在有一条出版商的信息被删除了,则所有由该出版商出版的出版物都应该将 pub_id 修改为 NULL，或者删除有关的出版物信息.同样titleauthor 表中的信息也应该相应的得到修改.这样关联到三张表的一致性维护问题，可以使用触发器来实现.,设置 DELETE 触发器,又比如公司库存量（stock）必须保持在一定水平以内.用户可以设置一个触发器.这样当库存

16、量超过标准时，就会向数据库管理员发送信息以示警告.SQL Server 在有关触发器方面的功能非常灵活、强大，这是吸引数据库工程师选择使用SQL Server 的重要原因之一.在 SQL Server 中一张表可以有多个触发器.触发器可以容纳非常复杂的 T-SQL 语句.它是一个独立的单元，作为一个事物来执行.,储发器的主要功能：级联修改数据库的所有相关表.撤销或回滚违反引用完整性的操作.执行比检查约束（check）更复杂的约束操作.查找在数据修改前后的表状态之间的差别，并根据差别分别采取相应的措施.在一张表的同一类型的操作(INSERT、UPDATE 或 DELETE)上设置多个触发器，从而

17、可以针对同样的修改语句执行不同的多种操作.,注意：表的拥有者处理触发器的权限不许转授.可以在触发器中引用视图或临时表，但不能在视图、临时表或系统表上创建触发器.在执行修改语句时，触发器的执行是修改语句事物的一部分.所以，如果触发器执行不成功，则整个修改事物将会回滚.当使用约束、规则和默认值就可以实现预定的数据完整性时，应优先考虑使用这三种措施.TRUNCATE TABLE 虽然在功能上与删除操作类似,但是它不会触发DELECT触发器运行.,储发器用 CREATE 创建，DROP 删除,用ALTER 修改.相应的操作也可以由企业管理器进行.创建触发器的语句必须写在批处理的第一行.在创建触发器的语

18、句中也有一些禁止使用的 T-SQL 语句.触发器是特殊的存储过程，所以适合于存储过程的管理方式，都是适用于触发器.所以可以使用 sp_helptext,sp_help,sp_depends 等系统存储过程.,2 储发器的使用和管理,触发器的撰写方式相当简便,但在撰写时需要注意将触发器与表相连,因此需要指定表与表的操作才会执行触发器.触发器的简单语法如下.,CREATE TRIGGER 触发器名称 ON 表AFTER INSERT,UPDATE,DELETE AS T-SQL命令,USE NorthwindGOCREATE TRIGGER dEmp_Delete ON CustomerAFTER

19、 DELETEASIF(SELECT COUNT(*)FROM Deleted)1BEGIN RAISERROR(一次只能删除一个,16,1)ROLLBACK TRANSACTIONEND,下例创建了一个简单的触发器.USE Northwind IF EXISTS(SELECT name FROM sysobjects WHERE name=tr_product_update AND type=TR)DROP TRIGGER tr_product_update GO USE Northwind,GO CREATE TRIGGER tr_product_updateON products AFT

20、ER UPDATE AS DECLARE msg varchar(100)SELECT msg=str(rowcount)+“employees updated by this statement”PRINT msg RETURN GO 它返回 UPDATE 操作所修改的行数.,通常存储过程和触发器可以用来维护数据库引用的行动完整性，也就是在与外键值相应的主键发生改变以后规范对外键可能执行的操作，约束外键值的变化.用户应该记得 SQL Server 提供的外键约束的特点.当存在外键引用时，用户不能删除或修改被引用的主键值或 UNIQUE 列的值.使用存储过程可以实现受外键约束限制的主键或UNI

21、QUE 列的删除和修改.一般用级联操作.,3 使用存储过程维护数据完整性,当使用存储过程作级联修改时，可执行步骤:以新的主键值或 UNIQUE 列值向主表插入新的数据行，重复现存行的所有其他列的值.将依赖表中的外键值改为新值.删除主表中的旧数据行.当使用存储过程作级联删除时，可执行步骤:删除外键所在的行，或将外键修改为默认值或 NULL.删除主表中的行.使用存储过程来维护数据的完整性，并不是要替代原有的外键约束.只是外键约束的补充.,下例建立了一个存储过程实现级联删除.USE pubs GO CREATE PROC delete_title title_id tid AS BEGIN DELETE sales WHERE title_id=title_id DELETE titles WHERE title_id=title_id END GO,练习题 建立一个存储过程级联删除数据库pubs 的表 titles 和 titleauthor,当用户从表titles中删除记录时,将首先删除表titleauthor中的相关数据,两个表连接的条件是 title_id 相等.,