存储过程与触发器.ppt
第7章 存储过程与触发器,科学出版社,高校计算机,电气与信息管理与商务类规划教材,2012上海市精品课程上海教育高地建设项目,主编 贾铁军 甘泉副主编 沈学东 常艳 连志刚 胡静编著 李向臣 曾刚,数据库原理应用与实践,目 录,教学目标 了解存储过程的特点、类型和作用 理解和掌握存储过程的执行方式 了解DML触发器的工作原理 理解和掌握DDL触发器的特点和创建方式,重点,7.1存储过程概述,7.1.1 存储过程的概念 存储过程（Stored Procedure）是数据库系统中，一组完成特定功能的SQL语句集。经编译后存储在数据库中，用户通过指定存储过程名及给出参数（若此存储过程带有参数）进行调用执行。SQL Server提供了一种方法，可将一些固定的操作集中由SQL Server数据库服务器完成,以实现某个任务,这种方法就是存储过程。,要点复习,可调用(执行)的过程文件,7.1存储过程概述,7.1.1 存储过程的概念 存储过程是一组相关的T-SQL语句集合，提供了一种封装任务的方法，并具有强大的编程功能。存储过程包括4个方面：（1）包含在数据库中执行操作的语句，包括调用(执行)其他存储过程。（2）接受输入参数。（3）状态值返回指示成功或失败。（4）以输出参数的形式，将多个值返回到发起调用的存储过程或客户端应用程序。,7.1存储过程概述,7.1.2 存储过程的特点和类型 1.存储过程的特点：（1）在服务器注册后,可以提高T-SQL语句执行效率.（2）存储过程具有安全性和所有权链接，可执行所有的权限管理。用户可以被授予执行存储过程的权限，而不必拥有直接对存储过程中引用对象的执行权限。（3）存储过程允许用户模块化设计程序，极大地提高了程序设计的效率。例如，存储过程创建之后，可以在程序中任意调用，提高程序的设计效率、提高了应用程序的可维护性。（4）存储过程可以大大减少网络通信流量，这是一条非常重要的使用存储过程的原因。,7.1存储过程概述,2 存储过程的类型在SQL Server系统中，提供3种基本存储过程类型:（1）用户定义的存储过程（2）扩展存储过程（3）系统存储过程 除此之外，还有临时存储过程、远程存储过程等，各自起着不同的作用。,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 END 2.调用(执行)存储过程 EXECUTE Production.proc_name,7.2 存储过程的实现,*7.2.2 创建参数化存储过程1.使用输入参数的准则 若要定义接受参数的存储过程，应在CREATE PROCEDURE语句中声明参数。使用输入参数时，应考虑以下准则：（1）根据情况为参数提供默认值。如果定义了默认值，则用户无需为该参数指定值即可执行存储过程。（2）在存储过程的开头验证所有传入的参数值，以尽早查出缺少的值和无效值。,7.2 存储过程的实现,2.使用输入参数的示例【案例7-4】修改存储过程LongLeadProducts,添加参数MinimumLength,并指定其为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.2 存储过程的实现,7.2.3 查看存储过程 存储过程被创建之后,其名字存储在系统表sysobjects中,源语句则存放在系统表syscomments中。可使用企业管理器或系统存储过程查看用户创建的存储过程.1.用企业管理器查看存储过程 2.用系统存储过程查看存储过程 格式：sp_helpobjname=name类似请见P184.,7.2 存储过程的实现,7.2.4 修改存储过程 在SQL Server统中，可以使用ALTER PROCEDURE语句修改已经存在的存储过程。【案例7-3】修改存储过程，查询制造时间在一天以上的所有产品名称，产品号及制造时间，并将结果按制造时间的降序进行显示。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_rename原存储过程名，新存储过程名2.删除存储过程 若要从当前数据库中删除用户定义的存储过程，应使用DROP PROCEDURE语句。其语法格式为：DROP PROCEDURE 存储过程名,7.2 存储过程的实现,*7.3 触发器应用,1.触发器的概念及类型 触发器是一种特殊类型的存储过程，包括了大量的T-SQL语句。但是触发器又与存储过程不同，例如存储过程可以由用户直接调用执行，但是触发器不能被直接调用执行，它只能自动执行。,在语言事件执行时自动运行的一种特殊类型的存储过程,7.3 触发器应用,1.触发器的概念及类型 按照触发事件的不同，可以把SQL Server系统提供的触发器分成两大类型，即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 系统提供的DML触发器分成3种类型，即INSERT类型、UPDATE类型和DELETE类型。这也是DML触发器的基本类型。,7.3 触发器应用,1.触发器的概念及类型 任何触发器都可以包含影响另外一个表的INSERT、UPDATE或DELETE语句。当允许触发器嵌套时，一个触发器可以修改触发第二个触发器的表，第二个触发器又可以触发第三个触发器。在默认情况下，系统允许触发器嵌套。,7.3 触发器应用,2.触发器的用途 触发器是一种特殊的存储过程，在INSERT，UPDATE或DELETE语句修改指定表中的数据时执行。触发器可查询其他表并且可包含复杂的T-SQL语句。人们通常创建触发器以往不同表中的逻辑相关数据之间实施引用完整性或一致性。,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触发器，该触发器替代常规触发操作执行,还可以在基于一个或多个基表的视图上定义。,7.3 触发器应用,7.3.3 INSERT触发器的工作方式 当执行INSERT语句将要数据插入表或视图时，如果该表或视图配置了INSERT触发器，就会激发该INSERT触发器来执行特定的操作。当INSERT触发器触发时，新行将插入触发器和inserted表。inserted表是一个逻辑表，保留已插入行的副本。inserted表包含由INSERT语句引起的已记入日志的插入活动。,7.3 触发器应用,7.3.4 DELETE触发器的工作方式 DELETE触发器是一种特殊的存储过程，它在每次DELETE语句从配置了该触发器的表或者视图中删除数据时执行。当DELETE触发器激发时，被删除的行将放置在特殊的deleted表中。Deleted表是一个逻辑表，它保留已删除行的副本。,7.3 触发器应用,7.3.5 UPDATE触发器的工作方式 UPDATE触发器是在每次UPDATE语句配置了UPDATE触发器的表或视图中的数据进行更改时执行的触发器。UPDATE触发器的工作过程可视为两步：（1）数据前映像的DELETE步骤。（2）捕获数据后映像是INSERT语句。当UPDATE语句在已定义了触发器的表上执行时，原始行（前映像）移入deleted表，而更新行（后映像）插入inserted表中。,7.3 触发器应用,7.3.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结构及数据库种类,数据库原理应用与实践,诚挚谢意！,
