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

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, adv

2、ance from titles 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 开头, 放在 Ma

5、ster 数据库中. 用户存储过程 通过撰写 T-SQL命令的方式实现.,表13-1 常用的系统存储过程, 创建存储过程 创建存储过程的简单语法如下： CREATE PROC procedure_name WITH RECOMPILE|ENCRYPTION AS sql_statement procedure_name: 存储过程的名字. 其命名 必须符合规范. RECOMPILE：在执行完存储过程以后不在 高速缓存里保留存储过程的备份. 所以每次执行 存储过程时都要对存储过程进行重新编译和优化.,2 存储过程的使用和管理,ENCRYPTION：存储过程作为数据库对 象将在 syscommen

6、t 表中留下完整的代码等 信息. 使用 ENCRYPTION 参数以后将对访 问这些数据的入口进行加密. 问题是这种方法在加密时并没有使用密 钥, 所以很容易破解. dOMNAR 的 dSQLSRVD 实用程序(一个免费软件)，可以 使具有 SysAdmin 角色的用户对加密的对象 进行解密. ,CREATE PROC 存储过程名称 AS T-SQL命令,最简单的存储过程语法是,存储过程分为两类，一类是没有参数的 存储过程，另一类是有参数的存储过程.,下例用于创建一个简单的存储过程，AS下 面有一段 select 命令.,USE pubs GO CREATE PROC author_infor

7、mation AS SELECT au_fname, phone, address FROM authors ORDER BY City GO,用户也可以搭配条件选择.,USE pubs GO CREATE PROC author_information AS IF USER=dbo SELECT * FROM authors ORDER BY City ELSE SELECT au_id, City FROM authors ORDER BY City GO,EXEC author_information,执行没有参数的存储过程相当简单:,但是没有参数的存储过程在实际使用时 并不多见, 因为

8、存储过程在撰写时多作为函 数使用, 输入几个参数, 经过存储过程运算 后返回用户所要的数据. 存储过程主要由三种类型的参数: 输入参数; 输出参数; 返回值.,输入参数是在调用这个存储过程之前必须 输入的参数, 而该参数仅在程序中使用, 并不 会再返回给用户.,USE pubs GO CREATE PROC USP_FindOrder OrderID int AS SELECT OrderID,OrderDate,Status FROM Order where OrderID = OrderID,EXEC USP_FindOrder 43670,下例用于创建一个简单的存储过程，但这 个存储过程

9、中包含了复杂的查询语句： USE pubs GO CREATE PROCEDURE author_information AS SELECT au_lname, au_fname, title, pub_name FROM authors a JOIN titleauthor ta ON a.au_id=ta.au_id JOIN titles t ON t.title_id=ta.title_id JOIN publishers p ON t.pub_id=p.pub_id GO,但加入下面一段语句会更好一些. USE pubs GO IF EXISTS( SELECT name FROM

10、sysobjects WHERE name=au_info AND type=P) DROP PROCEDURE au_info GO 这个例子首先检查在表中是否存在名字相同 的存储过程的记录， 如果有则先删除原有的存储 过程，再创建与之同名的存储过程.,可以使用企业管理器创建存储过程： （1）在树型结构上选择相应的数据库 （2）在展开的数据库中选中存储过程节点 （3）右击，选新建存储过程 （4）在对话框中, 输入正确的 T-SQL 语句 （5）单击检查语法按钮 （6）单击确定按钮 系统允许创建临时存储过程：局部或全局 临时存储过程，只要在存储过程前加“#”“#”., 管理存储过程 可以使用

11、sp_helptext 命令查看创建存储 过程的文本信息, 如： USE pubs GO sp_helptext byroyalty GO 演示 可用 sp_help 查看存储过程的一般信息. 可用 sp_rename 修改存储过程的名字.,可以使用企业管理器浏览存储过程的信息: （1）在树型结构上选定数据库 （2）在展开的数据库中选中存储过程节点 （3）在展开的列表中, 选定某一存储过程 （4）右击，选属性 这种方法可以修改存储过程的内容，并保 存修改. 可用 sp_depends 查看某个表被存储过程 引用的情况. 若想知道某个存储过程引用表的情况呢？,USE pubs GO sp_dep

12、ends titles GO, 执行存储过程 如果对存储过程的调用是批处理的第一条 语句，则可以直接使用存储过程的名字调用： USE pubs GO byroyalty GO 但下面的语句就不行了： USE pubs byroyalty GO, 删除存储过程 删除存储过程的语法是 DROP PROCEDURE procedure n 例如： USE pubs GO DROP PROC reptq1 GO,二、使用触发器 1 触发器概述,触发器（trigger）是一种特殊的存储过程, 它与表紧密相连，可以看作是表定义的一部分. 触发器是表上的程序, 主要提供数据添加、修改 与删除后的程序处理方法

13、, 可以用来检查数据及 进行数据更新, 也可以分担一些前端应用程序撰 写的逻辑规则.,触发器的英文名称是 Trigger , 也就是扳 机的意思. 与枪支操作原理类似, 只有扣动扳机, 子弹才会飞出. 将该词用在 SQL Server 环境中, 表示当表发生了添加、删除、修改操作之后, 才 会执行的程序, 这样的程序就称为触发器.,触发器的内容, 也是 T-SQL 命令的集合, 但具有特性: 触发器本身存于表中, 当表被删除时, 存 于表中的触发器也一起被删除. 触发器本身无法单独执行. 对于存储过程 或函数来说, 用户可以单独执行. 但触发器则不 同, 只有当表被添加、修改或删除时, 系统才

14、会 执行触发操作. 因此, 执行触发器的方法对表进行添加、修 改或删除操作.,触发器也难以调试, 很多可能的逻辑错误都 因触发器而引起. 触发器本身参与事务. 触发器当表被添 加、修改或删除操作发生后才会被执行, 所以如 果数据都被删除了才去执行触发器, 不是已经太 晚了吗? 其实不然，因为触发器本身会加入事 务，可以回滚操作. 这也会使事务时间延长. 一个表的任何操作可以有多个触发器. 但 同一个操作的触发器不能有先后的顺序的关系.,使用触发器的主要原因如下: 对输入的数据进行进一步的检查. 可以对 表设置条件约束, 进行数据检查. 但更复杂的验 证就可以使用触发器. 比如, 身份证号码字段

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

16、(publishers) 的信息、出版物的信息(titles)、出版 物与作者关联的信息 (titleauthor) 以及作者信息 (authors). 现在有一条出版商的信息被删除了, 则 所有由该出版商出版的出版物都应该将 pub_id 修改为 NULL，或者删除有关的出版物信息. 同样 titleauthor 表中的信息也应该相应的得到修改. 这 样关联到三张表的一致性维护问题，可以使用触发 器来实现.,设置 DELETE 触发器,又比如公司库存量（stock）必须保持在一 定水平以内. 用户可以设置一个触发器. 这样当 库存量超过标准时，就会向数据库管理员发送信 息以示警告. SQL

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

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

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

20、_Delete ON Customer AFTER DELETE AS IF (SELECT COUNT(*) FROM Deleted)1 BEGIN RAISERROR(一次只能删除一个,16,1) ROLLBACK TRANSACTION END,下例创建了一个简单的触发器. 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_update ON products AFTER UPDATE AS DECLARE msg varchar(100) SELECT msg=str(rowcount)+ “employees updated by this statement” PRINT msg RETURN GO 它返回 UPDATE 操作所修改