存储过程与触发器.docx

1、存储过程与触发器第8章 存储过程与触发器 教学目标通过本章学习，使学生掌握SQL Server存储过程的创建、运行、查看、修改和删除等基本操作方法，掌握DML触发器的基本概念及其创建、触发、查看、修改和删除等基本操作，解决实际的应用问题。 教学要求知识要点能力要求关联知识存储过程(1) 掌握存储过程的基本概念和特点(2) 掌握存储过程的建立、执行和修改等基本操作方法CREATE PROCEDURE，EXECUTE，ALTER PROCEDURE等语句触发器(1) 掌握触发器的基本概念和分类(2) 掌握触发器的创建、触发和修改等基本操作方法CREATE TRIGGER ，ALTER TRIGGE

2、R等语句 重点难点 存储过程的基本概念和特点 存储过程的创建、执行和修改方法 触发器的基本概念和分类 触发器的创建、触发和修改方法8.1任务描述本章完成项目的第8个任务：在大学生选课管理数据库Student中，完成以下基本操作：1创建一个添加学生选课信息的存储过程。2创建一个删除学生选课信息的存储过程。3创建一个修改学生选课信息的存储过程。4创建一个统计某被选课程的平均成绩、最高和最低成绩的存储过程。5创建一个INSERT触发器。6创建一个DELETE触发器。7创建一个UPDATE,INSERT触发器。8.2存储过程综述8.2.1 存储过程的概念存储过程是数据库的一种对象，是为了实现某个特定任

3、务，以一个存储单元的形式存储在服务器上的一组SQL语句的集合。用户也可以把存储过程看成是以数据库对象形式存储在SQL Server中的一段程序或函数。存储过程是由一系列的SQL语句或控制流程语句组成的。存储过程的特点： 接收输入参数并以输出参数的形式将多个值返回至调用过程。 包含执行数据库操作的编程语句。 向调用过程或批处理返回状态值，以表明成功或失败及失败原因。使用存储过程的优点： 增强安全机制。SQL Server可以只给用户访问存储过程的权限，而不授予用户访问存储过程引用的对象（表或视图）的权限。这样，可以保证用户通过存储过程操作数据库中的数据，而不能直接访问与存储过程相关的表，从而保证

4、了数据的安全性。 提高执行速度。用户可以多次使用存储过程的名称调用存储过程。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中，当用户再次执行该存储过程时，调用的是高速缓存中的编译代码，因此，其执行速度要比执行相同的SQL语句快得多。 减少网络流量。存储过程中包含大量的SQL语句，但是它以一个独立的单元存放在服务器上。调用执行过程中，只需传递执行存储过程的调用命令即可将执行结果返回调用过程或批处理，从而减少了网络上数据的传输。8.2.2 存储过程的类型在SQL Server 2008中，存储过程可以分为三种类型：用户定义的存储过程、系统存储过程和扩展存储过程。 用户定义的存储过

5、程：用户定义的存储过程是用户根据需要，为完成某一特定功能，在自己的普通数据库中创建的存储过程。 系统存储过程：系统存储过程以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQL Server提供帮助，为用户查看数据库对象提供方便。系统存储过程存储在资源数据库中。 扩展存储过程：扩展存储过程以xp_为前缀，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库（DLL）文件所包含的函数中实现逻辑，从而扩展了SQL的功能，并且可以像调用SQL过程那样从SQL语句调用这些函数。这里主要介绍用户定义的存储过程。8.3 创建、执行、修改和删除存储过程8.3.1 创建存储过程存

6、储过程是数据库的一种对象，只能在当前数据库中创建存储过程，且存储过程名必须唯一。创建存储过程的语法格式：CREATE PROCEDURE dbo.存储过程名 形式参数定义 WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION AS BEGIN 过程体语句组 END 其中： 形式参数定义格式为：形式参数 数据类型=默认值OUTPUT,n形式参数分为输入参数和输出参数。OUTPUT指示参数为输出参数。 RECOMPILE：表明该过程将在执行时重新编译。 ENCRYPTION：表示加密存储过程文本。在创建存储过程时，一般是先编写实现存储过程功能的S

7、QL语句，然后进行执行调试，待调试成功后，再按照存储过程的语法创建存储过程。8.3.2 执行存储过程调用存储过程的语法格式：EXECUTE 存储过程名 实际参数 WITH RECOMPILE 其中，实际参数格式为：输入参数对应的表达式 | 输出参数对应的变量 OUTPUT,n注意：实际参数与形式参数从类型、个数和顺序必须一一对应。下面通过例题学习存储过程的创建和执行方法。1创建和执行无参存储过程【例8-1】在数据库Student中，创建一个存储过程st_jsjbj，该存储过程可列出计算机系的所有班级名称。USE StudentGOCREATE PROCEDURE st_jsjbjASSelec

