《SQL server数据库》课件第8章 存储过程与触发器.ppt

1、8.1任务描述：任务描述：本章完成项目的第8个任务：在大学生选课管理数据库Student中，完成以下基本操作：1创建一个添加学生选课信息的存储过程。2创建一个删除学生选课信息的存储过程。3创建一个修改学生选课信息的存储过程。4创建一个INSERT触发器。5创建一个DELETE触发器。6创建一个UPDATE,INSERT触发器。8.2.1 存储过程的概念存储过程的概念 存储过程是数据库的一种对象，是为了实现某个特定任务，以一个存储单元的形式存储在服务器上的一组SQL语句的集合。用户也可以把存储过程看成是以数据库对象形式存储在SQL Server中的一段程序或函数。存储过程是由一系列的SQL语句或

2、控制流程语句组成。8.2 存储过程综述存储过程综述 8.2.2 存储过程的类型存储过程的类型 用户定义的存储过程 系统存储过程 扩展存储过程8.3 创建、执行、修改和删除存储过程创建、执行、修改和删除存储过程 8.3.1 创建存储过程创建存储过程 创建存储过程的语法格式：创建存储过程的语法格式：CREATE PROCEDURE dbo.存储过程名 形式参数定义 WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS BEGIN 过程体语句组 END 例如：在数据库Student中，创建一个存储过程st_ksp，该存储过程可根据输入的课程名称返回该课程

3、的平均成绩。USE StudentGOCREATE PROCEDURE st_ksp km varchar(20),pf decimal(5,1)OUTPUTASIF Exists(Select *From ctab Where kcm=km)Select pf=Avg(cj)From sctab Where kch=(Select kch From ctab Where kcm=km)ELSESET pf=0GO在查询编辑器中执行以上代码，创建存储过程st_ksp。8.3.1 创建存储过程创建存储过程8.3.2 执行存储过程执行存储过程调用存储过程的语法格式：调用存储过程的语法格式：EXEC

4、UTE 存储过程名存储过程名 实际参数实际参数 WITH RECOMPILE 其中，实际参数格式为：其中，实际参数格式为：输入参数对应的表达式输入参数对应的表达式|输出参数对应的变量输出参数对应的变量 OUTPUT,n注意：实际参数与形式参数从类型、个数和顺序必注意：实际参数与形式参数从类型、个数和顺序必须一一对应。须一一对应。例如：利用上述存储过程st_ksp输出高等数学这门课的平均成绩。USE StudentGODECLARE pj decimal(5,1)EXECUTE st_ksp 高等数学 ,pj OUTPUTPRINT 高等数学平均成绩为：+STR(pj)GO8.3.2 执行存储过

5、程执行存储过程8.3.3 查看存储过程查看存储过程查看存储过程信息语法格式：查看存储过程信息语法格式：sp_helptext 存储过程名存储过程名例：查看数据库例：查看数据库Student中存储过程中存储过程st_ksp的信息。的信息。USE StudentGOsp_helptext st_kspGO8.3.4 修改存储过程修改存储过程修改存储过程语法格式：修改存储过程语法格式：ALTER PROCEDURE dbo.存储过程名存储过程名 形式参数定义形式参数定义 WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS BEGIN 过程体语句组过程体

6、语句组 END 8.3.5 删除存储过程删除存储过程删除存储过程语法格式：删除存储过程语法格式：DROP PROCEDURE 存储过程名存储过程名例：删除数据库例：删除数据库Student中的存储过程中的存储过程st_jsjbj.USE StudentGODROP PROCEDURE st_jsjbjGO8.4 存储过程的重新编译与加密存储过程的重新编译与加密1、在建立存储过程时设定重新编译、在建立存储过程时设定重新编译在在CREATE PROCEDURE命令中指定命令中指定WITH RECOMPILE选项。选项。2、在执行存储过程时设定重新编译、在执行存储过程时设定重新编译在在EXECUTE

