《存储过程和触发器课件》由会员分享，可在线阅读，更多相关《存储过程和触发器课件（34页珍藏版）》请在金锄头文库上搜索。
1、第7章 存储过程和触发器,7.1 存储过程 7.1.1 存储过程的概念 7.1.2 系统存储过程 7.1.3 用户自定义存储过程,在大型数据库中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL语句的结合。就本质而言，触发器也是一种存储过程。,7.1.1 存储过程的概念,存储过程（Store Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，供用户调用。 存储过程同其他编程语言中的过程或函数相似，有如下特点： 接受参数并以输出参数的形式将多个值返回 返回状态值，以表明成功或失败 在SQL中存储过程分为两类： 系统存储过程 用户自定义存储过程
2、,7.1.2 系统存储过程,在SQL Server中，有许多管理工作是通过执行系统存储过程来完成的。 系统过程以“sp_”为前缀，主要存储在master数据库中，但可以在任何数据库使用系统存储过程。,常用系统存储过程 sp_helpdb 数据库名：查看数据库的一般信息 sp_help 数据库对象名：查看数据库对象的一般信息 sp_helptext 数据库对象名：查看数据库对象的文本信息 sp_depends 数据库对象名：查看数据库对象的相关性 sp_rename 旧名，新名：修改数据库对象的名称 例：execute sp_help 学生,7.1.3 用户自定义存储过程,存储过程是一种数据库对
3、象。创建存储过程的方法：向导 企业管理器 SQL语句,1. 不带参数的存储过程 例1创建一个存储过程，用于查询货品book的订单信息。 create procedure sp1 as select * from orders where 货品名称=book go -注意:创建存储过程的语句必须在一个独立的批中 execute sp1 -执行存储过程 例2 （书P148）:在market数据库中建立一个名为st_cus的存储过程，用于检索客户信息与订单信息。,7.1.3 用户自定义存储过程,2. 带参数的存储过程 例1创建一个存储过程，显示某货品的订单信息。该存储过程带有一个参数，用于接受货品名
4、称。 （注意：形式参数的定义和实际参数的使用）,create procedure sp2 x varchar(6) as select * from orders where 货品名称=x go execute sp2 book -执行存储过程 -或者execute sp2 x=book,7.1.3 用户自定义存储过程,例2创建一个存储过程，显示某货品的订单信息。该存储过程带有一个参数，用于接受货品名称，默认值为“book”。（注意：默认值的设定）,create procedure sp3 x varchar(6)=book as select * from orders where 货品名称
5、=x go -执行存储过程 execute sp3 default -default关键字启用参数的默认值 -或者execute sp3 忽略具有默认值的参数，表示启用参数的默认值 -或者execute sp3 pen,7.1.3 用户自定义存储过程,3. 带输出参数的存储过程 例1创建一个存储过程，它带有一个输入参数，用于接受货品名称，带有一个输出参数，用于统计该货品的订单数。 （注意：输出参数的定义和使用）,create procedure sp4 x varchar(6),y int output as select y=count(*) from orders where 货品名称=x
6、 go -执行存储过程 declare z int execute sp4 book,z output select z,7.1.3 用户自定义存储过程,关于存储过程的返回值 任何存储过程都返回一个整数状态值，一般返回“”以表明调用成功。如果要返回其他值，在存储过程中写RETURN语句，但只能是整数值。,例1可以这样看到存储过程sp1的返回状态值： declare x int execute x=sp1 select x,7.1.3 用户自定义存储过程,关于存储过程的返回值 例2创建一个存储过程，如果找到北京的客户，则返回北京的客户数，否则返回0。,create procedure sp5 a
7、s declare x int select x=count(*) from customers where 地址 like 北京% return x go declare x int execute x=sp5 -执行存储过程 select x,7.1.3 用户自定义存储过程,存储过程的优点 1. 存储过程能够减少网络流量 2. 增强代码的重用性和共享性 3. 存储过程能够实现较快的执行速度 4. 存储过程保证安全性 5. 允许不同的客户端应用程序访问存储过程 存储过程的缺点 1. 存储过程不能实现复杂的逻辑操作 2. 存储过程的管理比较困难,执行字符串(略),EXECUTE语句的主要用途是
8、执行存储过程。除此外，可以执行字符串。 例1 execute (select * from orders) 例2 declare x varchar(50) set x= select * from orders execute (x) 例3 declare x varchar(50) set x= orders execute (select * from +x),7.2 触发器,7.2.1 触发器概述 7.2.2 创建和使用触发器 7.2.3 管理触发器,触发器是一种特殊类型的存储过程，它也是由T-SQL语句组成。,7.2.1 触发器概述,触发器的概念 触发器是一种特殊类型的存储过程，它也
9、是由T-SQL语句组成。 触发器与表紧密相连 触发器不能被直接调用，当对某表进行如UPDATE、INSERT、DELETE这些操作时，触发器被激发，SQL Server就会自动执行触发器所定义的SQL语句。 触发器没有参数,触发器的类型 SQL Server 2000支持两种类型的触发器： AFTER触发器 (重点掌握) INSTEAD OF触发器,两种类型的触发器(两种触发方式),AFTER触发器称为后触发器，就是在修改语句成功执行之后激活触发器；AFTER触发器只能在表上定义，不能创建在视图上；可以为每个触发操作（INSERT、UPDATE、DELETE）定义多个触发器 (默认先建的先触发
10、) 。 INSTEAD OF触发器称为替代触发器，就是在修改语句执行之前激活触发器，但并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅执行触发器本身。既可以在表上定义，也可以在视图上定义，但对每个触发操作只能定义一个INSTEAD OF触发器。,7.2.2 创建和使用触发器,1、使用企业管理器创建触发器 右击表，“所有任务”“管理触发器” 可以创建、查看、修改、删除触发器 2、使用SQL语句创建触发器 CREATE TRIGGER 触发器名 ON 表名|视图名 FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE AS SQL语句,7.2.
11、2 创建和使用触发器,例一个最简单的触发器 use market go create trigger tr1 on goods -创建触发器 after insert,delete,update as select * from goods go,-检验触发器的作用 insert goods(货品名称) values(computer) update goods set 库存量=10 where 货品名称=computer delete from goods where 货品名称=computer,INSERT触发器,例1 当向orders表中插入一条订单时，检查goods表中该订单中的货品是
12、否正在整理中，如是，则不能下订单。,CREATE TRIGGER tr2 ON orders AFTER INSERT AS declare y varchar(6) select y=货品名称 from inserted if (select 状态 from goods where 货品名称=y)=1 begin print 货品正在整理中，不能下订单 rollback transaction -事务回滚 end go insert orders(货品名称,客户编号,数量) values (pen,2,5),INSERT触发器,例2 当向orders表中插入一条订单时，检查goods表中该订
13、单中的库存量够不够，如不够，则不能下订单；如够，则还要减去goods表中相应的库存量。,CREATE TRIGGER tr3 ON orders AFTER INSERT AS declare x int,y varchar(6),z int select y=货品名称,z=数量 from inserted select x=库存量 from goods where 货品名称=y if xz begin print 库存不够，不能下订单 rollback transaction end else update goods set 库存量=库存量-z where 货品名称=y,说 明,注意：前2
14、个触发器都是建立在orders表上的，都是INSERT触发器。谁触发？谁先谁后？,结论：可以为每个触发操作（INSERT、UPDATE和DELETE）定义多个触发器 ，默认先建的先触发，但是，其中任何一个触发器执行了rollback transaction语句，则不再有其他触发器被执行，且全部回滚。,INSERT触发器,重要说明 INSERTED表是触发器被激发时产生的临时表，触发器执行完成后，这个表被删除。要想看到它的内容，例： create trigger temp1 on orders after insert as select * from inserted go insert or
15、ders(货品名称,客户编号,数量) values(pen,2,5),rollback transaction 事务回滚 触发器和触发它的语句是一个事务，执行rollback transaction后，整个事务将回滚。这是数据完整性的一种保护机制。,INSERT触发器能够参考数据库中其他表的信息，从而实现CHECK约束不能实现的、更复杂的数据完整性。,DELETE触发器,例1 删除goods表中的货品时，将这些货品在orders中的订单也全部删除。,CREATE TRIGGER tr4 ON goods AFTER DELETE AS delete orders where 货品名称 in (
16、select 货品名称 from deleted) go -检验触发器的作用 delete goods where 货品名称=pen -有外键约束,注意： 约束前反应，触发器后反应； 要实现级联删除(级联修改)可以使用触发器或外键约束。,DELETE触发器,例2 不能删除有订单的客户。 CREATE TRIGGER tr5 ON customers AFTER DELETE AS if exists(select * from orders where 客户编号 in (select 客户编号 from deleted) begin print 不能删除有订单的客户 rollback transaction end go -检验触发器的作用 delete customers where 客户编号=2 -有外键约束,DELETE触发器,问题 TRUNCATE TABLE语句删除表中所有记录，那么该语句会激活DELETE触发器吗？,答案 TRUNCATE TABLE语句的操作不记入事务日志，所以它不会激活DELETE触发器
