第11章 存储过程和触发器

数据库原理及应用（SQLServer2005）授课教师：******2020年1月18日第11章存储过程和触发器第11章存储过程和触发器任务目标：了解存储过程、触发器的基本概念、用途和类型；掌握存储过程、触发器的创建、修改、执行和删除等操作；掌握DML触发器和DDL触发器的特点和创建方式；理解AFTER触发器和INSTEADOF触发器会配置统计信息的选项；灵活运用存储过程和触发器提高系统开发效率第11章存储过程与触发器11.1存储过程10.2触发器习题与实验11.1存储过程10.1.1存储过程概述•存储过程（StoredProcedure）是一组为了完成特定功能的T-SQL语句集，经编译后存储在SQLServer服务器端数据库中。利用存储过程可以加速SQL语句的执行。•存储过程具有以下优点：（1）存储过程允许用户进行模块化程序设计，大大提高了用户设计程序的效率。（2）存储过程已经在服务器上注册，这样可以提高T-SQL语句的执行效率。（3）存储过程运行稳定，错误较少。11.1存储过程10.1.1存储过程概述（4）存储过程具有安全性和所有权链接，可以执行所有的权限管理。（5）存储过程可以提高应用程序的安全性，可以防止SQL嵌入式攻击。（6）存储过程是一组命名代码，允许延迟绑定。（7）存储过程可以大大减少网络通信流量•SQLServer2005中有3种基本类型的存储过程：（1）系统存储过程（2）用户自定义存储过程（3）扩展存储过程11.1存储过程10.1.2创建存储过程SQLServer2005中可以用CREATEPROCEDUR语句来定义存储过程，语法如下：CREATE{PROC|PROCEDURE}存储过程名称[;数值][{@参数数据类型}[VARYING][=参数的默认值][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]AS{SQL语句[;][...n]11.1存储过程10.1.2创建存储过程1．创建简单的存储过程任务11-1创建一个简单的存储过程Proc_kc，它不包含任何参数，只包含一条简单Select查询语句。该存储过程用于查询所有课程的信息。如图11-1所示：图11-1创建简单的存储过程Proc_kc11.1存储过程10.1.2创建存储过程2.创建一个带有输入参数的存储过程任务11-2创建一个带有输入参数的存储过程Proc_s_g_c，查询指定课程的学生成绩信息，默认情况下返回“105101”号课程的成绩。图11-2创建带有一个输入参数的存储过程Proc_S_G_C11.1存储过程10.1.2创建存储过程3．创建一个带输入参数和输出参数的存储过程任务11-3：创建一个带有输入参数和输出参数的存储过程PROC_AVG，用于返回学生所学课程的平均分。图11-3创建带有一个输入参数和输出参数的存储过程PROC_AVG11.1存储过程10.1.2创建存储过程4．创建执行插入操作的存储过程任务11-4：创建一个插入操作的存储过程proc_ins_s，用于向student表中插入一条记录。图11-4创建执行插入操作的存储过程Proc_ins_s11.1存储过程10.1.2创建存储过程5．创建执行更新操作的存储过程任务11-5创建一个带有输入参数的基于更新的存储过程Proc_upd_g，用于在grade表中为指定课程的成绩（介于55－59分）之间的学生成绩都修改为60分。如图11-5所示：图11-5创建执行更新操作的存储过程Proc_upd_g11.1存储过程10.1.2创建存储过程6．创建执行删除操作的存储过程任务11-6创建一个带有输入参数的存储过程Proc_del_sno，用于删除student表中指定学号的学生信息。如图11-6所示：图11-6创建执行删除操作的存储过程Proc_del_sno11.1存储过程10.1.3执行存储过程存储过程创建完成后，可以使用EXECUTE语句来执行存储过程。语法格式如下：[｛EXEC|EXECUTE｝]{[@返回状态=]{@存储过程变量}}[[@参数=]{参数值|@变量[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]11.1存储过程10.1.3执行存储过程1．执行简单的存储过程任务11-7执行任务10-1创建的简单的存储过程Proc_kc，用于显示所有课程的信息。图11-7执行简单的存储过程Proc_kc11.1存储过程10.1.3执行存储过程2．执行带一个输入参数的存储过程任务11-8执行任务11-2创建的简单的存储过程Proc_S_G_C，用于显示某个特定课程的成绩信息。图11-8执行带一个输入参数的存储过程Proc_S_G_C11.1存储过程10.1.3执行存储过程3．执行带一个输入参数和输出参数的存储过程任务11-9执行任务11-3创建的简单的存储过程Proc_AVG，用于输出某个同学的考试成绩平均分。图11-9执行带一个输入参数和输出参数的存储过程Proc_AVG11.1存储过程10.1.3执行存储过程4．执行带多个输入参数存储过程任务11-10执行任务11-4创建的具有插入操作功能的存储过程Proc_ins_s，用于向student表中插入记录。图11-10执行带多个输入参数存储过程Proc_ins_s11.1存储过程10.1.4修改与删除存储过程1．修改存储过程SQLServer2005中可以使用ALTERPROCEDURE语句以命令方式实现。其语法形式如下：ALTER{PROC|PROCEDURE}存储过程名称[;数值][{@参数数据类型}[VARYING][=参数的默认值][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]AS{SQL语句[;][...n]11.1存储过程10.1.4修改与删除存储过程任务11-11修改任务11-3创建的简单的存储过程Proc_AVG，用于输出某门课程的平均分。图11-11修改存储过程11.1存储过程10.1.4修改与删除存储过程2．删除存储过程删除存储过程的语法形式如下：DROPPROCEDURE｛procedure｝[1…n]下面以实例讲解如何修改和删除存储过程.任务11-12删除任务11-1创建的简单的存储过程Proc_kc。DROPPROCEDUREProc_kc11.2触发器10.2.1触发器概述•触发器实际上就是一种特殊类型的存储过程，它是在执行某些特定的T-SQL语句时自动执行的一种存储过程。1、触发器的作用（1）多张表的级联修改。（2）强于CHECK约束的复杂限制。（3）比较数据修改前后的差别。（4）强制表的修改要合乎业务规则。2.触发器的类型（1）DML触发器（2）DDL触发器11.2触发器10.2.1触发器概述3.DML触发器的工作原理在SQLServer2005里，为每个DML触发器都定义了两个特殊的表，一个是插入表inserted，一个是删除表deleted。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表（虚拟表），而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。4.设计DML触发器的注意事项及技巧（1）设计触发器的限制（2）其他注意事项11.2触发器10.2.2创建触发器1.创建DML触发器语法格式如下：CreateTRIGGER触发器名ON{表名|视图名}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[Delete][,][Insert][,][Update]}AS[{IFUpdate(列名)[{AND|or}Update(列名)][...n]|IF(COLUMNS_UPDATED(){AND|OR}updated_bitmask){|=}column_bitmask[...n]}]SQL语句[...n]}}11.2触发器10.2.2创建触发器任务11-13为course表创建一个INSERT触发器，当有新的课程（如网络数据库）插入时，需要及时更新coursetype表中该类型（专业课）课程的数量（加1）。如图11-13所示：图11-13创建INSERT触发器11.2触发器10.2.2创建触发器任务11-14为student表创建一个UPDATE触发器，当更新了某位学生的学号时，就激活触发器级联更新grade表中相关成绩记录中的学号信息。如图11-15所示：图11-15创建Update触发器11.2触发器10.2.2创建触发器任务11-16为grade表创建一个Update触发器用来检测指定列是否被更新。如图11-19所示：图11-17创建Delete触发器11.2触发器10.2.2创建触发器2．创建Insteadof触发器InsteadOf触发器也属DML触发器，InsteadOf触发器在SQLServer服务器接到执行SQL语句请求后，先建立临时的Inserted表和Deleted表，然后就触发了InsteadOf触发器。在以下情形下使用InsteadOf触发器：（1）数据库里的数据禁止修改（2）有可能要回滚修改的SQL语句（3）在视图中使用触发器（4）用自己的方式去修改数据11.2触发器10.2.2创建触发器任务11-17course表和grade表是主从表之间的关系，当对course表进行删除操作时，首先应判断grade表中是否有相关记录，如果有，则禁止删除。如图11-20所示：图11-20创建简单的insteadof触发器11.2触发器10.2.2创建触发器3．创建DDL触发器创建DDL触发器的语法形式如下：CREATETRIGGER触发器名ON{ALLSERVER|DATABASE}[WITHDDL触发器选项[,...n]]{FOR|AFTER}{事件类型|事件分组}[,...n]AS{SQL语句[;][...n]|EXTERNALNAME}DDL触发器选项::=[ENCRYPTION][EXECUTEASClause]11.2触发器10.2.2创建触发器任务11-18建立一个DDL触发器，用于保护数据库中的数据表不被修改，不被删除。如图11-21所示：图11-21创建DDL触发器tri_no_del11.2触发器10.2.2创建触发器任务11-19建立一个DDL触发器，用来记录数据库修改状态。如图11-21所示：图11-22创建DDL触发器tri_log11.2触发器10.2.3修改、查看、删除触发器1．修改触发器任务11-22使用ALTERTRIGGER命令修改任务11-16创建的触发器tri_upd_grade的正文，将其修改为当有更新成绩信息的操作时，让它返回提示信息。图11-29修改触发器的内容11.2触发器10.2.3修改、查看、删除触发器2．查看触发器可以使用系统存储过程SP_HELP、SP_HELPTEXT、SP_DEPENDS等查看触发器的相关信息，具体用途和语法格式如下：SP_HELP：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。SP_HELP‘触发器名称’SP_HELPTEXT：用于查看触发器的正文信息。SP_HELPTEXT‘触发器名称’SP_DEPENDS：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。SP_DEPENDS‘触发器名称’11.2触发器10.2.3修改、查看、删除触发器3．删除触发器删除触发器用DROPTRIGGER语句，可以同时删除一个或多个触发器，语法如下：DROPTRIGGER触发器名[,...n]ON{DATABASE|ALLSERVER}[;]任务11-23删除任务11-15创建的触发器tri_del_student。DROPTRIGGERTri_del_student11.2触发器10.2.3修改、查看、删除触发器4．禁止和启用