8、t bj 班级名称 From stab Where ssx=计算机系GO在查询编辑器中执行以上代码，创建存储过程st_jsjbj。调用该存储过程：USE StudentGOEXECUTE st_jsjbjGO【例8-2】在数据库Student中，创建一个存储过程st_xmax，该存储过程可列出选修人数最多的课程号、课程名和学生人数。USE StudentGOCREATE PROCEDURE st_xmaxASBEGINCREATE VIEW ctab_view1( 课程号 , 课程名 , 学生人数 ) AS Select ctab.kch , ctab.kcm , count(*) From

9、ctab , sctab Where ctab.kch=sctab.kch Group By ctab.kch , ctab.kcmSelect * From ctab_view1 Where 学生人数=ALL( Select 学生人数 From ctab_view1 )Drop View ctab_view1ENDGO在查询编辑器中执行以上代码，创建存储过程st_xmax。调用该存储过程：USE StudentGOEXECUTE st_xmaxGO2创建和执行有参存储过程（1）带有输入参数【例8-3】在数据库Student中，创建一个存储过程st_xk，该存储过程可通过输入的学号来列出该学生

10、的选课信息。USE StudentGOCREATE PROCEDURE st_xk no char(6)ASIF Exists( Select * From sctab Where xh=no)Select stab.xm 姓名 , ctab.kcm 课程名 , sctab.cj 成绩 From stab , ctab , sctabWhere stab.xh=sctab.xh and ctab.kch=sctab.kch and sctab.xh=noELSEPRINT 此学生无选修课！GO在查询编辑器中执行以上代码，创建存储过程st_xk。调用该存储过程：例如：利用该存储过程查看10000

11、6号学生的选课情况。USE StudentGOEXECUTE st_xk 100006GO（2）带有输出参数【例8-4】在数据库Student中，创建一个存储过程st_gsp，该存储过程可以返回高等数学这门课的平均成绩。USE StudentGOCREATE PROCEDURE st_gsp pf decimal(5,1)=0 OUTPUTASSelect pf=Avg(cj) From sctab Where kch=( Select kch From ctab Where kcm=高等数学)GO在查询编辑器中执行以上代码，创建存储过程st_gsp。调用该存储过程：利用该存储过程输出高等数学

12、这门课的平均成绩。USE StudentGODECLARE pj decimal(5,1)EXECUTE st_gsp pj OUTPUTPRINT 高等数学平均成绩为：+STR( pj )GO（3）带有多个参数【例8-5】在数据库Student中，创建一个存储过程st_ksp，该存储过程可根据输入的课程名称返回该课程的平均成绩。USE StudentGOCREATE PROCEDURE st_ksp km varchar(20) , pf decimal(5,1) OUTPUTASIF Exists( Select * From ctab Where kcm=km)Select pf=Avg

13、(cj) From sctab Where kch=( Select kch From ctab Where kcm=km)ELSESET pf=0GO在查询编辑器中执行以上代码，创建存储过程st_ksp。调用该存储过程：例如，利用该存储过程输出高等数学这门课的平均成绩。USE StudentGODECLARE pj decimal(5,1)EXECUTE st_ksp 高等数学 , pj OUTPUTPRINT 高等数学平均成绩为：+STR( pj )GO8.3.3 查看存储过程查看存储过程信息语法格式：sp_helptext 存储过程名【例8-6】查看数据库Student中存储过程st_k

14、sp的信息。USE StudentGOsp_helptext st_kspGO8.3.4 修改存储过程修改存储过程语法格式：ALTER PROCEDURE dbo.存储过程名 形式参数定义 WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION AS BEGIN 过程体语句组 END 【例8-7】修改数据库Student中的存储过程st_gsp，使该存储过程返回程序设计这门课的最高成绩。USE StudentGOALTER PROCEDURE st_gsp pf decimal(5,1)=0 OUTPUTASSelect pf=Max(cj)

15、From sctab Where kch=( Select kch From ctab Where kcm=程序设计)GO8.3.5 删除存储过程删除存储过程语法格式：DROP PROCEDURE 存储过程名【例8-8】删除数据库Student中的存储过程st_jsjbj.USE StudentGODROP PROCEDURE st_jsjbjGO8.4 存储过程的重新编译与加密8.4.1 存储过程的重新编译存储过程第一次执行后，其被编译的代码将驻留在高速缓存中，当用户再次执行该存储过程时，SQL Server 将其从缓存中调出执行。有时，在使用了存储过程后，可能会因为某些原因，必须向表中新增

