【教学课件】第7章存储过程和触发器.ppt

《【教学课件】第7章存储过程和触发器.ppt》由会员分享，可在线阅读，更多相关《【教学课件】第7章存储过程和触发器.ppt（34页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、信息技术系信息技术系第第7章章 存储过程和触发器存储过程和触发器 7.1 存储过程存储过程7.1.1 存储过程的概念存储过程的概念7.1.2 系统存储过程系统存储过程7.1.3 用户自定义存储过程用户自定义存储过程在大型数据库中，存储过程和触发器具有很重要在大型数据库中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是的作用。无论是存储过程还是触发器，都是SQL语句的语句的结合。就本质而言，触发器也是一种存储过程。结合。就本质而言，触发器也是一种存储过程。信息技术系信息技术系7.1.1 存储过程的概念存储过程的概念 存存储储过过程程（Store Procedure）是是一一组组

2、为为了了完完成成特特定定功功能的能的SQL语句集，语句集，经编译后存储在数据库中经编译后存储在数据库中，供用户调用。，供用户调用。存存储储过过程程同同其其他他编编程程语语言言中中的的过过程程或或函函数数相相似似，有有如如下下特点：特点：n接受参数并以输出参数的形式将多个值返回接受参数并以输出参数的形式将多个值返回n返回状态值，以表明成功或失败返回状态值，以表明成功或失败 在在SQL中存储过程分为两类：中存储过程分为两类：n系统存储过程系统存储过程n用户自定义存储过程用户自定义存储过程信息技术系信息技术系7.1.2 系统存储过程系统存储过程在在SQL Server中中，有有许许多多管管理理工工作

3、作是是通通过过执执行行系统存储过程来完成的。系统存储过程来完成的。系系 统统 过过 程程 以以“sp_”为为 前前 缀缀，主主 要要 存存 储储 在在master数数据据库库中中，但但可可以以在在任任何何数数据据库库使使用用系系统统存存储过程。储过程。常用系统存储过程常用系统存储过程sp_helpdb 数据库名数据库名：查看数据库的一般信息：查看数据库的一般信息sp_help 数据库对象名数据库对象名：查看数据库对象的一般信息查看数据库对象的一般信息sp_helptext 数据库对象名数据库对象名：查看数据库对象的文本信息：查看数据库对象的文本信息sp_depends 数据库对象名数据库对象名

4、：查看数据库对象的相关性：查看数据库对象的相关性sp_rename 旧名，新名旧名，新名：修改数据库对象的名称：修改数据库对象的名称例：例：execute sp_help 学生学生信息技术系信息技术系7.1.3 用户自定义存储过程用户自定义存储过程 存存储储过过程程是是一一种种数数据据库库对对象象。创创建建存存储储过过程程的的方方法法：向导向导 企业管理器企业管理器 SQL语句语句1.不带参数的存储过程不带参数的存储过程例例1创建一个存储过程，用于查询货品创建一个存储过程，用于查询货品book的订单信息。的订单信息。create procedure sp1 as select*from ord

5、ers where 货品名称货品名称=bookgo -注意注意:创建存储过程的语句必须在一个独立的批中创建存储过程的语句必须在一个独立的批中execute sp1 -执行存储过程执行存储过程例例2（书（书P148）:在在market数据库中建立一个名为数据库中建立一个名为st_cus的存储过程，用于检索客户信息与订单信息。的存储过程，用于检索客户信息与订单信息。信息技术系信息技术系7.1.3 用户自定义存储过程用户自定义存储过程2.带参数的存储过程带参数的存储过程例例1创建一个存储过程，显示某货品的订单信息。该存储创建一个存储过程，显示某货品的订单信息。该存储过程带有一个参数，用于接受货品名称

6、。过程带有一个参数，用于接受货品名称。（注意：形式参数的定义和实际参数的使用）（注意：形式参数的定义和实际参数的使用）create procedure sp2 x varchar(6)as select*from orders where 货品名称货品名称=xgoexecute sp2 book -执行存储过程执行存储过程-或者或者execute sp2 x=book形参的定义形参的定义实参的使用实参的使用信息技术系信息技术系7.1.3 用户自定义存储过程用户自定义存储过程例例2创建一个存储过程，显示某货品的订单信息。该存创建一个存储过程，显示某货品的订单信息。该存储过程带有一个参数，用于接受

