存储过程与触发器 (2).ppt

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

1、第第10章章 存储过程与触发器存储过程与触发器n n10.110.1存储过程概述存储过程概述存储过程概述存储过程概述n n10.210.2存储过程的操作存储过程的操作存储过程的操作存储过程的操作n n10.310.3创建和执行带参数的存储过程创建和执行带参数的存储过程创建和执行带参数的存储过程创建和执行带参数的存储过程n n10.410.4存储过程的重新编译存储过程的重新编译存储过程的重新编译存储过程的重新编译n n10.510.5系统存储过程与扩展存储过程系统存储过程与扩展存储过程系统存储过程与扩展存储过程系统存储过程与扩展存储过程n n10.610.6案例中的存储过程案例中的存储过程案例中

2、的存储过程案例中的存储过程n n10.710.7触发器概述触发器概述触发器概述触发器概述n n10.810.8触发器的创建执行触发器的创建执行触发器的创建执行触发器的创建执行n n10.910.9触发器的操作触发器的操作触发器的操作触发器的操作n n10.1010.10嵌套触发器嵌套触发器嵌套触发器嵌套触发器n n10.1110.11案例中的触发器案例中的触发器案例中的触发器案例中的触发器5/27/20235/27/20231 1主讲：王颂华主讲：王颂华第第10章章 存储过程与触发器存储过程与触发器 存存储储过过程程和和触触发发器器是是SQL SQL ServerServer数数据据库库系系统

3、统重重要要的的数数据据库库对对象象，以以SQL SQL Server Server 20052005为为后后台台数数据据库库创创建建的的应应用用具具有有重重要要的的应应用用价价值值。本本章章介介绍绍存储过程和触发器的概念、作用和基本操作。存储过程和触发器的概念、作用和基本操作。5/27/20235/27/20232 2主讲：王颂华主讲：王颂华10.1 存储过程概述存储过程概述n n10.1.1 10.1.1 存储过程的概念存储过程的概念 存储过程是一种数据库，是为了实现某个特定任务，以存储过程是一种数据库，是为了实现某个特定任务，以一个存储单元的形式存储在服务器上的一组一个存储单元的形式存储在

4、服务器上的一组T-SQLT-SQL语句的集合。语句的集合。也可以把存储过程看成是以数据库对象形式存储在也可以把存储过程看成是以数据库对象形式存储在SQL ServerSQL Server中的一段程序或函数。存储过程既可以是一些简单的中的一段程序或函数。存储过程既可以是一些简单的SQLSQL语句，语句，如如SELECT*FROM authorsSELECT*FROM authors，也可以由一系列用来对数据库，也可以由一系列用来对数据库实现复杂商务规则的实现复杂商务规则的SQLSQL语句或控制流语句所组成。语句或控制流语句所组成。5/27/20235/27/20233 3主讲：王颂华主讲：王颂华

5、10.1 存储过程概述存储过程概述n n10.1.2 10.1.2 存储过程的类型存储过程的类型 在在SQL Server 2005SQL Server 2005中，存储过程可以分为三类：用户中，存储过程可以分为三类：用户自定义的存储过程、系统存储过程和扩展存储过程。自定义的存储过程、系统存储过程和扩展存储过程。5/27/20235/27/20234 4主讲：王颂华主讲：王颂华n n10.2.1 10.2.1 创建存储过程创建存储过程n n10.2.2 10.2.2 执行存储过程执行存储过程n n10.2.3 10.2.3 查看存储过程查看存储过程n n10.2.4 10.2.4 修改存储过程

6、修改存储过程n n10.2.5 10.2.5 删除存储过程删除存储过程 10.2 存储过程的操作存储过程的操作5/27/20235/27/20235 5主讲：王颂华主讲：王颂华10.2 存储过程的操作存储过程的操作n n10.2.1 10.2.1 创建存储过程创建存储过程 创建存储过程需要注意：创建存储过程需要注意：n n（1 1）只能在当前数据库中创建存储过程。）只能在当前数据库中创建存储过程。n n（2 2）创建者不仅要有数据库的）创建者不仅要有数据库的CREATE PROCEDURECREATE PROCEDURE权限，权限，还要有对架构的还要有对架构的ALTERAALTERA权限。权限