16、加数据列或者为表新添加索引，从而改变了数据库的逻辑结构，而SQL Server不自动进行优化，直接下一次重新启动后，再运行该存储过程。这时，需要对它进行重新编译，使存储过程能够得到优化。SQL Server提供三种重新编译存储过程的方法，下面分别介绍。1在建立存储过程时设定重新编译在CREATE PROCEDURE命令中指定WITH RECOMPILE选项。【例8-9】在数据库Student中，建立一个存储过程st_kq，该存储过程可根据输入的课程号列出其被选修的情况，要求SQL Server在每次执行该过程时都要重新编译。USE StudentGOCREATE PROCEDURE st_kq

17、 kh char(3)WITH RECOMPILEASIF Exists( Select * From sctab Where kch=kh ) Select ctab.kch 课程号 , ctab.kcm 课程名 , count(*) 选修人数From ctab , sctabWhere ctab.kch=sctab.kch and sctab.kch=khGroup By ctab.kch , ctab.kcmELSE PRINT 此课无人选修！GO2在执行存储过程时设定重新编译在EXECUTE命令中指定WITH RECOMPILE选项。【例8-10】调用数据库Student中的存储过程s

18、t_ksp，输出高等数学这门课的平均成绩，且对该存储过程强制重新编译。USE StudentGODECLARE pj decimal(5,1)EXECUTE st_ksp 高等数学 , pj OUTPUT WITH RECOMPILEPRINT 高等数学平均成绩为：+STR( pj )GO3使用系统存储过程对存储过程设定重新编译语法格式：EXEC sp_recompile 存储过程名【例8-11】对数据库Student中的存储过程st_gsp设定重新编译。USE StudentGOEXEC sp_recompile st_gspGO8.4.2 存储过程的加密在CREATE PROCEDURE

19、命令中指定WITH ENCRYPTION选项。【例8-12】在数据库Student中，创建一个加密存储过程st_nkq，该过程可列出未选修任何课程的学生信息。USE StudentGOCREATE PROCEDURE st_nkqWITH ENCRYPTIONASSelect xh 学号 , xm 姓名 From stab Where xh NOT IN ( Select DISTINCT xh From sctab )GO8.5 触发器综述触发器是一种特殊类型的存储过程。与存储过程类似，它也是由SQL语句组成的，可以实现一定的功能。不同的是，触发器的执行不能通过名称调用来完成，而是当用户对数

20、据库进行操作时，如INSERT、 DELETE、 UPDATE数据时，将会自动触发执行与该操作相关的触发器，使其自动执行。触发器不允许带参数，它的定义与表紧密相连，即作用于表的触发器，可以作为表的一部分，该表称为触发器表。在SQL Server 2008中，触发器分为两大类：DML触发器和DDL触发器。这里主要介绍DML触发器。DML触发器是对表或视图进行了INSERT、 DELETE和UPDATE操作而激活的触发器，该类触发器有助于在表或视图中修改数据时强制业务规则，扩展数据完整性。根据引起触发器自动执行的操作，DML触发器分为三种类型：INSERT、 DELETE和UPDATE触发器。根据

21、触发器被激活的时机，DML触发器分为两种类型：AFTER触发器和 INSTEAD OF触发器。AFTER触发器又称后触发器，当引起触发器执行的操作成功完成之后激发该类触发器。如果因操作错误而执行失败，触发器将不会执行。此类触发器只能定义在表上，不能创建在视图上。可以为每个触发操作（INSERT、 DELETE或UPDATE）创建多个AFTER触发器。INSTEAD OF触发器又称为替代触发器，该类触发器代替触发操作执行，即触发器在数据发生变动之前被触发执行，取代变动数据的操作（INSERT、 DELETE或UPDATE操作），执行触发器定义的操作。该类触发器可在表和视图上定义。只能为每个触发操

22、作（INSERT、 DELETE或UPDATE）创建一个INSTEAD OF触发器。DML触发器包含复杂的处理逻辑，能够实现复杂的数据完整性约束。同其它约束相比，它有以下优点： 触发器自动执行。系统内部机制可以侦测用户在数据库中的操作，并自动激活相应的触发器执行，实现相应的功能。 触发器能够对数据库中的相关表实现级联操作。触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联操作。 触发器可以实现比CHECK约束更为复杂的数据完整性约束。在数据库中为了实现数据完整性约束，可以使用CHECK约束或触发器。CHECK约束不允许引用其他表中的列来完成检查工作，而触发器可以引用