7、货品名称，默认值为储过程带有一个参数，用于接受货品名称，默认值为“book”。（注意：默认值的设定）（注意：默认值的设定）create procedure sp3 x varchar(6)=book as select*from orders where 货品名称货品名称=xgo-执行存储过程执行存储过程execute sp3 default -default关键字启用参数的默认值关键字启用参数的默认值-或者或者execute sp3 忽略具有默认值的参数，表示启用参数忽略具有默认值的参数，表示启用参数的默认值的默认值-或者或者execute sp3 pen信息技术系信息技术系7.1.3 用户

8、自定义存储过程用户自定义存储过程3.带输出参数的存储过程带输出参数的存储过程例例1创建一个存储过程，它带有一个输入参数，用于接受创建一个存储过程，它带有一个输入参数，用于接受货品名称，带有一个输出参数，用于统计该货品的订单数。货品名称，带有一个输出参数，用于统计该货品的订单数。（注意：输出参数的定义和使用）（注意：输出参数的定义和使用）create procedure sp4 x varchar(6),y int output as select y=count(*)from orders where 货品名称货品名称=xgo-执行存储过程执行存储过程declare z intexecute

9、sp4 book,z outputselect z信息技术系信息技术系7.1.3 用户自定义存储过程用户自定义存储过程关于存储过程的返回值关于存储过程的返回值 任何存储过程都返回一个任何存储过程都返回一个整数整数状态值，一般返回状态值，一般返回“”以表明调用成功。如果要返回其他值，在存储过程以表明调用成功。如果要返回其他值，在存储过程中写中写RETURN语句，但只能是语句，但只能是整数整数值。值。例例1可以这样看到存储过程可以这样看到存储过程sp1的返回状态值：的返回状态值：declare x intexecute x=sp1select x信息技术系信息技术系7.1.3 用户自定义存储过程用

10、户自定义存储过程关于存储过程的返回值关于存储过程的返回值例例2创建一个存储过程，如果找到北京的客户，则返回创建一个存储过程，如果找到北京的客户，则返回北京的客户数，否则返回北京的客户数，否则返回0。create procedure sp5 as declare x int select x=count(*)from customers where 地址地址 like 北京北京%return xgodeclare x intexecute x=sp5 -执行存储过程执行存储过程select x信息技术系信息技术系7.1.3 用户自定义存储过程用户自定义存储过程存储过程的优点存储过程的优点1.存储

11、过程能够减少网络流量存储过程能够减少网络流量2.增强代码的重用性和共享性增强代码的重用性和共享性 3.存储过程能够实现较快的执行速度存储过程能够实现较快的执行速度4.存储过程保证安全性存储过程保证安全性5.允许不同的客户端应用程序访问存储过程允许不同的客户端应用程序访问存储过程存储过程的缺点存储过程的缺点1.存储过程不能实现复杂的逻辑操作存储过程不能实现复杂的逻辑操作2.存储过程的管理比较困难存储过程的管理比较困难信息技术系信息技术系执行字符串执行字符串(略略)EXECUTE语句的主要用途是执行存储过程。除此外，可语句的主要用途是执行存储过程。除此外，可以执行字符串。以执行字符串。例例1 ex

12、ecute(select*from orders)例例2 declare x varchar(50)set x=select*from ordersexecute(x)例例3 declare x varchar(50)set x=ordersexecute(select*from+x)信息技术系信息技术系7.2 触发器触发器 7.2.1 触发器概述触发器概述7.2.2 创建和使用触发器创建和使用触发器7.2.3 管理触发器管理触发器触发器是一种特殊类型的存储过程，它也是由触发器是一种特殊类型的存储过程，它也是由T-SQL语句组成。语句组成。信息技术系信息技术系7.2.1 触发器概述触发器概述触

13、发器的概念触发器的概念触发器是一种特殊类型的存储过程，它也是由触发器是一种特殊类型的存储过程，它也是由T-SQL语语句组成。句组成。触发器与表紧密相连触发器与表紧密相连触发器不能被直接调用，当对某表进行如触发器不能被直接调用，当对某表进行如UPDATE、INSERT、DELETE这些操作时，触发器被激发，这些操作时，触发器被激发，SQL Server就会自动执行触发器所定义的就会自动执行触发器所定义的SQL语句。语句。触发器没有参数触发器没有参数触发器的类型触发器的类型SQL Server 2000支持两种类型的触发器：支持两种类型的触发器：AFTER触发器触发器 (重点掌握重点掌握)INST

