存储过程与触发器.ppt

《存储过程与触发器.ppt》由会员分享，可在线阅读，更多相关《存储过程与触发器.ppt（28页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、存储过程与触发器 Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望目目 录录教学目标教学目标 了解存储过程的特点、类型和作用了解存储过程的特点、类型和作用 理解和掌握理解和掌握存储过程的执行方式存储过程的执行方式 了解了解DML触发器触发器的工作原理的工作原理 理解和掌握理解和掌握DDL触发器的特点和创建方式触发器的特点和创建方式重点重点重点重点7.1存储过程概述存储过程概述 n7.1.1 7.1.1 存储过程的概念存储过程的概念 存储过程存储过程存储过程存储过程（

2、Stored ProcedureStored Procedure）是数据库系统中，）是数据库系统中，一组一组完成完成特定功能特定功能的的SQLSQL语句集语句集。经。经编译后编译后存储在数据存储在数据库中，用户库中，用户通过通过指定指定存储过程名存储过程名及给出及给出参数参数（若此存（若此存储过程带有参数）储过程带有参数）进行调用执行进行调用执行。SQL Server SQL Server提供了提供了一种方法，可将一些固定的一种方法，可将一些固定的操操作集作集中中由由SQL ServerSQL Server数据库服务器数据库服务器完成完成,以实现以实现某个某个任任务务,这种这种方法方法就是就是

3、存储过程存储过程。要点复习要点复习要点复习要点复习可调用可调用可调用可调用(执行执行)的的的的过程文件过程文件过程文件过程文件7.1存储过程概述存储过程概述 n7.1.1 7.1.1 存储过程的概念存储过程的概念 存储过程存储过程是一组相关的是一组相关的T-SQLT-SQL语句集合语句集合，提供了提供了一种一种封封装装任务任务的的方法方法，并具有强大的编程功能。，并具有强大的编程功能。n 存储过程存储过程包括包括4 4个方面个方面：n（1 1）包含在数据库中）包含在数据库中执行执行操作的语句，包括操作的语句，包括调用调用(执行执行)其他存储过程。其他存储过程。n（2 2）接受接受输入参数。输入

4、参数。n（3 3）状态值）状态值返回返回指示成功或失败。指示成功或失败。n（4 4）以输出参数的形式，将多个值）以输出参数的形式，将多个值返回到返回到发起调用的存发起调用的存储过程或客户端应用程序。储过程或客户端应用程序。7.1存储过程概述存储过程概述 n7.1.2 7.1.2 存储过程的特点和类型存储过程的特点和类型 1.1.存储过程的存储过程的特点特点：n（1 1）在服务器在服务器注册注册后后,可以可以提高提高T-SQLT-SQL语句语句执行效率执行效率.n（2 2）存储过程）存储过程具有具有安全性安全性和和所有权链接所有权链接，可执行可执行所有的权限管所有的权限管理。用户可以理。用户可以

5、被授予执行被授予执行存储过程的权限，而不必拥有直接对存存储过程的权限，而不必拥有直接对存储过程中引用对象的执行权限。储过程中引用对象的执行权限。n（3 3）存储过程允许用户）存储过程允许用户模块化模块化设计设计程序程序，极大地，极大地提高了提高了程序设程序设计的效率。计的效率。例如例如，存储过程创建之后，可以在程序中，存储过程创建之后，可以在程序中任意任意调用调用，提高程序的设计效率、提高了应用程序的可维护性。提高程序的设计效率、提高了应用程序的可维护性。n（4 4）存储过程可以）存储过程可以大大减少大大减少网络网络通信流量通信流量，这是一条非常重要，这是一条非常重要的使用存储过程的原因。的使

6、用存储过程的原因。7.1存储过程概述存储过程概述 n2 2 存储过程的类型存储过程的类型n在在SQL ServerSQL Server系统中，系统中，提供提供3 3种种基本存储过程基本存储过程类型类型:n（1 1）用户定义的存储过程）用户定义的存储过程n（2 2）扩展存储过程）扩展存储过程n（3 3）系统存储过程）系统存储过程n 除此之外，还有除此之外，还有临时存储过程、远程存储过程临时存储过程、远程存储过程等，各自等，各自起着不同的作用。起着不同的作用。n7.2.1 7.2.1 创建存储过程创建存储过程n可以可以使用使用三种三种创建存储过程创建存储过程方法方法：n（1 1）利用）利用创建创建