23、其他表中的列。 触发器可以评估数据修改前后表的状态，并根据其差异采取对策。 一个表中可以同时存在三种不同操作的触发器（INSERT、 DELETE或UPDATE）。8.6 触发器的创建执行、修改和删除8.6.1 触发器的创建执行1Inserted表和Deleted表在创建触发器前，需要了解两个与触发器紧密相关的专用临时表：Inserted表和Deleted表。系统为每个触发器创建专用临时表，其表结构与触发器作用的表结构相同。专用临时表被存放在内存中，由系统进行维护，用户可以对其查询，不能对其修改。触发器执行完成后，与该触发器相关的临时表被删除。当向表中插入数据时，如果该表存在INSERT触发器

24、，触发器将被触发而自动执行。此时，系统将自动创建一个与触发器表具有相同表结构的Inserted临时表，新的记录被添加到触发器表和Inserted表中。Inserted表中保存了所有新插入记录的副本，方便用户查找当前的插入数据。当从表中删除数据时，如果该表存在DELETE触发器，触发器将被触发而自动执行。此时，系统将自动创建一个与触发器表具有相同表结构的Deleted临时表，用来保存触发器表中被删除的记录。Deleted表中保存了所有被删除的记录，方便用户查找当前删除的数据。当修改表中的数据时，就相当于删除一条旧的记录，添加一条新的记录，其中被删除的记录存放在Deleted表中，同时添加的新记录

25、存放在Inserted表中。2创建触发器只能在当前数据库中创建触发器，且触发器的名称必须唯一。创建触发器是指为哪个表或视图创建触发器，当对这个表进行操作（INSERT、DELETE和UPDATE）时，其相应的触发器就会被自动触发执行。语法格式：CREATE TRIGGER 触发器名 ON 触发器的表名 WITH ENCRYPTION FOR INSERT , DELETE , UPDATE AS IF UPDATE (列) AND | OR UPDATE (列) n BEGIN 触发器语句组 END 其中： WITH ENCRYPTION ：表示对触发器文本进行加密； INSERT、 DELE

26、TE、UPDATE：用于指定触发器类型； IF UPDATE (列) ：指定对表中字段进行增加或修改内容时起作用，不能用于删除操作。说明：本命令创建的都是AFTER触发器。【例8-13】在数据库Student中，为教师信息表ttab建立一个名称为del_js的DELETE触发器，其作用是当删除某个教师的记录时，检查教师教课信息表tctab中是否存在该教师的上课信息，如果存在则提示不允许删除该教师的记录。USE StudentGOCREATE TRIGGER del_js ON ttabFOR DELETEASDECLARE jh char(4) /*变量jh用于存放删除的教师的编号*/Sele

27、ct jh=jsh From Deleted /*从Deleted表中提取刚删除的教师记录的教师号*/IF Exists( Select * From tctab Where jsh=jh ) BEGIN PRINT 该教师能够上课，不能删除！ ROLLBACK TRANSACTION /*撤销以前的所有操作，回到起始状态*/ ENDGO在查询编辑器中执行以上代码创建触发器del_js。【例8-14】删除教师信息表ttab中编号为0002的教师记录，观察触发器del_js的作用。USE StudentGODELETE From ttab Where jsh=0002GO注意：如果触发器表上存在

28、约束，则在INSTEAD OF触发器执行后、在AFTER触发器执行前检查这些约束。如果违反了约束，则回滚INSTEAD OF触发器操作并且不执行AFTER触发器。【例8-15】设数据库Teaching中有如下两个数据表：其中“总分”指每个学生的中文、英文和数学的总分。表8-1 score（学生成绩表）学号姓名中文英文数学（续表）100001孙大亮789067100005良关英765491100009张小红689283100012王伟568790100016孙启名677898100034和大卫768091表8-2 total（学生成绩汇总表）学号姓名总分100001孙大亮235100005良关英

29、221100009张小红243100012王伟233100016孙启名243100034和大卫247在数据库Teaching中，为表score创建一个名称为add_cj的INSERT触发器，当向该表中添加新的学生的成绩记录时，会自动计算该学生的中文、英文和数学的总分并将其相应信息添加在total表中。USE TeachingGOCREATE TRIGGER add_cj ON scoreFOR INSERTASDECLARE xh char(6) , xm varchar(10) , zf intSelect xh=score.学号 , xm=score.姓名 , zf=score.中文 + score.英文 + score.数学From score , Inserted Where score.学号=Inserted.学号INSERT INTO total values( xh , xm , zf )GO【例8-16】在数据库Teaching中，为表score创建一个名称为del_cj的DELETE触发器，当删除该表中某个学生的成绩记录时，同时也会将total表中该学生的相应记录删掉。USE TeachingGOCREATE TRIGGER del_cj ON scoreFOR DELETEASDEC