7、。n n（3 3）存储过程是数据库对象，其名称必须遵循标识符命）存储过程是数据库对象，其名称必须遵循标识符命名规则。名规则。n n（4 4）不能将）不能将CREATE PROCEDURECREATE PROCEDURE语句与其他语句与其他Transact-SQLTransact-SQL语句组合到一个批处理中。语句组合到一个批处理中。n n（5 5）创建存储过程时应指定：输入参数和向调用过程或）创建存储过程时应指定：输入参数和向调用过程或批处理返回的输出参数，执行数据库操作的编程语句，返批处理返回的输出参数，执行数据库操作的编程语句，返回调用过程或批处理的状态值。回调用过程或批处理的状态值。5/

8、27/20235/27/20236 6主讲：王颂华主讲：王颂华10.2 存储过程的操作存储过程的操作n n使用使用Transact-SQLTransact-SQL语句创建存储过程语句创建存储过程 使用使用Transact-SQLTransact-SQL语句创建存储过程语法格式如下：语句创建存储过程语法格式如下：CREATE PROCEDURE CREATE PROCEDURE procedure_name;numberprocedure_name;number parameter parameter data_typeVARYING=defaultOUTPUT,data_typeVARYING

9、=defaultOUTPUT,n n WITHRECOMPILE WITHRECOMPILEENCRYPTIONENCRYPTIONRECOMPILERECOMPILE ENCRYPTION ENCRYPTION FOR REPLICATION FOR REPLICATION AS AS sql_statetion sql_statetion n n 5/27/20235/27/20237 7主讲：王颂华主讲：王颂华10.2 存储过程的操作存储过程的操作n n使用使用SQL ServerSQL Server管理平台创建存储过程见课本管理平台创建存储过程见课本180180页的页的【例例10-21

10、0-2】n n10.2.2 10.2.2 执行存储过程执行存储过程 对于存储在服务器上的存储过程可使用对于存储在服务器上的存储过程可使用EXECUTEEXECUTE命命令来执行。其语法格式如下：令来执行。其语法格式如下：EXECUTE EXECUTE return_status=return_status=procedure_name;number procedure_name;number procedure_name_varprocedure_name_var parameter=value parameter=valuevariableOUTPUT variableOUTPUT DEFA

11、ULTDEFAULT ,:n ,:n WITH RECOMPLIE WITH RECOMPLIE5/27/20235/27/20238 8主讲：王颂华主讲：王颂华10.2 存储过程的操作存储过程的操作n n10.2.3 10.2.3 查看存储过程查看存储过程 存储过程创建好后，其名称保存在系统表存储过程创建好后，其名称保存在系统表sysobjectssysobjects中，其源代码保存在中，其源代码保存在syscommentssyscomments中，再通过中，再通过IDID字段进行字段进行关联。如果需要查看存储过程的相关信息，可以直接使用关联。如果需要查看存储过程的相关信息，可以直接使用系统

12、表，也可以使用系统的存储过程，还可以使用系统表，也可以使用系统的存储过程，还可以使用SQL SQL ServerServer管理平台。管理平台。n n10.2.4 10.2.4 修改存储过程修改存储过程 当存储过程所依赖的基本表发生变化或用户有需要时，当存储过程所依赖的基本表发生变化或用户有需要时，可以对可以对 存储过程的定义或参数进行修改，修改通过执行存储过程的定义或参数进行修改，修改通过执行CREATE PROCEDURECREATE PROCEDURE语句创建过程，不会改变权限，也不影语句创建过程，不会改变权限，也不影响相关的存储过程或触发器。响相关的存储过程或触发器。修改可通过使用修改

13、可通过使用Transact-SQLTransact-SQL语句进行，也可使用语句进行，也可使用SQL ServerSQL Server管理平台进行修改。管理平台进行修改。5/27/20235/27/20239 9主讲：王颂华主讲：王颂华10.2 存储过程的操作存储过程的操作n n10.2.5 10.2.5 删除存储过程删除存储过程 当存储过程没有存在的意义时，可以使用当存储过程没有存在的意义时，可以使用DROP DROP PROCEDUREPROCEDURE语句或语句或SQL ServerSQL Server管理平台将其删除。管理平台将其删除。n n【例例例例10101010-7 7 7 7】

14、删除存储过程删除存储过程st_jsjbjst_jsjbj。代码如下：代码如下：USE student USE student GO GO DROP PROCEDURE st_jsjbj DROP PROCEDURE st_jsjbj GO GO5/27/20235/27/20231010主讲：王颂华主讲：王颂华 带带参参数数的的存存储储过过程程可可以以扩扩展展存存储储过过程程的的功功能能。使使用用输输入入参参数数，可可以以将将外外部部信信息息传传入入存存储储过过程程；使使用用输输出出参参数数，可可以以将将存存储储过过程程的的信信息息传传到到外外部部。创创建建带带参参数数的的存存储储过过程程时时

