数据库存储过程与触发器精.ppt

《数据库存储过程与触发器精.ppt》由会员分享，可在线阅读，更多相关《数据库存储过程与触发器精.ppt（45页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库存储过程与触发器第1页，本讲稿共45页本章学习目标本章学习目标l l 理解存储过程和触发器的作用理解存储过程和触发器的作用l l 熟练创建和管理存储过程及触发器熟练创建和管理存储过程及触发器l l 灵活运用存储过程和触发器提高系统开灵活运用存储过程和触发器提高系统开发效率发效率第2页，本讲稿共45页 8.1 8.1 存储过程存储过程8.1.1 8.1.1 存储过程基础知识存储过程基础知识1 1存储过程简介存储过程简介 存储过程是存储在服务器上的由SQL语句和控制流语句组成的一个预编译集合。存储过程划分为系统存储过程以及用户存储过程。一个存储过程可以作为一个独立的单元进行处理。在SQL S

2、erver中可以通过存储过程来完成很多管理任务，利用一些常用的系统存储过程可以很方便的查看、操作数据库对象。系统存储过程的命名通常以”sp_”作为前缀，并且存储于Master数据库中，如果用户拥有足够的权限，就可以在任何数据库中调用这些存储过程。存储过程提供了一种封装某一个需要重复执行任务的方法。一旦定义了一个存储过程之后，在应用程序中就可以对其进行调用。在存储过程中，不仅可以包含程序流、逻辑以及对数据库的查询，而且也可以接受参数、输出参数、返回单个或多个结果集。第3页，本讲稿共45页 所以通过设计自定义用户存储过程，用户可以使其实现强大的编程功能。使用存储过程可以比单独的SQL语句完成更为复

3、杂的功能，并且系统会对存储过程中的SQL语句进行了预编译处理，使得执行速度有了大幅度的提升。存储过程被第一次调用后，会保存在高速缓冲区中，这样再次执行同一个存储过程时，会提高了重复调用的效率。在实际应用中，可以将复杂的商业规则封装在存储过程中，从而提高程序语句的利用率。2存储过程的优点存储过程的优点存储过程一旦执行一次后，其执行的计划就会驻留在计算机的高速缓冲存储器中。其后对同一个存储过程的调用就可以直接利用编译后在高速缓存中的二进制形式来完成操作。可以在单个存储过程中执行一系列SQL语句，因而可以用于设计、封装企业的功能模块。应用程序使用相同的存储过程进行操作，确保了在数据访问、操纵的一致性

4、。第4页，本讲稿共45页可以在当前的存储过程内部引用其它存储过程，这样可以将复杂语句进行简化。提供了一种数据库访问的途径，只需要用户拥有执行存储过程的权限，那么通过使用存储过程就可以完成对数据库的各种操作，如添加数据、修改数据、删除数据等，而不需要考虑用户是否拥有存储过程所处理的数据对象的访问权限。3使用存储过程的注意事项使用存储过程的注意事项在命名自定义存储过程时避免与系统存储过程名相同。存储过程最多能够支持32层的嵌套。命名存储过程中的标识符时，长度不能超过128个字符。存储过程中参数的个数不能超过2100。第5页，本讲稿共45页8.1.2 创建和执行存储过程创建和执行存储过程在SQL S

5、erver中，既可以通过企业管理器，也可以通过使用CREATE PROCEDRUE语句的方式来创建存储过程。1使用企业管理器创建存储过程使用企业管理器创建存储过程步骤：在企业管理器中，展开服务器组、服务器节点、数据库节点。选择某一个数据库并在该节点上点击鼠标右键，选择【新建】菜单下的【存储过程】级联菜单，将弹出如图8-1所示的对话框。图8-1新建存储过程对话框第6页，本讲稿共45页在【存储过程属性】对话框中的【文本】编辑框中，对OWNER以及PROCEDURE NAME部分进行修改，分别修改为该存储过程的所有者和存储过程名称，并且输入该存储过程所包含的SQL语句。如图8-2所示为创建了一个用于

6、查询学生信息的存储过程。图8-2用于查询学生信息的存储过程第7页，本讲稿共45页如果需要验证存储过程中的SQL语句的正确性，可以单击【检查语法】按钮，如果没有语法错误则会弹出如图8-3所示的“语法检查成功”的对话框。图8-3“语法检查成功”的提示对话框当存储过程中没有语法错误后，可以单击【确定】按钮来 保存自定义的存储过程。第8页，本讲稿共45页2使用使用CREATE PROCEDURE语句来创建存储过程语句来创建存储过程该语句可以创建永久使用的存储过程，也可以创建局部临时过程、全局临时过程。语法格式为：CREATE PROCEDURE 存储过程名称;数值参数 数据类型 VARYING=参数的

