存储过程与触发器.ppt

《存储过程与触发器.ppt》由会员分享，可在线阅读，更多相关《存储过程与触发器.ppt（28页珍藏版）》请在三一办公上搜索。

1、第7章 存储过程与触发器,科学出版社,高校计算机,电气与信息管理与商务类规划教材,2012上海市精品课程上海教育高地建设项目,主编 贾铁军 甘泉副主编 沈学东 常艳 连志刚 胡静编著 李向臣 曾刚,数据库原理应用与实践,目 录,教学目标 了解存储过程的特点、类型和作用 理解和掌握存储过程的执行方式 了解DML触发器的工作原理 理解和掌握DDL触发器的特点和创建方式,重点,7.1存储过程概述,7.1.1 存储过程的概念 存储过程（Stored Procedure）是数据库系统中，一组完成特定功能的SQL语句集。经编译后存储在数据库中，用户通过指定存储过程名及给出参数（若此存储过程带有参数）进行调

2、用执行。SQL Server提供了一种方法，可将一些固定的操作集中由SQL Server数据库服务器完成,以实现某个任务,这种方法就是存储过程。,要点复习,可调用(执行)的过程文件,7.1存储过程概述,7.1.1 存储过程的概念 存储过程是一组相关的T-SQL语句集合，提供了一种封装任务的方法，并具有强大的编程功能。存储过程包括4个方面：（1）包含在数据库中执行操作的语句，包括调用(执行)其他存储过程。（2）接受输入参数。（3）状态值返回指示成功或失败。（4）以输出参数的形式，将多个值返回到发起调用的存储过程或客户端应用程序。,7.1存储过程概述,7.1.2 存储过程的特点和类型 1.存储过程

3、的特点：（1）在服务器注册后,可以提高T-SQL语句执行效率.（2）存储过程具有安全性和所有权链接，可执行所有的权限管理。用户可以被授予执行存储过程的权限，而不必拥有直接对存储过程中引用对象的执行权限。（3）存储过程允许用户模块化设计程序，极大地提高了程序设计的效率。例如，存储过程创建之后，可以在程序中任意调用，提高程序的设计效率、提高了应用程序的可维护性。（4）存储过程可以大大减少网络通信流量，这是一条非常重要的使用存储过程的原因。,7.1存储过程概述,2 存储过程的类型在SQL Server系统中，提供3种基本存储过程类型:（1）用户定义的存储过程（2）扩展存储过程（3）系统存储过程 除此

4、之外，还有临时存储过程、远程存储过程等，各自起着不同的作用。,7.2.1 创建存储过程可以使用三种创建存储过程方法：（1）利用创建存储过程向导创建存储过程。（2）使用SQL Server企业管理器创建存储过程。（3）使用T-SQL语句中的CREATE PROCEDURE命令创建存储过程(重点介绍)。,7.2 存储过程的实现,7.2.1 创建存储过程 1.创建存储过程 利用T-SQL语句CREATE PROCEDURE命令创建存储过程,包含一些选项，其语法格式如下所示：CREATE PROCEDURE proc_name ASBEGINsql_statement1sql_statement2 E

5、ND 2.调用(执行)存储过程 EXECUTE Production.proc_name,7.2 存储过程的实现,*7.2.2 创建参数化存储过程1.使用输入参数的准则 若要定义接受参数的存储过程，应在CREATE PROCEDURE语句中声明参数。使用输入参数时，应考虑以下准则：（1）根据情况为参数提供默认值。如果定义了默认值，则用户无需为该参数指定值即可执行存储过程。（2）在存储过程的开头验证所有传入的参数值，以尽早查出缺少的值和无效值。,7.2 存储过程的实现,2.使用输入参数的示例【案例7-4】修改存储过程LongLeadProducts,添加参数MinimumLength,并指定其为

6、int数据类型和其默认值为1。ALTER PROC Production.LongLeadProducts MinimumLength int=1-default valueAS IF(MinimumLength 0)-validate验证 BEGIN RAISERROR(Invalid lead time.,14,1)RETURN END,7.2 存储过程的实现,3.调用参数化存储过程EXEC Production.LongLeadProducts MinimumLength=44.使用参数默认值（1）执行存储过程时，没有为参数指定任何值。（2）DEFAULT关键字指定为参数的值。5.输出参

7、数和返回值 通过使用输出参数和返回值，存储过程可将信息返回给进行调用的存储过程和客户端。,7.2 存储过程的实现,7.2.3 查看存储过程 存储过程被创建之后,其名字存储在系统表sysobjects中,源语句则存放在系统表syscomments中。可使用企业管理器或系统存储过程查看用户创建的存储过程.1.用企业管理器查看存储过程 2.用系统存储过程查看存储过程 格式：sp_helpobjname=name类似请见P184.,7.2 存储过程的实现,7.2.4 修改存储过程 在SQL Server统中，可以使用ALTER PROCEDURE语句修改已经存在的存储过程。【案例7-3】修改存储过程，

8、查询制造时间在一天以上的所有产品名称，产品号及制造时间，并将结果按制造时间的降序进行显示。ALTER PROC Production.LongLeadProducts AS SELECT Name,ProductNumber,DaysToManufacture FROM Production.Product WHERE DaysToManufacture=1 ORDER BY DaysToManufacture DESC,Name GO,7.2 存储过程的实现,7.2.5 更名或删除存储过程1.更名存储过程 修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：sp_ren