7、命令中指定命令中指定WITH RECOMPILE选项。选项。3、使用系统存储过程对存储过程设定重新编译、使用系统存储过程对存储过程设定重新编译语法格式：语法格式：EXEC sp_recompile 存储过程名存储过程名8.4.1 存储过程的重新编译存储过程的重新编译在CREATE PROCEDURE 命令中指定WITH ENCRYPTION选项。例：例：在数据库Student中，创建一个加密存储过程st_nkq，该过程可列出未选修任何课程的学生信息。USE StudentGOCREATE PROCEDURE st_nkqWITH ENCRYPTIONASSelect xh 学号 ,xm 姓名

8、From stab Where xh NOT IN(Select DISTINCT xh From sctab)GO8.4.2 存储过程的加密存储过程的加密8.5 触发器综述触发器综述 触发器是一种特殊类型的存储过程。与存触发器是一种特殊类型的存储过程。与存储过程类似，它也是由储过程类似，它也是由SQL语句组成的，可以语句组成的，可以实现一定的功能。实现一定的功能。触发器分为两大类：DML触发器和DDL触发器。8.6 触发器的创建执行、修改和删除触发器的创建执行、修改和删除 8.6.1 触发器的创建与执行触发器的创建与执行1、Inserted表和表和Deleted表表 当向表中插入数据时，如果

9、该表存在INSERT触发器，触发器将被触发而自动执行。当从表中删除数据时，如果该表存在DELETE触发器，触发器将被触发而自动执行。当修改表中的数据时，就相当于删除一条旧的记录，添加一条新的记录，其中被删除的记录存放在Deleted表中，同时添加的新纪录存放在Inserted表中。8.6.1 触发器的创建与执行触发器的创建与执行 2、创建触发器、创建触发器语法格式：语法格式：CREATE TRIGGER 触发器名 ON 触发器的表名 WITH ENCRYPTION FOR INSERT ,DELETE ,UPDATE AS IF UPDATE(列)AND|OR UPDATE(列)n BEGIN

10、 触发器语句组 END 例如：在数据库Student中，为教师信息表ttab建立一个名称为del_js的DELETE触发器，其作用是当删除某个教师的记录时，检查教师教课信息表tctab中是否存在该教师的上课信息，如果存在则提示不允许删除该教师的记录。USE StudentGOCREATE TRIGGER del_js ON ttabFOR DELETEASDECLARE jh char(4)Select jh=jsh From Deleted IF Exists(Select *From tctab Where jsh=jh)BEGIN PRINT 该教师能够上课，不能删除！该教师能够上课，不

11、能删除！ROLLBACK TRANSACTION ENDGO在查询编辑器中执行以上代码创建触发器del_js。8.6.1 触发器的创建与执行触发器的创建与执行8.6.2 查看触发器信息查看触发器信息查看某表的触发器信息语法格式：查看某表的触发器信息语法格式：sp_helptrigger 表名表名 ,INSERT ,DELETE ,UPDATE 例：查看数据库例：查看数据库Student中的教师信息表中的教师信息表ttab的触的触发器的信息。发器的信息。USE StudentGOsp_helptrigger ttabGO8.6.3 修改触发器修改触发器修改触发器语法格式：修改触发器语法格式：AL

12、TER TRIGGER 触发器名触发器名 ON 触发器的表名触发器的表名 WITH ENCRYPTION FOR INSERT ,DELETE ,UPDATE AS IF UPDATE(列列)AND|OR UPDATE(列列)n BEGIN 触发器语句组触发器语句组 END 8.6.4 禁止、启用和删除触发器禁止、启用和删除触发器1、禁止触发器、禁止触发器语法格式：语法格式：DISABLE TRIGGER 触发器名称触发器名称 ON 表名表名触发器被禁止后，则不再起作用。触发器被禁止后，则不再起作用。2、启用触发器、启用触发器重新启用被禁止的触发器，语法格式：重新启用被禁止的触发器，语法格式：

13、ENABLE TRIGGER 触发器名称触发器名称 ON 表名表名3、删除触发器、删除触发器语法格式：语法格式：DROP TRIGGER 触发器名称触发器名称8.7 任务实现任务实现1、创建一个添加学生选课信息的存储过程、创建一个添加学生选课信息的存储过程 在数据库Student中创建一个存储过程sc_add，该存储过程可添加某学生的选课信息。参数x、k、j、c分别为学生的学号、课程号、教师号和成绩。USE StudentGOCREATE PROCEDURE sc_add x char(6),k char(3),j char(4),c decimal(4,1)AS Insert into sc