7、默认值 OUTPUT,n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS SQL语句 .n 其中：存储过程名称 必须符合标识符命名规则，而且对于数据库以及所有者命名必须唯一。一个完整的命名不能超过128个字符。第9页，本讲稿共45页【例8-1】使用CREATE PROCEDURE语句创建一个名称为proc_bjrs的存储过程，用于检索现有班级及人数。CREATE PROCEDURE proc_bjrs AS SELECT DISTINCT(班级表班级表.班级编号班级编号),班级表班级表.班级班级 名称，名称，人

8、数人数=COUNT(学生基本信息表学生基本信息表.学号学号)FROM 学生基本信息表学生基本信息表,班级表班级表 WHERE 班级表班级表.班级编号班级编号=学生基本信息表学生基本信息表.班级编号班级编号 GROUP BY 班级表班级表.班级编号班级编号,班级表班级表.班级名称班级名称GO在查询分析器中执行以上命令后，然后执行该存储过程在查询分析器中执行以上命令后，然后执行该存储过程EXECUTE proc_bjrs，运行结果如图，运行结果如图8-4所示。所示。第10页，本讲稿共45页图8-4 检索现有班级以及人数第11页，本讲稿共45页【例例8-2】设计一个带有参数的存储过程，该参数用于传递

9、班级编号，根据该参数在学生基本信息表中检索出某一个班级中所有学生的信息。CREATE PROCEDURE proc_bjcx bjbh VARCHAR(8)AS SELECT*FROM 学生基本信息表 WHERE 班级编号=bjbhGO其中参数bjbh用于接收班级编号，在实际调用中根据该参数将会返回一个结果集。例如，EXECUTE proc_bjcx 20051003，将会显示班级编号为20051003的学生信息。运行结果如图8-5所示第12页，本讲稿共45页图8-5 用于传递班级编号的存储过程第13页，本讲稿共45页8.1.3 修改与删除存储过程修改与删除存储过程在SQL Server中，可

10、以通过企业管理器或SQL语句两种方式修改或删除存储过程。1使用企业管理器修改存储过程使用企业管理器修改存储过程在企业管理器中，展开服务器组、服务器以及数据库节点。选择某一个数据库，展开该数据库节点后点击【存储过程】，在右侧的窗口中将会显示出存储过程的列表。右击需要修改的存储过程，在弹出菜单中选择【属性】，弹出存储过程属性对话框，如图8-6所示。图8-6 【存储过程属性】对话框第14页，本讲稿共45页在【存储过程属性】对话框的【文本】编辑框中修改包含的SQL语句。需要注意的是，存储过程的名称不能修改。在检查了语法的正确性之后，可以单击【确定】按钮保存修改并关闭对话框。2使用企业管理器重命名存储过

11、程使用企业管理器重命名存储过程在企业管理器中，展开服务器组、服务器以及数据库节点。选择某一个数据库，展开该数据库节点后点击【存储过程】，在右侧的窗口中将会显示出存储过程的列表。右击需要重命名的存储过程，并选择弹出菜单中的【重命名】命令，如图8-7所示。第15页，本讲稿共45页图8-7 弹出菜单中的【重命名】命令重新输入存储过程的名称后，按下回车键完成修改。第16页，本讲稿共45页3使用使用ALTER PROCEDURE语句修改存储过程语句修改存储过程ALTER PROCEDURE语句可以对数据库中已有的存储过程进行修该，但不会更改权限，也不影响相关的存储过程或触发器。该语句的语法格式为：ALT

12、ER PROCEDURE 存储过程名称;数值参数 数据类型 VARYING=参数的默认值 OUTPUT,n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS SQL语句 .n 其中，存储过程名称为数据库中已经存在的过程名，语法中的常用选项与CREATE PROCEDURE语句一致。第17页，本讲稿共45页【例例8-3】修改已经创建的存储过程proc_bjcx及其功能，将其修改为可以根据系部编号来查询某一个系部中所有学生的信息。修改后将存储过程名称重命名为proc_xbcx。在查询分析器中运行如下命令，即可完成重命