15、，参参数数可可以以是是一一个个，也也可可以以是是多多个个，有有多多个个参参数数时时，参参数数之之间间用用逗逗号号分分隔隔；所所有有类类型型的的数数据据均均可可以以作作为为存存储储过过程程的的参参数数，一一般般情情况况下下，参参数数的的数据类型要与其相关字段的数据类型一致。数据类型要与其相关字段的数据类型一致。10.3 创建和执行带参数的存储创建和执行带参数的存储过程过程5/27/20235/27/20231111主讲：王颂华主讲：王颂华 存存储储过过程程第第一一次次执执行行后后，其其被被编编译译的的代代码码将将驻驻留留在在高高速速缓缓存存中中，当当用用户户再再次次执执行行该该存存储储过过程程时

16、时，SQL SQL ServerServer将将其其从从缓缓存存中中调调出出执执行行。在在使使用用了了存存储储过过程程后后，有有时时可可能能会会因因为为某某些些原原因因，必必须须向向表表中中添添加加数数据据列列或或为为表表添添加加索索引引，从从而而改改变变数数据据库库的的逻逻辑辑结结构构，此此时时SQL SQL ServerServer不不自自动动执执行行优优化化，直直到到下下一一次次重重新新启启动动后，再运行该存储过程。后，再运行该存储过程。10.4存储过程的重新编译存储过程的重新编译 5/27/20235/27/20231212主讲：王颂华主讲：王颂华10.5系统存储过程与扩展存储系统存储

17、过程与扩展存储过程过程 在在SQL ServerSQL Server中还有两类重要的存储过程中还有两类重要的存储过程:系统存系统存储过程和扩展存储过程。这些存储过程为用户管理数据库、储过程和扩展存储过程。这些存储过程为用户管理数据库、获取系统信息、查看系统对象提供了很大的帮助。下面简获取系统信息、查看系统对象提供了很大的帮助。下面简单介绍这两类存储过程。单介绍这两类存储过程。n n 系统存储过程系统存储过程 SQL Server 2005 SQL Server 2005提供了上百个系统存储过程，提供了上百个系统存储过程，这些系统存储过程可以帮助用户很容易地管理这些系统存储过程可以帮助用户很容易

18、地管理SQL SQL ServerServer的数据库。的数据库。n n10.5.2 10.5.2 扩展存储过程扩展存储过程 扩展存储过程是允许用户使用某种编程语言创建的程扩展存储过程是允许用户使用某种编程语言创建的程序，程序中使用序，程序中使用SQL ServerSQL Server开放数据服务的开放数据服务的APIAPI函数，它函数，它们可以直接在们可以直接在SQL ServerSQL Server地址空间中运行。用户可以像使地址空间中运行。用户可以像使用普通的存储过程一样使用它们，也可以将参数传递给它用普通的存储过程一样使用它们，也可以将参数传递给它们并返回结果和状态值。们并返回结果和状

19、态值。5/27/20235/27/20231313主讲：王颂华主讲：王颂华n n1.创建一个加密存储过程n n2.创建带输入参数的存储过程n n3.创建带输出参数的存储过程n n4.创建添加、删除和修改记录存储过程10.6 案例中的存储过程案例中的存储过程 5/27/20235/27/20231414主讲：王颂华主讲：王颂华 触发器（触发器（triggertrigger）是一种特殊类型的存储过）是一种特殊类型的存储过程。与其他存储过程类似，它也由程。与其他存储过程类似，它也由Transact-SQLTransact-SQL语语句组成，可以实现一定的功能；不同的是，触发器句组成，可以实现一定的功

20、能；不同的是，触发器的执行不能通过名称调用来完成，而是当用户对数的执行不能通过名称调用来完成，而是当用户对数据库发生事件（如添加、删除、修改数据）时，将据库发生事件（如添加、删除、修改数据）时，将会自动触发与该事件相关的触发器，使其自动执行。会自动触发与该事件相关的触发器，使其自动执行。触发器不允许带参数，它的定义与表紧密相连，触触发器不允许带参数，它的定义与表紧密相连，触发器可以作为表的一部分。发器可以作为表的一部分。在在SQL Server 2005SQL Server 2005中，触发器可分为两大中，触发器可分为两大类：类：DMLDML触发器和触发器和DDLDDL触发器。触发器。10.7

