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

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

1、2023/10/11,主讲：王颂华,1,第10章 存储过程与触发器,10.1存储过程概述10.2存储过程的操作10.3创建和执行带参数的存储过程10.4存储过程的重新编译10.5系统存储过程与扩展存储过程10.6案例中的存储过程10.7触发器概述10.8触发器的创建执行10.9触发器的操作10.10嵌套触发器10.11案例中的触发器,2023/10/11,主讲：王颂华,2,第10章 存储过程与触发器,存储过程和触发器是SQL Server数据库系统重要的数据库对象，以SQL Server 2005为后台数据库创建的应用具有重要的应用价值。本章介绍存储过程和触发器的概念、作用和基本操作。,202

2、3/10/11,主讲：王颂华,3,10.1 存储过程概述,10.1.1 存储过程的概念 存储过程是一种数据库，是为了实现某个特定任务，以一个存储单元的形式存储在服务器上的一组T-SQL语句的集合。也可以把存储过程看成是以数据库对象形式存储在SQL Server中的一段程序或函数。存储过程既可以是一些简单的SQL语句，如SELECT*FROM authors，也可以由一系列用来对数据库实现复杂商务规则的SQL语句或控制流语句所组成。,2023/10/11,主讲：王颂华,4,10.1 存储过程概述,10.1.2 存储过程的类型 在SQL Server 2005中，存储过程可以分为三类：用户自定义的

3、存储过程、系统存储过程和扩展存储过程。,2023/10/11,主讲：王颂华,5,10.2.1 创建存储过程10.2.2 执行存储过程10.2.3 查看存储过程10.2.4 修改存储过程10.2.5 删除存储过程,10.2 存储过程的操作,2023/10/11,主讲：王颂华,6,10.2 存储过程的操作,10.2.1 创建存储过程 创建存储过程需要注意：（1）只能在当前数据库中创建存储过程。（2）创建者不仅要有数据库的CREATE PROCEDURE权限，还要有对架构的ALTERA权限。（3）存储过程是数据库对象，其名称必须遵循标识符命名规则。（4）不能将CREATE PROCEDURE语句与其

4、他Transact-SQL语句组合到一个批处理中。（5）创建存储过程时应指定：输入参数和向调用过程或批处理返回的输出参数，执行数据库操作的编程语句，返回调用过程或批处理的状态值。,2023/10/11,主讲：王颂华,7,10.2 存储过程的操作,使用Transact-SQL语句创建存储过程 使用Transact-SQL语句创建存储过程语法格式如下：CREATE PROCEDURE procedure_name;number parameter data_typeVARYING=defaultOUTPUT,n WITHRECOMPILEENCRYPTIONRECOMPILE ENCRYPTION

5、 FOR REPLICATION AS sql_statetion n,2023/10/11,主讲：王颂华,8,10.2 存储过程的操作,使用SQL Server管理平台创建存储过程见课本180页的【例10-2】10.2.2 执行存储过程 对于存储在服务器上的存储过程可使用EXECUTE命令来执行。其语法格式如下：EXECUTE return_status=procedure_name;number procedure_name_var parameter=valuevariableOUTPUT DEFAULT,:n WITH RECOMPLIE,2023/10/11,主讲：王颂华,9,10.

6、2 存储过程的操作,10.2.3 查看存储过程 存储过程创建好后，其名称保存在系统表sysobjects中，其源代码保存在syscomments中，再通过ID字段进行关联。如果需要查看存储过程的相关信息，可以直接使用系统表，也可以使用系统的存储过程，还可以使用SQL Server管理平台。10.2.4 修改存储过程 当存储过程所依赖的基本表发生变化或用户有需要时，可以对 存储过程的定义或参数进行修改，修改通过执行CREATE PROCEDURE语句创建过程，不会改变权限，也不影响相关的存储过程或触发器。修改可通过使用Transact-SQL语句进行，也可使用SQL Server管理平台进行修改

7、。,2023/10/11,主讲：王颂华,10,10.2 存储过程的操作,10.2.5 删除存储过程 当存储过程没有存在的意义时，可以使用DROP PROCEDURE语句或SQL Server管理平台将其删除。【例10-7】删除存储过程st_jsjbj。代码如下：USE student GO DROP PROCEDURE st_jsjbj GO,2023/10/11,主讲：王颂华,11,带参数的存储过程可以扩展存储过程的功能。使用输入参数，可以将外部信息传入存储过程；使用输出参数，可以将存储过程的信息传到外部。创建带参数的存储过程时，参数可以是一个，也可以是多个，有多个参数时，参数之间用逗号分隔

8、；所有类型的数据均可以作为存储过程的参数，一般情况下，参数的数据类型要与其相关字段的数据类型一致。,10.3 创建和执行带参数的存储过程,2023/10/11,主讲：王颂华,12,存储过程第一次执行后，其被编译的代码将驻留在高速缓存中，当用户再次执行该存储过程时，SQL Server将其从缓存中调出执行。在使用了存储过程后，有时可能会因为某些原因，必须向表中添加数据列或为表添加索引，从而改变数据库的逻辑结构，此时SQL Server不自动执行优化，直到下一次重新启动后，再运行该存储过程。,10.4存储过程的重新编译,2023/10/11,主讲：王颂华,13,10.5系统存储过程与扩展存储过程,

