第10章 存储过程和触发器.pptx

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

1、课程主讲人：第10章 存储过程和触发器第第10章章 存储过程和触发器存储过程和触发器本章主要介绍存储过程的基本概念，存储过程的创建、执行、查看、修改、调用和删除等操作；再介绍了触发器的基本概念，触发器的分类，触发器的创建、查看、修改、禁用、启用和删除等。本章内容:10.1 存储过程10.1.1 存储过程概述10.1.2 存储过程的类型10.1.3 创建存储过程10.1.4 执行存储过程10.1.5 查看存储过程10.1.6 修改和删除存储过程10.1.7 存储过程的其他操作10.2 触发器10.2.1 触发器概述10.2.2 触发器的分类10.2.3 创建触发器10.2.4 查看触发器信息10

2、.2.5 修改触发器10.2.6 禁用、启用触发器10.2.7 删除触发器10.1 存储过程10.1.1 存储过程概述存储过程是一组在数据库系统中为了完成特定功能的T-SQL语句的集合，经编译后独立存储在数据库中。当需要其功能时，只需要通过存储过程名并给出参数（如果存储过程有参数的情况）调用即可，且存储过程只在首次执行时进行编译，而不需要每次执行时重新编译，所以比单个T-SQL语句块的运行速度快。存储过程是SQL Server中一个非常有用的工具。SQL Server支持存储过程和系统过程。存储过程是独立存在于表之外的数据对象。可以由客户调用，也可以从另一个过程或触发器调用，参数可以被传递和返

3、回，出错代码也可以被检验。10.1.2 存储过程的类型1.系统存储过程系统存储过程是安装SQL Server 2014时自动创建的。系统存储过程能执行许多管理性和信息性活动（如获取数据库信息或者数据库对象的信息等）。系统过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为数据库系统管理员管理SQL Server提供支持。2本地存储过程本地存储过程也称为用户定义存储过程，是由用户自行创建并存储在用户数据库中的存储过程，一般所说的存储过程指的就是本地存储过程。3临时存储过程临时存储过程可分为以下两种：(1)本地临时存储过程如果在创建存储过程时，其名称

4、以“#”号开头，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程。(2)全局临时存储过程如所创建的存储过程名称是以 “#”号开头，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程。不论创建的是本地临时存储过程还是全局临时存储过程，只要SQL Server 2014停止运行，它们将不复存在。4扩展存储过程扩展存储过程是用户可以使用外部程序语言（例如C语言）编写的存储过程。显而易见，扩展存储过程可以弥补SQL Server 2014的不足，并按需要自行扩展其功能。扩展存储过程在使用和执行上与一般的存储过程完全相同，为了区别，扩展存储过程的名称通常以XP_开头。扩

5、展存储过程是以动态链接库(DLL)的形式存在，能让SQL Server 2014动态地装载和执行。扩展存储过程一定要存储在系统数据库master中。10.1.3 创建存储过程1在SQL Server Management Studio中创建存储过程打开SQL Server Management Studio，依次展开“对象资源管理器”“数据库”“teaching” “可编程性”，右键单击“存储过程”选项，选择“新建” “存储过程”命令 。单击“查询”菜单，选择“指定模板参数的值”，在弹出的“指定模板参数的值”对话框中设置新建存储过程的相关参数值。【例10.1】要求在SQL Server Man

6、agement Studio中创建teaching数据库中的存储过程stu_grade,其功能是显示指定学生的指定课程的成绩。2使用T-SQL语句创建存储过程在SQL Server 2014中提供了CREATE PROCEDURE语句在当前数据库创建永久存储过程，或在tempdb数据库中创建临时存储过程。其语法格式如下：CREATE PROC | PROCEDURE schema_name.procedure_name ; number parametertype_schema_name. data_type VARYING = default OUT|OUT PUT |READONLY ,.

7、n WITH ,.n FOR REPLICATIONAS BEGIN sql_statement ; .n END;:=RECOMPILEENCRYPTIONEXECUTE AS clause【例10-1】在teaching数据库中创建无参存储过程，查询每个同学各门功课的平均成绩。【例10-2】在teaching数据库中创建带参数的存储过程student_avgpara，查询指定学生的所有课程的平均成绩。【例10-3】在teaching数据库中创建带参数的存储过程，查询某个同学的基本信息。【例10-4】在teaching数据库中创建带参数的存储过程，修改某个同学某门课的成绩。【例10-5】在t