9、ame原存储过程名，新存储过程名2.删除存储过程 若要从当前数据库中删除用户定义的存储过程，应使用DROP PROCEDURE语句。其语法格式为：DROP PROCEDURE 存储过程名,7.2 存储过程的实现,*7.3 触发器应用,1.触发器的概念及类型 触发器是一种特殊类型的存储过程，包括了大量的T-SQL语句。但是触发器又与存储过程不同，例如存储过程可以由用户直接调用执行，但是触发器不能被直接调用执行，它只能自动执行。,在语言事件执行时自动运行的一种特殊类型的存储过程,7.3 触发器应用,1.触发器的概念及类型 按照触发事件的不同，可以把SQL Server系统提供的触发器分成两大类型，

10、即DML触发器和DDL触发器。在SQL Server中，可以创建CLR触发器，既可以是DML触发器，也可以是DDL触发器。,由于SQLServer 与.NET Framework 公共语言运行库(CLR)相集成，可用任何.NET Framework 语言创建 CLR 触发器。,7.3 触发器应用,1.触发器的概念及类型 当数据库中发生数据操纵语言(data manipulation language，DML)事件时将调用DML触发器。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。按照触发器事件类型的不同，可以把SQL Server 系统提供的DM

11、L触发器分成3种类型，即INSERT类型、UPDATE类型和DELETE类型。这也是DML触发器的基本类型。,7.3 触发器应用,1.触发器的概念及类型 任何触发器都可以包含影响另外一个表的INSERT、UPDATE或DELETE语句。当允许触发器嵌套时，一个触发器可以修改触发第二个触发器的表，第二个触发器又可以触发第三个触发器。在默认情况下，系统允许触发器嵌套。,7.3 触发器应用,2.触发器的用途 触发器是一种特殊的存储过程，在INSERT，UPDATE或DELETE语句修改指定表中的数据时执行。触发器可查询其他表并且可包含复杂的T-SQL语句。人们通常创建触发器以往不同表中的逻辑相关数据

12、之间实施引用完整性或一致性。,7.3 触发器应用,7.3.2 创建触发器可使用CREATE TRIGGER语句创建触发器，其语法如下：CREATE TRIGGER trigger_name ON table|view FOR|AFTER|INSTEAD OF|INSTERT|UPDATE|DELETE WITH APPEND AS sql_statement,7.3 触发器应用,7.3.2 创建触发器 触发器的类型，有两类触发器DML和DDL，其中，DML触发器也有两类：（1）AFTER触发器。该触发器在执INSERT,UPDATE,DELETE语句的之后执行。（2）INSTEAD OF触发器

13、，该触发器替代常规触发操作执行,还可以在基于一个或多个基表的视图上定义。,7.3 触发器应用,7.3.3 INSERT触发器的工作方式 当执行INSERT语句将要数据插入表或视图时，如果该表或视图配置了INSERT触发器，就会激发该INSERT触发器来执行特定的操作。当INSERT触发器触发时，新行将插入触发器和inserted表。inserted表是一个逻辑表，保留已插入行的副本。inserted表包含由INSERT语句引起的已记入日志的插入活动。,7.3 触发器应用,7.3.4 DELETE触发器的工作方式 DELETE触发器是一种特殊的存储过程，它在每次DELETE语句从配置了该触发器的

14、表或者视图中删除数据时执行。当DELETE触发器激发时，被删除的行将放置在特殊的deleted表中。Deleted表是一个逻辑表，它保留已删除行的副本。,7.3 触发器应用,7.3.5 UPDATE触发器的工作方式 UPDATE触发器是在每次UPDATE语句配置了UPDATE触发器的表或视图中的数据进行更改时执行的触发器。UPDATE触发器的工作过程可视为两步：（1）数据前映像的DELETE步骤。（2）捕获数据后映像是INSERT语句。当UPDATE语句在已定义了触发器的表上执行时，原始行（前映像）移入deleted表，而更新行（后映像）插入inserted表中。,7.3 触发器应用,7.3.

15、6 INSTEAD OF触发器的工作方式 INSTEAD OF触发器替代常规触发器操作执行。INSTEAD OF触发器还可以在基于一个或多个基表的视图上定义。INSTEAD OF触发器主要具有优点：（1）允许由多个基表组成的视图支持引用表中数据的插入、更新和删除操作。（2）允许编写逻辑语句以拒绝执行批处理的某些命令，同时不影响批处理其他部分的成功执行。（3）允许针对符合指定条件情况的指定备选数据库操作。,7.3 触发器应用,现在可以来总结一下，触发器可用来维持数据完整性，它防止了对数据的不正确、未授权的、和不一致的改变。当触发器激发对INSERT，DELETE，或UPDATE语句的响应时，两个特殊的表被创建。这些是插入和删除表：（1）inserted表包含插入在触发器表中的所有记录的拷贝.（2）deleted表包含了已从触发器表中被删除的所有记录。,讨论思考：（1）什么是存储过程？用途是什么？（2）什么是触发器？用途是什么？（3）建立及查看存储过程的语句是什么？,3.3 SQL Server结构及数据库种类,数据库原理应用与实践,诚挚谢意！,