13、名：ALTER PROCEDURE proc_bjcx xbbh VARCHAR(2)AS SELECT*FROM 学生基本信息表 WHERE 班级编号 IN(SELECT 班级编号 FROM 班级表 WHERE 系部编号=xbbh)GOSP_RENAME proc_bjcx,proc_xbcxGO第18页，本讲稿共45页使用SP_RENAME可以重新命名一个现有的存储过程。SP_RENAME的语法格式为：SP_RENAME 原名称,新名称。完成修改后查询系部编号为01的学生信息。EXEC proc_xbcx 014使用企业管理器删除存储过程使用企业管理器删除存储过程在企业管理器中，展开服务器

14、组、服务器以及数据库节点。选择某一个数据库，展开该数据库后点击【存储过程】，在右侧的窗口中显示出存储过程的列表。右击需要删除的存储过程，并在弹出菜单中选择【删除】命令，将弹出【除去对象】对话框，如图8-8所示。在【除去对象】对话框中单击【全部除去】按钮将会完成对存储过程的删除操作。第19页，本讲稿共45页图8-8 【除去对象】对话框第20页，本讲稿共45页5使用使用DROP PROCEDURE语句删除存储过程语句删除存储过程使用DROP PROCEDURE语句可以从当前的数据库中删除一个或多个存储过程。DROP PROCEDURE语句的语法格式为：DROP PROCEDURE 存储过程名称 ,

15、.n【例8-4】同时删除当前数据库中的两个存储过程，proc_a和proc_b。在查询分析器中运行如下命令，即可完成删除操作：DROP PROCEDURE proc_a,proc_bGO第21页，本讲稿共45页8.2 8.2 触发器触发器8.2.1 触发器及其作用触发器及其作用1触发器简介触发器简介 触发器属于一种特殊的存储过程，可以在其中包含复杂的SQL语句。触发器与存储过程的区别在于触发器能够自动执行并且不含有参数。通常可以在触发器内编写一段自动执行的程序，用于保证数据操作的完整性，从而扩展了对默认值、约束和规则的完整性检查。对表进行包括添加数据、删除数据、更新数据中的一种或多种操作时，触

16、发器就会自动执行。触发器可以划分为三种类别：INSERT触发器、DELETE触发器、UPDATE触发器。这三种触发器分别在发生数据的添加、删除、修改行为时自动执行。第22页，本讲稿共45页2触发器的优点触发器的优点触发器有助于在添加、更新或删除表中的记录时保留表之间已定义的关系。触发器可以自动调用，当发生了对数据所作的任何修改时，与之相关的触发器就会立刻被激活。可以完成数据库中相关表之间的级联修改。级联修改是指为了保证数据之间的逻辑性以及依赖关系，在对一张表进行修改的同时，其他表中需要进行的修改能够自动实现。触发器可以对需要存储的数据加以限制，并且能够实现比CHECK约束更为复杂的功能。在CH

17、ECK约束中不允许引用其他表中的列，而触发器可以引用。3触发方式触发方式 触发器的触发方式可以分为后触发和替代触发两种方式。后触发：指能够引发触发器的修改操作在完成之后才执行触发器的行为方式。创建该类触发器，需要使用AFTER关键字或者FOR关键字。第23页，本讲稿共45页替代触发：指定执行一个触发器，而不是执行SQL语句，这种替代触发语句的方式称为替代触发方式。8.2.3 创建触发器创建触发器可以使用企业管理器以及CREATE TRIGGER语句两种方式来创建触发器。1使用企业管理器创建触发器使用企业管理器创建触发器打开企业管理器，在控制台根目录下依次展开服务器组、服务器节点、数据库节点。选

18、择某一个数据库，并双击【表】节点，在右侧窗口的列表中选择其中的一张表。右击所选中的表，在弹出的下拉菜单中选择【所有任务】，并继续选择级联菜单中的【管理触发器】命令，将打开图8-9所示的【触发器属性】对话框。第24页，本讲稿共45页图8-9 【触发器属性】对话框第25页，本讲稿共45页在【触发器属性】对话框的文本框中，将TRIGGER NAME修改为新建触发器的名称，并输入触发器所包含的SQL语句。通过点击【检查语法】按钮来判断触发器的语法是否正确，最后，点击【确定】按钮保存新建的触发器。2使用使用CREATE TRIGGER语句创建触发器语句创建触发器CREATE TRIGGER语句的部分语法