8、eaching数据库中创建带有参数和默认值（通配符）的存储过程，从student表中返回指定的学生（提供姓名）的信息。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则返回所有学生的信息。【例10-6】在teaching数据库中使用T-SQL语句创建带输入参数和输出参数的存储过程stu_avgparaout，通过输入参数指定学生的学号，查询指定学生的所有课程的平均成绩，通过输出参数返回平均成绩。10.1.4 执行存储过程1使用SQL Server Management Studio执行存储过程步骤如下：(1)打开SQL Server Management Studio，展开存储过程所在的

9、数据库，展开“可编程性”选项，右键单击存储过程名，在弹出的快捷菜单中选择“执行存储过程”命令 。(2)进入“执行过程”对话框，输入要查询的参数值，再点“确定”按钮。2. 使用T-SQL 语句执行存储过程EXECUTE可以简写为EXEC，如果存储过程是批处理中的第一条语句，那么可以省略EXECUTE关键字。对于以sp_开头的系统存储过程，系统将在master数据库中查找。如果执行用户自定义的sp_开头的存储过程，就必须用数据库名和所有者名限定。EXECUTE语句的语法格式为： EXEC | EXECUTE return_status = procedure_name;number paramet

10、er = value | variable OUTPUT ,.n WITH ,n:= RECOMPILE | RESULT SETS UNDEFINED WITH ,.n | RESULT SETS NONE | RESULT SETS ( ,.n ) 【例10-7】执行存储过程student_avg。【例10-8】执行带参数的存储过程GetStudent，查询学号为20160212的学生的基本信息。【例10-9】执行修改成绩的存储过程Update_score 。【例10-10】执行带有参数和默认值（通配符）的存储过程Student_Name。【例10-11】将Student_Name进行修改

11、，可以实现模糊查询，即输入学生姓名信息不全时同样可以得到查询结果。例如输入“王”，可以查询所有姓“王”的学生信息。10.1.5 查看存储过程查看存储过程可以使用SQL Server Management Studio界面，也可以使用T-SQL 语句。1使用SQL Server Management Studio查看【例10-12】使用SQL Server Management Studio界面查看GetStudent存储过程。2使用T-SQL 语句查看存储过程可以执行系统存储过程sp_helptext，用于查看创建存储过程的命令语句；也可以执行系统存储过程sp_help，用于查看存储过程的名称

12、、拥有者、类型、创建时间，以及存储过程中所使用的参数信息。其语法格式分别为： sp_helptext 存储过程名称 sp_help 存储过程名称【例10-13】查看存储过程student_avg的相关信息。10.1.6 修改和删除存储过程存储过程创建好之后，如果不能满足用户需求，需要修改部分功能，此时需要修改存储过程。某些存储过程不再需要，此时可以删除存储过程。修改和删除存储过程可以在SQL Server Management Studio中，也可以通过T-SQL中的ALTER语句来完成。1在SQL Server Management Studio中修改和删除存储过程（1）修改存储过程步骤如下

13、：在“对象资源管理器”中，选中要修改的存储过程，单击鼠标右键，在弹出的快捷菜单中选择“修改”，随后会打开相应的查询窗口。在查询窗口中修改相应的语句。（2）删除存储过程在SSMS的“对象资源管理器”中，右击要删除的存储过程，在弹出的快捷菜单中选择“删除”命令，在弹出“删除对象”对话框，单击“确定”按钮即可。2. 使用T-SQL语句修改和删除存储过程（1）使用T-SQL修改存储过程其基本语法格式如下：（2）使用T-SQL删除存储过程SQL Server 2014中提供了DROP PROCEDURE语句删除不需要的存储过程，如果另一个存储过程调用某个己删除的存储过程，则SQL Server 2014

14、会在执行该调用过程时显示一条错误信息。如果定义了同名和参数相同的新存储过程来替换己删除存储过程，那么引用该过程的其他过程仍能顺利执行。删除存储过程的T-SQL语句的语法格式为：DROPPROC| PROCEDURE schema_name.procedure_name ,n其中，procedure_name指要删除的存储过程或存储过程组的名称。ALTER PROC | PROCEDURE schema_name. procedure_name ; number parameter type_schema_name. data_type VARYING = default OUT | OUTPU

15、T READONLY ,.n WITH ,.n FOR REPLICATION AS BEGIN sql_statement ; .n END ; := ENCRYPTION RECOMPILE EXECUTE AS Clause 10.1.7 存储过程的其他操作1. 重命名存储过程重命名存储过程可以在SQL Server Management Studio中展开要重命名的存储过程，右键单击选择“重命名”，输入新的名字即可。也可以使用系统存储过程sp_rename实现重命名存储过程，基本语法格式为：sp_rename Proc_name,newName,OBJECTEXECUTE sp_ren