21、 触发器概述触发器概述 5/27/20235/27/20231515主讲：王颂华主讲：王颂华10.8 触发器的创建执行触发器的创建执行n n10.8.1 Inserted10.8.1 Inserted表和表和DeletedDeleted表表 向表中插入数据时，执行向表中插入数据时，执行INSRETINSRET操作，系统将自动操作，系统将自动创建一个与触发器表结构相同的创建一个与触发器表结构相同的InsertedInserted临时表，用来保临时表，用来保存新插入的记录。删除表中的数据时，执行存新插入的记录。删除表中的数据时，执行DELETEDELETE操作，操作，系统将自动创建一个与触发器表结

22、构相同的系统将自动创建一个与触发器表结构相同的DeletedDeleted临时临时表，用来保存触发器中被删除的记录。方便用户查找当前表，用来保存触发器中被删除的记录。方便用户查找当前删除的数据，修改表中的数据时，执行删除的数据，修改表中的数据时，执行UPDATEUPDATE操作，相当操作，相当于删除一条旧记录，添加一条新记录。被删除的记录放在于删除一条旧记录，添加一条新记录。被删除的记录放在DeletedDeleted表中，添加的记录放在表中，添加的记录放在InsertedInserted表中。表中。5/27/20235/27/20231616主讲：王颂华主讲：王颂华 n n创建触发器创建触发

23、器 创建触发器可以使用创建触发器可以使用Transact-SQLTransact-SQL语句，也可以使用语句，也可以使用SQL ServerSQL Server管理平台。在创建触发器前，必须注意以下几点：管理平台。在创建触发器前，必须注意以下几点：n n （1 1）CREATE TRIGGER CREATE TRIGGER 必须是批处理中的第一条语句，必须是批处理中的第一条语句，并且只能应用到一个表中。并且只能应用到一个表中。n n （2 2）触发器只能在当前的数据库中创建，但可以引用当前）触发器只能在当前的数据库中创建，但可以引用当前数据库的外部对象。数据库的外部对象。n n （3 3）表的

24、所有者具有创建触发器的默认权限，且不能将该）表的所有者具有创建触发器的默认权限，且不能将该权限转给其他用户。权限转给其他用户。n n （4 4）不能在临时表或系统表上创建触发器，触发器可以引）不能在临时表或系统表上创建触发器，触发器可以引用临时表但不能引用系统表。用临时表但不能引用系统表。n n （5 5）如果指定了触发器架构名称来限定触发器，则将以相）如果指定了触发器架构名称来限定触发器，则将以相同的方式限定表名称。同的方式限定表名称。n n （6 6）如果一个表的外键包含对定义的）如果一个表的外键包含对定义的DELETE/UPDATEDELETE/UPDATE操操作的级联，则不能在该表上定

25、义作的级联，则不能在该表上定义INSTEAD OF INSTEAD OF DELETE/UPDATEDELETE/UPDATE触发器。触发器。10.8 触发器的创建执行触发器的创建执行5/27/20235/27/20231717主讲：王颂华主讲：王颂华n n10.9.1 10.9.1 10.9.1 10.9.1 修改触发器修改触发器修改触发器修改触发器 对于建立好的触发器，可以根据需要对其名称及文对于建立好的触发器，可以根据需要对其名称及文本进行修改。通常使用系统存储过程对其进行更名操本进行修改。通常使用系统存储过程对其进行更名操作，使用作，使用SQL ServerSQL Server管理平台

26、或管理平台或Transact-SQLTransact-SQL语句语句修改其文本。修改的内容包括：修改其文本。修改的内容包括：n n（1 1）修改触发器名称；）修改触发器名称；n n（2 2）修改触发器定义；）修改触发器定义；n n（3 3）修改触发器文本。）修改触发器文本。n n10.9.2 10.9.2 10.9.2 10.9.2 禁止、启用和删除触发器禁止、启用和删除触发器禁止、启用和删除触发器禁止、启用和删除触发器 触发器的禁止、启用和删除操作可以使用触发器的禁止、启用和删除操作可以使用Transact-SQLTransact-SQL语句实现，也可以使用语句实现，也可以使用SQL Ser

27、verSQL Server管管理平台实现。理平台实现。n n1.1.1.1.使用使用使用使用Transact-SQLTransact-SQL语句实现语句实现语句实现语句实现n n（1 1）禁止触发器的语法格式：）禁止触发器的语法格式：n n DISABLE TRIGGER DISABLE TRIGGER 触发器名称触发器名称 ON ON 表名表名n n ALTER TABLE ALTER TABLE 表名表名 DISABLE TRIGGER DISABLE TRIGGER 触发器名称触发器名称 10.9 触发器的操作触发器的操作5/27/20235/27/20231818主讲：王颂华主讲：王颂