19、格式：CREATE TRIGGER 触发器名称 ON 表名|视图名 WITH ENCRYPTION FOR|AFTER|INSTEAD OF DELETE,INSERT,UPDATEAS SQL语句.n 第26页，本讲稿共45页 其中：触发器名称 必须符合标识符命名规则，并且在同一个数据库中触发器的名称不允许重复。触发器名称中可以省略所有者名称。表名或视图名称 是指建立触发器所依赖的对象。也可以称为触发器表或触发器视图。WITH ENCRYPTION 该选项的作用为对触发器中所包含的SQL语句进行加密。FOR或AFTER 代表后触发方式，即当满足所有的引用级联操作和约束检查后并且完成了SQL

20、语句中指定的所有操作后，指定的触发器才会被执行。在视图上不允许采用后触发方式。INSTEAD OF 表示替代触发方式，每一个INSERT、UPDATE、DELETE 语句只能定义一个 INSTEAD OF 触发器。第27页，本讲稿共45页DELETE、INSERT、UPDATE 三个选项中应该至少选择其一，用于表示在表或视图上执行了哪一类的操作时会将触发器激活。SQL语句 为触发器中包含的条件以及需要执行的操作。在执行触发器时，系统会自动创建两张临时表INSERTED、DELETED，这两张表的结构与触发器所依赖的表类似，用于保存在用户操作过程中被插入或被删除的数据。每一个触发器在执行过程中都

21、会产生与之相关的上述两张临时表，并且在执行结束后，两个临时表会自动被系统删除。对临时表可以进行查询操作，如SELECT*FROM DELETED，但是不能对临时表进行修改 第28页，本讲稿共45页【例例8-5】在数据库XSCJ中设计一个触发器，该触发器的作用为：当在班级表中删除某一个班级时，在学生基本信息表中该班级所包含的学生信息也全部被删除。提示：在此例中，由于涉及到了班级表的删除操作，因而需要设计一个DELETE类型的触发器。在查询分析器中运行如下命令：USE XSCJGOCREATE TRIGGER del_bj ON 班级表AFTER DELETEAS DELETE FROM 学生基本

22、信息表 WHERE 班级编号 IN(SELECT 班级编号 FROM DELETED)GO运行结果如图8-10所示。第29页，本讲稿共45页图8-10 一个DELETE类型的触发器【例例8-6】在数据库XSCJ中设计一个触发器，该触发器能够保证在学生基本信息表中添加新的纪录时，新学生的班级编号必须已经存在于班级表中。第30页，本讲稿共45页提示：设计该触发器有助于实现学生信息的完整性。在此例中由于涉及到了学生基本信息表中的添加操作，因而需要设计一个INSERT类型的触发器。在查询分析器中运行如下命令：USE XSCJGOCREATE TRIGGER insert_xs ON 学生基本信息表AF