16、ame stu_avgpara,stu_avgpara1,OBJECT重命名存储过程不会更改sys.sql_modules目录视图的定义列中相应对象名的名称。因此，建议不要重命名存储过程，而是删除存储过程，然后使用新名称重新创建该存储过程。2查看存储过程的定义存储过程创建完成后，如果要查看它的定义语句，可以使用SSMS查看，也可以使用系统存储过程查看.（1）使用SQL Server Management Studio界面查看存储过程的定义（2）使用相同存储过程查看存储过程的定义查看存储过程定义的常用的系统存储过程有sp_helptext和sp_help。其中sp_helptext主要用于查看存

17、储过程的定义语句；sp_help主要用于查看存储过程的名称、所有者、类型和创建时间，以及存储过程中使用的参数信息。sp_helptext的基本语法格式为：sp_helptext objname = namesp_help的基本语法格式为：sp_help objname = name 【例10-14】查看存储过程Student_Name的定义。【例10-15】查看存储过程Student_Name的定义。【例10-16】使用sys.sql_modules查看stu_update的定义。3查看存储过程的依赖关系10.2 触发器10.2.1 触发器概述就本质而言，触发器也是一种存储过程，它会在特定语言

18、事件发生时自动执行。10.2.2 触发器的分类1DML触发器DML触发器是当数据库服务器中发生数据操作语言(DML)事件时会自动执行的存储过程。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可用于强制业务规则和数据完整性、查询其他表并包括复杂的T-SQL语句。SQL Server 2014的DML触发器分为两类： AFTER触发器在执行INSERT、UPDATE、MERGE或DELETE语句的操作之后执行AFTER触发器。 AFTER触发器只能在表上定义，可以为针对表的同一操作定义多个触发器。INSTEAD OF触发器与AFTER触发

19、器不同，INSTEAD OF触发器一般用来取代原来的操作，它是在数据变更之前触发的，它并不执行原来的操作语句（INSERT、UPDATE或DELETE），而去执行触发器本身所定义的操作。 INSTEAD OF触发器不仅可以定义在表上，也可以定义在视图上。2DDL 触发器DDL触发器在响应数据定义语言（DDL）语句时触发，DDL触发器一般用于数据库中执行管理任务。当在执行触发DDL触发器的DDL语句后，DDL触发器才会触发。DDL触发器无法作为INSTEAD OF触发器使用。3登录触发器登录触发器将为响应 LOGIN 事件而激发存储过程。10.2.3 创建触发器1创建DML触发器（1）在SQL

20、Server Management Studio中创建触发器（2）利用T-SQL语句创建触发器基本语法格式如下：CREATE TRIGGER schema_name.trigger_name ON table | view WITH ,.n FOR | AFTER|INSTEAD OF INSERT,UPDATE,DELETEWITH APPENDNOT FOR REPLICATIONAS sql_statement ; ,.n := ENCRYPTION EXECUTE AS Clause 【例10-17】在teaching数据库的sc表上创建DML触发器unupdate，禁止修改sc表的数

21、据。【例10-18】在teaching数据库中的学生表student中创建DML触发器stu_unupdate，禁止修改学号sno和姓名sname字段。【例10-19】为teaching数据库的student表创建一个DML触发器stu_reminder，在插入和更新数据时自动显示提示信息。【例10-20】为student表创建一个DML触发器print_table，在插入和修改数据时，都会自动显示所有学生的信息。【例10-21】在学生表student上创建一个DELETE类型的触发器，删除数据时，显示删除学生的个数。【例10-22】删除所有“会计”专业的学生。【例10-23】使用触发器实现表

22、的级联更新：如果更新student表中的学号sno字段，则sc表中的学号sno字段也随之更新。【例10-24】使用触发器实现表的参照完整性：向sc表中插入数据时，检查插入的学号是否在student表中存在，检查插入的课程号是否在course表中存在，如果都存在允许插入，否则不允许插入，并给出错误提示。【例10-25】在teaching数据库中的student表上创建触发器实现：当修改数据时，如果该记录的专业是“计算机”，则不允许修改记录；否则可以修改，此时要检查修改性别时是否是“男”或“女”。2创建DDL触发器创建DDL触发器主要使用T-SQL语句方式，其基本语法格式为：CREATE TRIG