9、在SQL Server中还有两类重要的存储过程:系统存储过程和扩展存储过程。这些存储过程为用户管理数据库、获取系统信息、查看系统对象提供了很大的帮助。下面简单介绍这两类存储过程。系统存储过程 SQL Server 2005提供了上百个系统存储过程，这些系统存储过程可以帮助用户很容易地管理SQL Server的数据库。10.5.2 扩展存储过程 扩展存储过程是允许用户使用某种编程语言创建的程序，程序中使用SQL Server开放数据服务的API函数，它们可以直接在SQL Server地址空间中运行。用户可以像使用普通的存储过程一样使用它们，也可以将参数传递给它们并返回结果和状态值。,2023/1

10、0/11,主讲：王颂华,14,1.创建一个加密存储过程2.创建带输入参数的存储过程3.创建带输出参数的存储过程4.创建添加、删除和修改记录存储过程,10.6 案例中的存储过程,2023/10/11,主讲：王颂华,15,触发器（trigger）是一种特殊类型的存储过程。与其他存储过程类似，它也由Transact-SQL语句组成，可以实现一定的功能；不同的是，触发器的执行不能通过名称调用来完成，而是当用户对数据库发生事件（如添加、删除、修改数据）时，将会自动触发与该事件相关的触发器，使其自动执行。触发器不允许带参数，它的定义与表紧密相连，触发器可以作为表的一部分。在SQL Server 2005中

11、，触发器可分为两大类：DML触发器和DDL触发器。,10.7 触发器概述,2023/10/11,主讲：王颂华,16,10.8 触发器的创建执行,10.8.1 Inserted表和Deleted表 向表中插入数据时，执行INSRET操作，系统将自动创建一个与触发器表结构相同的Inserted临时表，用来保存新插入的记录。删除表中的数据时，执行DELETE操作，系统将自动创建一个与触发器表结构相同的Deleted临时表，用来保存触发器中被删除的记录。方便用户查找当前删除的数据，修改表中的数据时，执行UPDATE操作，相当于删除一条旧记录，添加一条新记录。被删除的记录放在Deleted表中，添加的记

12、录放在Inserted表中。,2023/10/11,主讲：王颂华,17,创建触发器 创建触发器可以使用Transact-SQL语句，也可以使用SQL Server管理平台。在创建触发器前，必须注意以下几点：（1）CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用到一个表中。（2）触发器只能在当前的数据库中创建，但可以引用当前数据库的外部对象。（3）表的所有者具有创建触发器的默认权限，且不能将该权限转给其他用户。（4）不能在临时表或系统表上创建触发器，触发器可以引用临时表但不能引用系统表。（5）如果指定了触发器架构名称来限定触发器，则将以相同的方式限定表名称。（6）如果一个表

13、的外键包含对定义的DELETE/UPDATE操作的级联，则不能在该表上定义INSTEAD OF DELETE/UPDATE触发器。,10.8 触发器的创建执行,2023/10/11,主讲：王颂华,18,10.9.1 修改触发器 对于建立好的触发器，可以根据需要对其名称及文本进行修改。通常使用系统存储过程对其进行更名操作，使用SQL Server管理平台或Transact-SQL语句修改其文本。修改的内容包括：（1）修改触发器名称；（2）修改触发器定义；（3）修改触发器文本。10.9.2 禁止、启用和删除触发器 触发器的禁止、启用和删除操作可以使用Transact-SQL语句实现，也可以使用SQ

14、L Server管理平台实现。1.使用Transact-SQL语句实现（1）禁止触发器的语法格式：DISABLE TRIGGER 触发器名称 ON 表名 ALTER TABLE 表名 DISABLE TRIGGER 触发器名称,10.9 触发器的操作,2023/10/11,主讲：王颂华,19,10.9 触发器的操作,（2）启用触发器的格式：ENABLE TRIGGER 触发器名称 ON 表名 ALTER TABLE 表名 ENABLE TRIGGER 触发器名称（3）删除触发器的格式：DROP TRIGGER触发器名称,n2.使用SQL Server管理平台实现（1）启动Microsoft S

15、QL Server Management Studio，在“对象资源管理器”窗口中，依次展开“数据库”student“表”“触发器”。（2）在“触发器”中，右击需要修改的触发器，在弹出的快捷菜单上选择相应的命令。,2023/10/11,主讲：王颂华,20,在触发器中可以包含影响另外一个表的INSERT、UPDATE或DELETE语句，这就是嵌套触发器。具体说就是，如果A上的触发器在执行时引发了表B上的触发器，而表B上的触发器又激活了表C上的触发器，表C上的触发器又激活了表D上的触发器所有触发器依次触发。这些触发器不会形成无限循环，因为SQL Server规定触发器最多可嵌套至32层。如果允许使

16、用嵌套触发器，且链 中的一个触发器开始一个无限循环，如果超出嵌套级，触发器将被终止执行。正确地使用嵌套触发器，可以执行一些有用的操作，但嵌套触发器比较复杂，使用时要注意技巧。例如，由于触发器在事务中执行，如果在一系列嵌套触发器的任意层中发生错误，则整个事务都将被取消，且所有的数据修改都将回滚。一般情况下，在触发器中包含PRINT语句，用来确定错误发生的位置。,10.10 嵌套触发器,2023/10/11,主讲：王颂华,21,1.创建一个 INSERT 触发器2.创建一个DELETE触发器3.创建一个UPDATE触发器,10.11案例中的触发器,2023/10/11,主讲：王颂华,22,本章重点介绍了存储过程和触发器的概念、创建和执行。存储过程是对数据库中的任意表或者多个表进行复杂的数据处理而设计的，需要用户指定它去运行，它才会执行里面的SQL代码。而触发器是针对某个表进行添加、修改等操作作出的响应，是不需要用户干预而自动运行的。使用触发器是利用Transact-SQL语言进行程序设计的较高技术和技巧，需要熟练掌握。,本 章 小 结,