14、EAD OF触发器触发器信息技术系信息技术系两种类型的触发器两种类型的触发器(两种触发方式两种触发方式)AFTER触触发发器器称称为为后后触触发发器器，就就是是在在修修改改语语句句成成功功执执行行之之后后激激活活触触发发器器；AFTER触触发发器器只只能能在在表表上上定定义义，不不能能创创建建在在视视图图上上；可可以以为为每每个个触触发发操操作作（INSERT、UPDATE、DELETE）定定义义多多个个触触发发器器(默默认认先先建建的的先先触触发发)。INSTEAD OF触触发发器器称称为为替替代代触触发发器器，就就是是在在修修改改语语句句执执行行之之前前激激活活触触发发器器，但但并并不不执

15、执行行其其所所定定义义的的操操作作（INSERT、UPDATE、DELETE），而而仅仅执执行行触触发发器器本本身身。既既可可以以在在表表上上定定义义，也也可可以以在在视视图图上上定定义义，但但对对每个触发操作只能定义一个每个触发操作只能定义一个INSTEAD OF触发器。触发器。信息技术系信息技术系7.2.2 创建和使用触发器创建和使用触发器1、使用企业管理器创建触发器、使用企业管理器创建触发器 右击表，右击表，“所有任务所有任务”“管理触发器管理触发器”可以创建、查看、修改、删除触发器可以创建、查看、修改、删除触发器2、使用、使用SQL语句创建触发器语句创建触发器 CREATE TRIGG

16、ER 触发器名触发器名 ON 表名表名|视图名视图名 FOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEASSQL语句语句信息技术系信息技术系7.2.2 创建和使用触发器创建和使用触发器例例一个最简单的触发器一个最简单的触发器 use marketgocreate trigger tr1 on goods -创建触发器创建触发器after insert,delete,updateasselect*from goodsgo-检验触发器的作用检验触发器的作用insert goods(货品名称货品名称)values(computer)update goods set 库

17、存量库存量=10 where 货品名称货品名称=computerdelete from goods where 货品名称货品名称=computer信息技术系信息技术系INSERT触发器触发器例例1 当当向向orders表表中中插插入入一一条条订订单单时时，检检查查goods表表中中该该订单中的货品是否正在整理中，如是，则不能下订单。订单中的货品是否正在整理中，如是，则不能下订单。CREATE TRIGGER tr2 ON ordersAFTER INSERTASdeclare y varchar(6)select y=货品名称货品名称 from insertedif(select 状态状态 f

18、rom goods where 货品名称货品名称=y)=1 begin print 货品正在整理中，不能下订单货品正在整理中，不能下订单 rollback transaction -事务回滚事务回滚 endgoinsert orders(货品名称货品名称,客户编号客户编号,数量数量)values(pen,2,5)信息技术系信息技术系INSERT触发器触发器例例2 当当向向orders表表中中插插入入一一条条订订单单时时，检检查查goods表表中中该该订订单单中中的的库库存存量量够够不不够够，如如不不够够，则则不不能能下下订订单单；如如够够，则还要减去则还要减去goods表中相应的库存量。表中相

19、应的库存量。CREATE TRIGGER tr3 ON ordersAFTER INSERTASdeclare x int,y varchar(6),z intselect y=货品名称货品名称,z=数量数量 from insertedselect x=库存量库存量 from goods where 货品名称货品名称=yif x 0 and 年龄年龄=30就全部失败就全部失败信息技术系信息技术系补充：事务的特性补充：事务的特性ACID1、原子性、原子性(ATOMICITY):事务包含的一组操作是不可分的，要么全做，要么全事务包含的一组操作是不可分的，要么全做，要么全不做。不做。2、一致性、一致

20、性(CONSISTENCY):事务执行的结果应使数据库从一个一致性状态转换到事务执行的结果应使数据库从一个一致性状态转换到另一个一致性状态。例如，银行转帐工作。另一个一致性状态。例如，银行转帐工作。3、隔离性、隔离性(ISOLATION):如果有多个事务并发执行，应使每个事务的执行不被如果有多个事务并发执行，应使每个事务的执行不被其他事务干扰。并发控制就是保证事务的隔离性（独立性）其他事务干扰。并发控制就是保证事务的隔离性（独立性）。4、持久性、持久性(DURABILITY):一个事务一旦成功完成，它对数据库中数据的改变就一个事务一旦成功完成，它对数据库中数据的改变就应该是永久的。应该是永久的。