23、GER trigger_name ON ALL SERVER |DATABASE WITH ,.n FOR | AFTER event_type|event_group ,.n AS sql_statement ; ,.n := ENCRYPTION EXECUTE AS Clause 【例10-26】在teaching数据库上创建DDL触发器，禁止修改、删除数据库中的表。【例10-27】在当前服务器实例中创建触发器，当创建数据库是给出提示信息。10.2.4 查看触发器信息1在SQL Server Management Studio中查看触发器步骤如下：在SQL Server Manageme

24、nt Studio的“对象资源管理器”面板中展开要查看的触发器，在弹出的快捷菜单中选择“编写触发器脚本为”“CREATE到”“新查询编辑器窗口”，在弹出的T-SQL命令窗口中显示了该触发器的语句内容 。2使用系统存储过程查看触发器系统存储过程SP_HELP和SP_HELPTEXT分别提供有关触发器的不同信息。(1)通过SP_HELP系统存储过程，可以了解触发器的一般信息，包括名字、拥有者名称、类型、创建时间。【例10-28】通过SP_HELP查看student表上的触发器stu_unupdate。【例10-29】通过SP_HELPTEXT查看student表上的触发器stu_unupdate。

25、【例10-30】通过SP_HELPTRIGGER查看student表上的触发器信息。10.2.5 修改触发器1在SQL Server Management Studio中修改触发器在SQL Server Management Studio中修改触发器时，展开要修改的触发器，选中该触发器单击鼠标右键，在弹出的快捷菜单中选择“修改” 。2. 使用T-SQL语句来修改触发器 修改DML触发器的基本语法格式如下：ALTER RIGGER schema_name.trigger_nameON table_name | view WITH ,.n FOR | AFTER | INSTEAD OF INSE

26、RT , DELETE , UPDATE NOT FOR REPLICATION AS sql_statement ; .n dml_trigger_option:= ENCRYPTION 修改DDL触发器的基本语法格式如下：ALTER RIGGER trigger_nameON DATABASE | ALL SERVER WITH ,.n FOR | AFTER event_type ,.n |event_groupAS sql_statement ; ddl_trigger_option:= ENCRYPTION 【例10-31】修改数据库teaching中student表上的触发器stu

27、_reminder，禁止向student表插入数据和更新数据。10.2.6 禁用、启用触发器1. 在SQL Server Management Studio中禁用或启用触发器在SQL Server Management Studio的“对象资源管理器”中可以很方便地启用或禁用触发器。当要禁用某个触发器时，可以在“对象资源管理器”中选中该触发器，单击鼠标右键，在弹出的快捷菜单中选择“禁用”即可。2. 使用T-SQL语句禁用或启用触发器禁用或启用DML触发器的T-SQL语句的语法格式如下： ALTER TABLE table_name ENABLE | DISABLE TRIGGER ALL |

28、trigger_name ,.n 【例10-32】禁止student表上创建的所有触发器。禁用或启用DDL触发器的T-SQL语句的语法格式如下：ENABLE | DISABLE TRIGGER schema_name. ALL | trigger_name ,.n ON object_name | DATABASE | ALL SERVER ; 【例10-33】禁用teaching数据库中的safety触发器。10.2.7 删除触发器当某个触发器不再需要时，可以删除它。删除已创建的触发器一般有以下两种方法：(1)在SQL Server Management Studio的“对象资源管理器”中找

29、到相应的触发器，单击鼠标右键，在弹出的快捷菜单中，选择“删除”命令即可。(2)使用T-SQL命令DROP TRIGGER删除指定的触发器，删除触发器的具体语法格式如下： DROP TRIGGER trigger_ name【例10-34】使用DROP TRIGGER命令删除student上的del_count触发器。本章小结本章主要介绍了存储过程和触发器。存储过程是大型数据库系统中，为了完成特定功能的一组T-SQL语句的集合，经过编译后存储在数据库中。使用存储过程可以减少服务器/客户端网络流量、提供更强的安全性、提高代码的重复使用并且提高执行速度。存储过程的类型主要有系统存储过程、本地存储过程、临时存储过程和扩展存储过程，用户可以通过自定义存储过程来提高系统的性能。触发器是一种特殊类型的存储过程，但它又不同于存储过程，触发器主要是通过事件触发而执行的，而存储过程是通过存储过程名来直接调用的。触发器的主要功能是实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，除此之外，触发器还可以实现的功能有强化约束、跟踪变化、级联运行、存储过程的调用等。触发器可以分为AFTER触发器和INSTEAD OF触发器。