28、华10.9 触发器的操作触发器的操作n n（2 2）启用触发器的格式：）启用触发器的格式：n n ENABLE TRIGGER ENABLE TRIGGER 触发器名称触发器名称 ON ON 表名表名n n ALTER TABLE ALTER TABLE 表名表名 ENABLE TRIGGER ENABLE TRIGGER 触发器名称触发器名称n n（3 3）删除触发器的格式：）删除触发器的格式：n n DROP DROP TRIGGERTRIGGER触发器名称触发器名称,nn n n2.2.使用使用使用使用SQL ServerSQL Server管理平台实现管理平台实现管理平台实现管理平台实

29、现n n（1 1）启动）启动Microsoft SQL Server Management StudioMicrosoft SQL Server Management Studio，在，在“对象资源管理器对象资源管理器”窗口中，依次展开窗口中，依次展开“数据库数据库”student student“表表”“触发器触发器”。n n（2 2）在）在“触发器触发器”中，右击需要修改的触发器，在弹出的快捷中，右击需要修改的触发器，在弹出的快捷菜单上选择相应的命令。菜单上选择相应的命令。5/27/20235/27/20231919主讲：王颂华主讲：王颂华 在在触触发发器器中中可可以以包包含含影影响响另另

30、外外一一个个表表的的INSERTINSERT、UPDATEUPDATE或或DELETEDELETE语语句句，这这就就是是嵌嵌套套触触发发器器。具具体体说说就就是是，如如果果A A上上的的触触发发器器在在执执行行时时引引发发了了表表B B上上的的触触发发器器，而而表表B B上上的的触触发发器器又又激激活活了了表表C C上上的的触触发发器器，表表C C上上的的触触发发器器又又激激活活了了表表D D上上的的触触发发器器所所有有触触发发器器依依次次触触发发。这这些些触触发发器器不不会会形形成成无无限限循循环环，因因为为SQL SQL ServerServer规规定定触触发发器器最最多多可可嵌嵌套套至至

31、3232层层。如如果果允允许许使使用用嵌嵌套套触触发发器器，且且链链 中中的的一一个个触触发发器器开开始始一一个个无无限限循循环环，如如果果超超出出嵌嵌套套级级，触触发发器器将将被被终终止止执执行行。正正确确地地使使用用嵌嵌套套触触发发器器，可可以以执执行行一一些些有有用用的的操操作作，但但嵌嵌套套触触发发器器比比较较复复杂杂，使使用用时时要要注注意意技技巧巧。例例如如，由由于于触触发发器器在在事事务务中中执执行行，如如果果在在一一系系列列嵌嵌套套触触发发器器的的任任意意层层中中发发生生错错误误，则则整整个个事事务务都都将将被被取取消消，且且所所有有的的数数据据修修改改都都将将回回滚滚。一一般

32、般情情况况下下，在在触触发发器器中中包包含含PRINTPRINT语语句句，用用来来确确定定错错误发生的位置。误发生的位置。10.10 嵌套触发器嵌套触发器5/27/20235/27/20232020主讲：王颂华主讲：王颂华n n1.1.创建一个创建一个创建一个创建一个 INSERT INSERT 触发器触发器触发器触发器n n2.2.创建一个创建一个创建一个创建一个DELETEDELETE触发器触发器触发器触发器n n3.3.创建一个创建一个创建一个创建一个UPDATEUPDATE触发器触发器触发器触发器10.11案例中的触发器案例中的触发器5/27/20235/27/20232121主讲：王

33、颂华主讲：王颂华 本章重点介绍了存储过程和触发器的概念、创建和执行。本章重点介绍了存储过程和触发器的概念、创建和执行。存储过程是对数据库中的任意表或者多个表进行复杂的数据处存储过程是对数据库中的任意表或者多个表进行复杂的数据处理而设计的，需要用户指定它去运行，它才会执行里面的理而设计的，需要用户指定它去运行，它才会执行里面的SQLSQL代码。而触发器是针对某个表进行添加、修改等操作作出的响代码。而触发器是针对某个表进行添加、修改等操作作出的响应，是不需要用户干预而自动运行的。使用触发器是利用应，是不需要用户干预而自动运行的。使用触发器是利用Transact-SQLTransact-SQL语言进行程序设计的较高技术和技巧，需要熟语言进行程序设计的较高技术和技巧，需要熟练掌握。练掌握。本 章 小 结5/27/20235/27/20232222主讲：王颂华主讲：王颂华