7、存储过程存储过程向导向导创建创建存储过程。存储过程。n（2 2）使用）使用SQL ServerSQL Server企业管理器企业管理器创建创建存储过程。存储过程。n（3 3）使用）使用T-SQLT-SQL语句中的语句中的CREATE PROCEDURECREATE PROCEDURE命令命令创建创建存存储过程储过程(重点介绍重点介绍)。7.2 存储过程的实现存储过程的实现n7.2.1 7.2.1 创建存储过程创建存储过程n 1.1.创建存储过程创建存储过程n 利用利用T-SQLT-SQL语句语句CREATE PROCEDURECREATE PROCEDURE命令命令创建创建存储过程存储过程,包

8、包含一些选项，其含一些选项，其语法格式语法格式如下所示：如下所示：n CREATE PROCEDURE CREATE PROCEDURE proc_nameproc_namen AS ASnBEGINBEGINnsql_statement1sql_statement1nsql_statement2sql_statement2n END END n 2.2.调用调用(执行执行)存储过程存储过程 n EXECUTE Production.EXECUTE Production.proc_nameproc_name7.2 存储过程的实现存储过程的实现n*7.2.2*7.2.2 创建参数化存储过程创建参

9、数化存储过程n1.1.使用输入参数的准则使用输入参数的准则n 若要若要定义定义接受参数的接受参数的存储过程存储过程，应在，应在CREATE PROCEDURECREATE PROCEDURE语句中语句中声明声明参数参数。使用使用输入参数时，应输入参数时，应考虑考虑以下以下准则准则：n（1 1）根据情况为参数）根据情况为参数提供提供默认值默认值。如果定义了默认值，则。如果定义了默认值，则用户无需为该参数用户无需为该参数指定指定值即可值即可执行执行存储过程。存储过程。n（2 2）在存储过程的开头）在存储过程的开头验证验证所有传入的所有传入的参数值参数值，以尽早查，以尽早查出缺少的值和无效值。出缺少