14、tab values(x,k,j,c)GO8.7 任务实现任务实现2、创建一个删除学生选课信息的存储过程创建一个删除学生选课信息的存储过程 在数据库Student中创建一个存储过程sc_del，该存储过程可删除某学生的选课信息。参数x、k、j分别为学生的学号、课程号和教师号。USE StudentGOCREATE PROCEDURE sc_del x char(6),k char(3),j char(4)ASDelete From sctab Where xh=x and kch=k and jsh=jGO8.7 任务实现任务实现3、创建一个修改学生选课信息的存储过程创建一个修改学生选课信息的

15、存储过程 在数据库Student中创建一个存储过程sc_edit，该存储过程可修改某学生的选课信息。参数x、k、j、c分别为学生的学号、课程号、教师号和成绩。USE StudentGOCREATE PROCEDURE sc_editx char(6),k char(3),j char(4),c decimal(4,1)ASUpdate sctab Set kch=k ,jsh=j ,cj=c Where xh=x and kch=k and jsh=jGO8.7 任务实现任务实现4、创建一个、创建一个INSERT触发器触发器 在数据库Student中，为教师教课表tctab建立一个名称为add

16、_js的INSERT触发器。当用户向教师教课表tctab中添加记录时，如果添加了在教师表ttab中没有的教师号或者在课程表ctab中没有的课程号，则提示用户不能添加记录，否则提示记录添加成功。代码如下所示：8.7 任务实现任务实现4、创建一个、创建一个INSERT触发器触发器USE StudentGOCREATE TRIGGER add_js ON tctabFOR INSERTASDECLARE jh char(4),kh char(3)Select jh=ttab.jsh From ttab ,Inserted Where ttab.jsh=Inserted.jshSelect kh=ct

17、ab.kch From ctab ,Inserted Where ctab.kch=Inserted.kchIF jh and kh PRINT 添加记录成功添加记录成功ELSE BEGIN PRINT 添加记录非法，不能添加记录！添加记录非法，不能添加记录！ROLLBACK TRANSACTION ENDGO8.7 任务实现任务实现5、创建一个、创建一个DELETE触发器触发器 在数据库Student中，为课程表ctab建立一个名称为del_kc的DELETE触发器。当用户删除课程表ctab中的记录时，如果在教师教课表tctab中引用了此记录的课程编号，则提示用户不能删除记录，否则提示记录已

18、删除。代码如下所示：8.7 任务实现任务实现5、创建一个、创建一个DELETE触发器触发器USE StudentGOCREATE TRIGGER del_kc ON ctabFOR DELETEASIF(Select count(*)From tctab,Deleted Where tctab.kch=Deleted.kch)0 BEGIN PRINT 删除记录非法，不能删除记录！ROLLBACK TRANSACTION ENDELSE PRINT 记录已删除GO 8.7 任务实现任务实现6、创建一个、创建一个UPDATE,INSERT触发器触发器 在数据库Student中，先为学生选课表sc

19、tab增加一个字段xf，用于存放课程的学分。然后再为该表建一个名称edit_cj的UPDATE,INSERT触发器。当用户更新该表的学生成绩或添加新记录时，自动依据课程表ctab为学分字段xf赋值或清零。代码如下所示：6、创建一个、创建一个UPDATE,INSERT触发器触发器USE StudentGOALTER TABLE sctab ADD xf tinyintGOCREATE TRIGGER edit_cj ON sctabFOR UPDATE,INSERTASDECLARE sc decimal(4,1),x char(6),kh char(3)IF UPDATE(cj)BEGINSelect sc=Inserted.cj ,x=Inserted.xh ,kh=Inserted.kch From sctab ,Inserted Where sctab.xh=Inserted.xh and sctab.kch=Inserted.kchIf(sc=60)Update sctab Set xf=(Select xf From ctab Where kch=kh)Where xh=x and kch=khelse Update sctab Set xf=0 Where xh=x and kch=khENDGO8.7 任务实现任务实现