23、TER INSERTAS IF EXISTS (SELECT*FROM INSERTED WHERE 班级编号 IN(SELECT 班级编号 FROM 班级表)PRINT 添加成功！ELSE BEGIN PRINT 班级编号与现有的班级不符！ROLLBACK TRANSACTION END第31页，本讲稿共45页运行结果如图8-11所示。图8-11 一个INSERT类型的触发器 第32页，本讲稿共45页创建了触发器insert_xs之后，我们可以添加新的学生纪录进行测试，例如：INSERT INTO 学生基本信息表(学号,姓名,性别,族别,班级编号)VALUES(000108,王松涛,男,汉,

24、20081001)由于“班级表”中不存在编号为20081001的班级，因而添加操作将会被取消。ROLLBACK TRANSACTION用于回滚已经完成的操作。运行结果如图8-12所示。图8-12 添加操作被取消第33页，本讲稿共45页8.2.4 修改与删除触发器修改与删除触发器1使用企业管理器修改触发器使用企业管理器修改触发器在控制台根目录下依次展开服务器组、服务器节点、数据库节点。选择某一个数据库，并双击【表】节点，在右侧窗口的列表中选择某一张需要对其所建立的触发器进行修该的表。右击所选中的表，在弹出的下拉菜单中选择【所有任务】，并继续选择级联菜单中的【管理触发器】命令，将打开【触发器】属性

25、对话框，如图8-13所示。在【触发器属性】对话框中，在【名称】下拉列表框选择一个已有的触发器，并在【文本】编辑框中对其所包含的SQL语句进行修改。第34页，本讲稿共45页图8-13【触发器】属性对话框第35页，本讲稿共45页在语法检查无误的情况下，点击【确定】按钮保存修改。如果需要删除当前触发器，可以点击【删除】按钮。2使用ALTER TRIGGER语句修改触发器ALTER TRIGGER语句与CREATE TRIGGER语句的语法格式及其参数类似，部分语法格式为：ALTER TRIGGER 触发器名称 ON 表名|视图名 WITH ENCRYPTION FOR|AFTER|INSTEAD O

26、F DELETE,INSERT,UPDATEAS SQL语句.n 第36页，本讲稿共45页【例例8-7】修改del_bj触发器，使得在班级表中删除某一个班级时，不仅在学生基本信息表中该班级所包含的学生信息将被删除，而且在成绩表中与学生相关的数据也将被删除。在查询分析器中运行如下命令：ALTER TRIGGER del_bj ON 班级表AFTER DELETEAS DELETE FROM 成绩表 WHERE 学号 IN(SELECT 学号 FROM 学生基本信息表 WHERE 班级编号 IN(SELECT 班级编号 FROM DELETED)DELETE FROM 学生基本信息表 WHERE

27、班级编号 IN(SELECT 班级编号 FROM DELETED)运行结果如图8-14所示。第37页，本讲稿共45页图8-14 修改del_bj触发器3使用使用DROP TRIGGER语句删除触发器语句删除触发器移除触发器时，与触发器有关的信息将从sysobjects和syscomments系统表中删除。可以通过删除触发器或删除表两种方式移除触发器。删除表时，将除去所有与表相关联的触发器。第38页，本讲稿共45页DROP TRIGGER语句的语法格式为：DROP TRIGGER 触发器名称 ,.n 触发器名称为数据库中现有的触发器。可以指定一个或多个触发器名称来删除一个或多个触发器。【例8-8

28、】删除触发器del_bj以及insert_xs。在查询分析器中运行如下命令：USE XSCJGODROP TRIGGER del_bj,insert_xs运行结果如图8-15所示。第39页，本讲稿共45页图8-15 删除触发器del_bj以及insert_xs第40页，本讲稿共45页本章小结本章小结本章学习了存储过程与触发器的相关知识，存储过程中可以将多条SQL语句集中在一起完成复杂的功能，从而用户可以在不具有对数据对象访问的权限下调用这些存储过程完成的特定的操作。触发器可以帮助用户完成数据的验证功能，从而能够保证数据的一致性、完整性。第41页，本讲稿共45页练习与上机练习与上机一选择题一选择

29、题1下面关于存储过程的描述中哪些是正确的（）A自定义存储过程与系统存储过程名称可以相同B存储过程最多能够支持64层的嵌套C命名存储过程中的标识符时，长度不能超过256个字符D存储过程中参数的个数不能超过21002用于创建存储过程的SQL语句为（）ACREATE DATABASE BCREATE TRIGGERCCREATE PROCEDURE DCREATE TABLE3用于修改存储过程的SQL语句为（）AALTER TABLE BALTER DATABASECALTER TRIGGER DALTER PROCEDURE第42页，本讲稿共45页 4下列对触发器的描述中哪一个是错误的（）A触发器

30、属于一种特殊的存储过程B触发器与存储过程的区别在于触发器能够自动执行并且不含有参数C触发器有助于在添加、更新或删除表中的记录时保留表之间已定义的关系D既可以对INSERTED、DELETED临时表进行查询，也可以进行修改第43页，本讲稿共45页二填空题二填空题1一个存储过程的名称不能超过_个字符。2使用_语句可以对存储过程进行重命名。3触发器有_、_触发方式。4用_语句可以删除触发器。5触发器可以划分为三种类别_、_、_。三简答题三简答题1简述什么是存储过程？2简要说明存储过程的语法格式？3简述什么是触发器？4简要说明触发器的语法格式？第44页，本讲稿共45页项目实训项目实训1创建一个存储过程，该存储过程能够实现根据系部的编号查询出系部中男生、女生的人数。2创建一个INSERT触发器，该触发器能够在向成绩表中添加数据时，自动判断学号、课程编号、成绩是否合法，如果非法则对插入操作进行回滚。3.修改存储过程的名字。4建立一个存储过程，来修改student数据库的c、sc两张表，当用户修改c表中的cno后，修改cno表中对应的数据。第45页，本讲稿共45页