10、的值和无效值。7.2 存储过程的实现存储过程的实现n2.2.使用输入参数的示例使用输入参数的示例n【案例案例7-47-4】修改修改存储过程存储过程LongLeadProducts,LongLeadProducts,添加添加参数参数MinimumLength,MinimumLength,并并指定指定其为其为intint数据类型和其默认值为数据类型和其默认值为1 1。nALTER PROC Production.LongLeadProductsn MinimumLength int=1-default valuenASn IF(MinimumLength=1n ORDER BY DaysToMan

11、ufacture DESC,Namen GO7.2 存储过程的实现存储过程的实现n 7.2.5 7.2.5 更名或删除存储过程更名或删除存储过程n1.1.更名存储过程更名存储过程n 修改修改存储过程的名称存储过程的名称可以可以使用使用系统存储过程系统存储过程sp_renamesp_rename，其，其语语法形式法形式如下：如下：n sp_renamesp_rename原存储过程名，新存储过程名原存储过程名，新存储过程名n2.2.删除存储过程删除存储过程n 若要从当前数据库中若要从当前数据库中删除删除用户定义的存储过程用户定义的存储过程，应使用应使用DROP DROP PROCEDUREPROC

12、EDURE语句。其语句。其语法格式语法格式为：为：n DROP PROCEDURE DROP PROCEDURE 存储过程名存储过程名7.2 存储过程的实现存储过程的实现*7.3 触发器应用触发器应用 1.触发器的概念及类型触发器的概念及类型 触发器触发器是一种是一种特殊类型特殊类型的的存储过程存储过程，包括了大量的，包括了大量的T-SQL语句。但是触发器又与存储过程语句。但是触发器又与存储过程不同不同，例如存，例如存储过程可以由用户直接调用执行，但是触发器储过程可以由用户直接调用执行，但是触发器不能被不能被直接直接调用执行调用执行，它，它只能只能自动执行自动执行。在语言事件执行时自动运行在语

13、言事件执行时自动运行的一种特殊类型的存储过程的一种特殊类型的存储过程7.3 触发器应用触发器应用 1.触发器的概念及类型触发器的概念及类型 按照按照触发事件触发事件的的不同不同，可以把，可以把SQL Server系统提供系统提供的触发器的触发器分成分成两大两大类型类型，即，即DML触发器和触发器和DDL触发器。触发器。在在SQL Server中，可以中，可以创建创建CLR触发器触发器，既可以是，既可以是DML触发器，也可以是触发器，也可以是DDL触发器。触发器。由于SQLServer 与.NET Framework 公共语言运行库公共语言运行库(CLR)相集成，可用任何.NET Framewo

14、rk 语言创建 CLR 触发器。7.3 触发器应用触发器应用 1.触发器的概念及类型触发器的概念及类型 当数据库中当数据库中发生发生数据操纵语言数据操纵语言(data manipulation language，DML)事件事件时将时将调用调用DML触发器。触发器。DML事事件包括在指定表或视图中件包括在指定表或视图中修改修改数据的数据的INSERT语句、语句、UPDATE语句或语句或DELETE语句。语句。按照按照触发器事件类型的不同，可以触发器事件类型的不同，可以把把SQL Server 系统系统提供提供的的DML触发器触发器分成分成3种类型种类型，即，即INSERT类型、类型、UPDAT

15、E类型和类型和DELETE类型。这也是类型。这也是DML触发器的基触发器的基本类型。本类型。7.3 触发器应用触发器应用 1.触发器的概念及类型触发器的概念及类型 任何触发器都可以包含影响另外一个表的任何触发器都可以包含影响另外一个表的INSERT、UPDATE或或DELETE语句。当允许触发器语句。当允许触发器嵌套嵌套时，一个时，一个触发器可以修改触发第二个触发器的表，第二个触发器触发器可以修改触发第二个触发器的表，第二个触发器又可以触发第三个触发器。在默认情况下，系统允许触又可以触发第三个触发器。在默认情况下，系统允许触发器嵌套。发器嵌套。7.3 触发器应用触发器应用 2.触发器的用途触发

16、器的用途 触发器触发器是一种特殊的是一种特殊的存储过程存储过程，在，在INSERT，UPDATE或或DELETE语句修改指定表中的数据时执语句修改指定表中的数据时执行。触发器可查询其他表并且可包含复杂的行。触发器可查询其他表并且可包含复杂的T-SQL语句。人们通常创建触发器以往不同表中的逻辑相语句。人们通常创建触发器以往不同表中的逻辑相关数据之间实施引用完整性或一致性。关数据之间实施引用完整性或一致性。7.3 触发器应用触发器应用 7.3.2 7.3.2 创建触发器创建触发器可使用可使用CREATE TRIGGER语句语句创建创建触发器，其触发器，其语法语法如下：如下：CREATE TRIGG

17、ER trigger_name ON table|view FOR|AFTER|INSTEAD OF|INSTERT|UPDATE|DELETE WITH APPEND AS sql_statement7.3 触发器应用触发器应用 7.3.2 7.3.2 创建触发器创建触发器 触发器的类型触发器的类型，有两类触发器，有两类触发器DML和和DDL，其中，其中，DML触发器也有触发器也有两类两类：（1）AFTER触发器。该触发器在执触发器。该触发器在执INSERT,UPDATE,DELETE语句的之后执行。语句的之后执行。（2）INSTEAD OF触发器，该触发器替代常规触发操作触发器，该触发器替

18、代常规触发操作执行执行,还可以在基于一个或多个基表的视图上定义。还可以在基于一个或多个基表的视图上定义。7.3 触发器应用触发器应用 7.3.3 INSERT7.3.3 INSERT触发器的工作方式触发器的工作方式 当执行当执行INSERT语句将要数据插入表或视图时，语句将要数据插入表或视图时，如果该表或视图配置了如果该表或视图配置了INSERT触发器，就会激发该触发器，就会激发该INSERT触发器来执行特定的操作。触发器来执行特定的操作。当当INSERT触发器触发时，新行将插入触发器和触发器触发时，新行将插入触发器和inserted表。表。inserted表是一个逻辑表，保留已插入表是一个逻

19、辑表，保留已插入行的副本。行的副本。inserted表包含由表包含由INSERT语句引起的已语句引起的已记入日志的插入活动。记入日志的插入活动。7.3 触发器应用触发器应用 7.3.4 DELETE7.3.4 DELETE触发器的工作方式触发器的工作方式 DELETE触发器是一种特殊的存储过程，它在每次触发器是一种特殊的存储过程，它在每次DELETE语句从配置了该触发器的表或者视图中删除数语句从配置了该触发器的表或者视图中删除数据时执行。据时执行。当当DELETE触发器激发时，被删除的行将放置在特殊触发器激发时，被删除的行将放置在特殊的的deleted表中。表中。Deleted表是一个逻辑表，

20、它保留已删表是一个逻辑表，它保留已删除行的副本。除行的副本。7.3 触发器应用触发器应用 7.3.5 UPDATE7.3.5 UPDATE触发器的工作方式触发器的工作方式 UPDATE触发器是在每次触发器是在每次UPDATE语句配置了语句配置了UPDATE触发器的表或视图中的数据进行更改时执行的触发器的表或视图中的数据进行更改时执行的触发器。触发器。UPDATE触发器的触发器的工作过程工作过程可视为可视为两步两步：（1）数据前映像的）数据前映像的DELETE步骤。步骤。（2）捕获数据后映像是）捕获数据后映像是INSERT语句。语句。当当UPDATE语句在已定义了触发器的表上执行时，原语句在已定

21、义了触发器的表上执行时，原始行（前映像）移入始行（前映像）移入deleted表，而更新行（后映像）插表，而更新行（后映像）插入入inserted表中。表中。7.3 触发器应用触发器应用 7.3.6 INSTEAD OF7.3.6 INSTEAD OF触发器的工作方式触发器的工作方式 INSTEAD OF触发器替代常规触发器操作执行。触发器替代常规触发器操作执行。INSTEAD OF触发器还可以在基于一个或多个基表的视触发器还可以在基于一个或多个基表的视图上定义。图上定义。INSTEAD OF触发器触发器主要具有主要具有优点优点：（1）允许由多个基表组成的视图支持引用表中数据的插）允许由多个基表

22、组成的视图支持引用表中数据的插入、更新和删除操作。入、更新和删除操作。（2）允许编写逻辑语句以拒绝执行批处理的某些命令，）允许编写逻辑语句以拒绝执行批处理的某些命令，同时不影响批处理其他部分的成功执行。同时不影响批处理其他部分的成功执行。（3）允许针对符合指定条件情况的指定备选数据库操作。）允许针对符合指定条件情况的指定备选数据库操作。7.3 触发器应用触发器应用 现在可以来现在可以来总结总结一下，触发器可用来维持数据完整一下，触发器可用来维持数据完整性，它防止了对数据的不正确、未授权的、和不一致的性，它防止了对数据的不正确、未授权的、和不一致的改变。当触发器激发对改变。当触发器激发对INSE

23、RT，DELETE，或，或UPDATE语句的响应时，语句的响应时，两个特殊的表被创建。这些是插入和删除表：两个特殊的表被创建。这些是插入和删除表：（1）inserted表包含插入在触发器表中的所有记录的拷贝表包含插入在触发器表中的所有记录的拷贝.（2）deleted表包含了已从触发器表中被删除的所有记录。表包含了已从触发器表中被删除的所有记录。讨论思考讨论思考讨论思考讨论思考：（1）什么是存储过程？用途是什么？）什么是存储过程？用途是什么？（2）什么是触发器？用途是什么？）什么是触发器？用途是什么？（3）建立及查看存储过程的语句是什么？）建立及查看存储过程的语句是什么？3.3 SQL Server结构及数据库种类结构及数据库种类数据库原理应用与实践数据库原理应用与实践数据库原理应用与实践数据库原理应用与实践