《数据库技术与应用 教学课件 ppt 作者 严冬梅 第12章 存储过程与触发器》由会员分享，可在线阅读，更多相关《数据库技术与应用 教学课件 ppt 作者 严冬梅 第12章 存储过程与触发器（30页珍藏版）》请在金锄头文库上搜索。
1、,第12章 存储过程与触发器,主编：严冬梅,科学出版社,第12章 存储过程与触发器,12.1 存储过程 12.2 触发器 12.3 本章小结 12.4 习题,12.1 存储过程,12.1.1 存储过程概述 12.1.2 创建和执行存储过程 12.1.3 管理存储过程 12.1.4 系统存储过程,12.1 存储过程,12.1.1 存储过程概述 1. 存储过程的概念和分类 存储过程是一组用来完成某种特定功能的Transact-SQL语句集合，这组SQL语句经过预编译后存储在数据库中，可以在SQL Server中或前端应用程序中对其进行调用。可以说存储过程是在数据库端执行的Transact-SQL程
2、序，它主要用于实现需要频繁使用的查询。 在SQL Server中，存储过程主要分为两大类：系统存储过程和用户自定义存储过程。,12.1 存储过程,12.1.1 存储过程概述 2. 存储过程的优点 增强代码的可重用性和共享性 执行速度快 减少网络流量 增强安全性,12.1.2 创建和执行存储过程,可以用CREATE PROCEDURE语句和EXECUTE语句来创建和执行存储过程。 创建存储过程的语法如下： CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH
3、RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statements,12.1.2 创建和执行存储过程,CREATE PROCEDURE主要参数： procedure_name：所创建的存储过程的名称。 parameter：存储过程中的参数，在一个存储过程可以没有参数，也可以指定一个或多个参数。使用符号作为第一个字符来指定参数名称，参数命名必须符合标识符的规则，而且符号和参数名之间不能有空格。 data_type：指定参数的数据类型。可以使用除table之外任何一种SQL Server所提供的数据类型
4、来定义参数，同样也可以使用用户自定义类型。 =default：参数的默认值 OUTPUT：表示参数为返回参数。 RECOMPILE：表示SQL Server不保存该存储过程的执行计划，每次执行都要重新编译。 ENCRYPTION：表示 SQL Server 加密 syscomments 表中包含CREATE PROCEDURE 语句文本。 AS：指定过程要执行的操作。 sql_statement：存储过程中包含的任意数目和类型的Transact-SQL 语句。,12.1.2 创建和执行存储过程,执行存储过程使用EXECUTE语句，具体语法如下： EXEC UTE return_status =
5、 procedure_name ;number | procedure_name_var parameter = value | variable OUTPUT | DEFAULT ,.n WITH RECOMPILE 参数说明如下： return_status：是一个可选的整型变量，保存存储过程的返回状态。 procedure_name：要进行调用的存储过程的名称。 procedure_name_var：局部定义变量名，代表存储过程名称。 parameter：存储过程参数，与前面在CREATE PROCEDURE 语句中定义的相一致。 value：存储过程中参数的值。 variable：是用
6、来保存参数或者返回参数的变量。 OUTPUT：指定存储过程必须返回一个参数。使用 OUTPUT 参数，目的是在调用存储过程的其它语句中使用其返回值，参数值必须作为变量传递。,12.1.2 创建和执行存储过程,还可以在SQL Server的企业管理器中创建存储过程。首先，启动企业管理器并登录到相应的服务器。在左侧窗格中选择要创建存储过程的数据库并将其展开，如图所示：右键单击“存储过程”一项并选择弹出菜单中的“新建存储过程(S)”，,12.1.2 创建和执行存储过程,右键单击“存储过程”一项并选择弹出菜单中的“新建存储过程(S)”，或者在右侧窗格的空白处单击鼠标右键，出现新建存储过程的对话框如图所
7、示。 输入存储过程代码后，单击“检查语法(C)”，如果没有错误，则单击“确定”保存存储过程。,12.1.3 管理存储过程,1. 查看存储过程 要查看存储过程的源代码，可以使用sp_helptext，格式为： sp_helptext 存储过程名 可以使用sp_help查看存储过程的一般信息，包括存储过程的所有者，创建日期以及相关参数的信息。格式为： sp_help 存储过程名 2. 重新命名存储过程 可以使用sp_rename对现有存储过程重新命名，其格式为： sp_rename 原过程名 新过程名,12.1.3 管理存储过程,3. 修改存储过程 修改存储过程可以通过ALTER PROCEDUR
8、E语句来实现，其语法如下： ALTER PROC EDURE procedure_name ;number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statements 其中各个参数和关键字的具体含义请参见有关CREATE PROCEDURE语句的说明。 4. 删除存储过程 使用DROP PROCEDURE可以删除一个或多个存储过程，其语法为： DROP PROCEDURE proced
9、ure ,.n ,12.1.4 系统存储过程,系统存储过程由SQL Server自动创建并存储在master数据库中，其名称都是以“sp_”为前缀。SQL Server所提供的系统存储过程数量众多，我们可以对系统存储过程按类别进行分组。一些常用的系统存储过程分类包括： 数据库维护计划过程：用于设置确保数据库性能所需的核心维护任务。 分布式查询过程：用于执行和管理分布式查询。 全文检索过程：用于执行和查询全文索引。 日志传送过程：用于配置和管理日志传送。 游标过程：执行游标变量功能。 安全过程：用于管理安全性。 复制过程：用于管理复制。 SQL 事件探查器过程：由 SQL 事件探查器用于监视性能
10、和活动。 系统过程：用于 SQL Server 的常规维护。,12.2 触发器,12.2.1 触发器概述 12.2.2 触发器的工作原理 12.2.3 创建触发器 12.2.4 触发器的应用 12.2.5 管理触发器 12.2.6 触发器的嵌套和递归,12.2 触发器,12.2.1 触发器概述 1. 触发器的概念和作用 从本质上讲，触发器是一种特殊的存储过程，但触发器和普通意义上的存储过程有所不同。存储过程可以通过其名称直接调用，而触发器不能通过名字进行调用，而是通过事件进行触发而被执行的。当对某个数据表进行INSERT、DELETE和UPDATE这些操作时，SQL Server就会自动执行触
11、发器中定义的SQL语句，其作用主要是保证业务规则的实现和数据的完整性及一致性。,12.2.1 触发器概述,1. 触发器的概念和作用(续) 在DBMS中，触发器都是一种非常重要的工具，其优点主要体现在以下几个方面： 可以通过触发器和CHECK约束强制实现业务规则和数据完整性，但使用触发器可以实现比CHECK更为复杂的约束，例如在触发器中可以引用其他表中的列，而约束只能根据逻辑表达式或同一表中的另一列来进行验证。 触发器可以根据对数据的操作，自动地级联修改相应数据表的内容，并可以通过检测修改前后数据的不同状态采取相应的策略。 通过触发器可以禁止违反业务规则或破坏数据完整性的操作，从而避免对数据进行
12、不允许或不正确的修改，保证了数据的安全。,12.2.1 触发器概述,2. 触发器的种类 在SQL Server中提供了两种不同类型的触发器：AFTER触发器和INSTEAD OF触发器。 AFTER型触发器：只能定义在基本表上，当对表执行了INSERT、UPDATE或DELETE操作时，AFTER触发器被触发并执行相应操作。允许对同一个操作定义多个触发器以便可以采取多种不同的对策以响应同一个修改语句，同时还可以利用系统存储过程sp_settriggerorder来指定这些触发器中哪个第一个被触发，哪个最后一个被触发。 INSTEAD OF触发器：是SQL Server 2000的新增功能，它指
13、定只执行触发器本身，而不执行触发它的INSERT、UPDATE或DELETE操作，除了在基本表上之外，还可以在视图上定义INSTEAD OF触发器，通过它可以实现对视图中所引用表的更新。与ATFER触发器不同，对于同一个操作只能定义一个INSTEAD OF触发器。,12.2.2 触发器的工作原理,对于每一个触发器，SQL Server都会自动为其创建两个特殊的表：INSERTED表和DELETED表。这两个表是由系统进行维护的逻辑表，它们并不存储在数据库中，而是存放在内存中，当触发器工作完成时，这两个表便会自动被删除。这两个表的结构与触发器所作用的表的结构完全相同，而且具有只读属性，不允许用户
14、直接对这两个表进行修改，但可以在触发器的SQL语句中对它们进行引用。 如果对一个在其上定义了INSERT触发器的表执行插入操作，那么在向其中写入新行值的同时，也向INSERTED表写入相同的副本。也就是说，INSERTED表中的行总是与触发器所作用的表中的新行相同。 如果对一个在其上定义了DELETE触发器对表执行删除操作，则将要删除的行存放到DELETED表中，一旦遇到终止触发器执行的语句时，可以从DELETED表中将被删除的行恢复出来。 如果对一个在其上定义了UPDATE触发器的表执行更新操作，则可以将UPDATE操作看成是DELETE操作和INSERT操作的共同结果，因此，在DELETE
15、D表中保存更新操作前旧行值，在INSERTED表中保存更新操作后的新行值。,12.2.3 创建触发器,在创建触发器时需要指定触发器的名称、定义触发器的表、触发器何时触发、激活触发器的数据修改操作以及触发器所执行的操作等。创建触发器可以通过Transaction_SQL的CREATE TRIGGER语句，也可以在图形化界面的企业管理器中进行，下面我们分别进行介绍。,12.2.3 创建触发器,1. 使用CREATE TRIGGER语句创建触发器的一般语法规则如下： CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask n sql_statement n ,1
