《2019年SQL server 2005案例教程 第6章 存储过程与触发器新.ppt》由会员分享，可在线阅读，更多相关《2019年SQL server 2005案例教程 第6章 存储过程与触发器新.ppt（41页珍藏版）》请在三一文库上搜索。
1、l l l 第 6 章存储过程与触发器 6.1 【案例 19】存储过程 相关知识 1存储过程的类型 （1）存储过程特点 存储过程是存储在 SQL Server 2005 服务器上、一种有效的封装重复性工作的方法，并具有支持用户声明的 变量、条件执行和其他强大的编程功能。与其他编程语言中的存储过程类似，SQL Server 2005 中的存储过程具 有如下特点。 接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。 包含用于在数据库中执行操作的编程语句。 向调用过程或批处理返回状态值，以指明成功或失败及失败的原因。 （2）用户自定义存储过程 用户自定义存储过程是由用户创建并能完成某一种特
2、定功能的存储过程。SQL Server 2005 可以使用的两种 自定义存储过程的类型为 Transact-SQL 和 CLR，具体说明如表 6-1-1 所示。 2019/5/231 （3）扩展存储过程 扩展存储过程是以在 SQL Server 2005 环境外执行的动态链接库（Dynamic-Link Libraries，DLL）来实现。 一般以 xp_为前缀标识。 （4）系统存储过程 在安装 SQL Server 2005 时，系统创建了很多系统存储过程，存储在 master 和 msdb 数据库中，并以 sp_为 前缀，系统存储过程主要是从系统表中获取信息，为系统管理员管理 SQL Se
3、rver 2005 提供支持。通过系统存储 过程，SQL Server 2005 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以顺利有效地 完成。 在 SQL Server 2005 中，许多管理活动和信息活动都可以使用系统存储过程来执行，系统存储过程的分类如 表 6-1-2 所示。 （5）临时存储过程 存储在 tempdb 数据库中，以#和#为前缀的过程，#表示本地临时存储过程，#表示全局临时存储过程。 （6）远程存储过程 是在远程服务器的数据库中创建和存储过程，可被各种服务器访问，向具有相应许可权限的用户提供服务。 2创建存储过程的规则 2019/5/232 在设计和创
4、建存储过程时，应该满足一定的约束和规则，只有满足了这些约束和规则才能创建有效的存储过 程。设计存储过程应遵守以下规则。 所有数据库对象（除存储过程）均可在存储过程中创建，只要该对象被创建就可被引用。 可以在存储过程内引用临时表。 如果在存储过程内创建了本地临时表，则该临时表仅为该存储过程存在，退出该存储过程后，临时表将 消失。 如果执行的存储过程中调用另一个存储过程，则被调用的存储过程可以访问由一个存储过程创建的所有 对象，包括临时表在内。 远程存储过程不参与事务处理，如果执行对远程 SQL Server 2005 实例进行更改的远程存储过程，不能 回滚这些更改。 存储过程中的参数的最大数目为
5、 2100。 存储过程中的局部变量的最大数目仅受可用内存的权限。 根据可用内存的不同，存储过程最大为 128MB。 使用 CREATE PROCEDURE 定义存储过程，可以包括任意数量和类型的 SQL 语句，但不能在存储过程中使用 以下语句，如表 6-1-3 所示。 2019/5/233 3使用 Transact-SQL 语句创建存储过程 在 SQL Server 2005 系统中，可以使用 Transact-SQL 语句中的 CREATE PROCEDURE 创建存储过程。具体操作 步骤如下：首先编写并测试一个要在存储过程内执行的 SQL 查询语句。然后使用 CREATE PROCEDUR
6、E 语句创建， 可以使用 ALTER PROCEDURE 语句修改。 存储过程的定义包括两个主要内容：过程名和参数的说明以及过程体，即包含执行存储过程操作的 Transact-SQL 语句在创建存储过程时，应该指定所有的输入参数、执行数据库操作的编程语句、返回至调用过 程或批处理时以示成功或失败的状态值、捕获和处理潜在错误时的错误处理语句等。 （1）语法 使用 CREATE PROCEDURE 语句创建存储过程的语法如下所示。 CREATE PROCEDURE 存储过程名 ; number 参数 1 数据类型VARYING= 默认值OUTPUT, 参数 n 数据类型= 默认值OUTPUT AS
7、 SQL 语句 (n) 2019/5/234 l l l l l l （2）主要参数说明 过程名称在架构中必须唯一，可在存储过程名前面使用一个数字符合（#）来创建局部临时过程，使用两个 数字符号（#）来创建全局临时过程。对于 CLR 存储过程，不能指定临时名称。 ;number为可选整数，用来对同名的过程分组，使用一个 DRIP PROCEDURE 语句可将这些分组过程一起删 除。如果名称中包含分隔标识符，则数字不应包含在标识符中，只应在存储过程名前后使用适当的分隔符。 参数：是指过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。除非定义了参数的默 认值或者将参
8、数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值，如果指定 了 FOR REPLICATION，则无法声明参数。 数据类型：是指参数的数据类型，存储过程中可以使用所有数据类型。如果指定的数据类型为 crusor，只 能用于 OUTPUT 参数，而且同时指定 VARYING 和 OUTPUT 关键字。对于 CLR 存储过程，不能指定 char、varchar、 text、ntext、image、cursor 和 table 数据类型作为参数。 VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。 默认值：是指参数的默认值。如果
9、定义了该值，则不需要指定此参数的值即可执行过程。默认值必须是常 量或 NULL。如果过程使用带 like 关键字的参数，则可包含%、_、和通配符。 2019/5/235 l l OUTPUT：是指该参数是输出参数。此选项的值可以返回给调用 EXECUTE 的语句。使用 OUTPUT 参数将值返回 该过程的调用方。 SQL 语句：是指包含在过程中的一个或多个 Transact-SQL 语句。 4使用 Transact-SQL 语句执行存储过程 可以使用 Transact-SQL 语句中的 EXECUTE 语句执行存储过程，如果存储过程是批处理中的第一条语句，那 么不使用 EXECUTE 的关键字
10、也可以执行该存储过程，EXECUTE 的语法格式如下。 EXECUTE 存储过程名 参数值列表 5使用 Transact-SQL 语句修改、查看、删除存储过程 （1）修改存储过程 使用 ALTER PROCEDURE 语句可以更改先前通过 CREATEPROCEDURE 语句创建的过程，语句格式如下所示。 ALTER PROCEDURE 存储过程名 ; number 参数 1 数据类型VARYING= 默认值OUTPUT, 参数 n 数据类型= 默认值OUTPUT 2019/5/236 AS SQL 语句 (n) （2）查看存储过程 （3）删除存储过程 使用 DROP PROCEDURE 可以
11、删除存储过程，其基本语句格式如下所示。 DROP PROCEDURE 存储过程名 6存储过程中的输入/输出参数 （1）参数的含义 参数是指在存储过程以及应用程序之间交换数据的值，SQL Server 2005 中存储过程可以使用两种类型的参 数：输入参数和输出参数。输入参数允许用户将数据值传递到存储过程或函数；输出参数允许存储过程将数据值 或游标变量传递给用户。每个存储过程可以向用户返回一个整数代码，如果存储过程没有显示设置返回代码的值， 则返回代码为 0。 参数在创建存储过程时，在 CREATE PROCEDURE 和 AS 关键字之间定义，并且要为其指定参数名和数据类型， 参数名必须以符号
12、为前缀，可以为参数指定默认值；如果是输出参数，则应用 OUTPUT 关键字描述。各个参数定 义之间用逗号隔开，具体语法如下所示。 参数名 数据类型=默认值OUTPUT 2019/5/237 l l （2）输入参数 输入参数，相当于存储过程中的一个条件，在执行存储过程时，可以为这个条件指定值，通过存储过程返回 相应的信息。使用输入参数可以向同一存储过程多次查找数据库。 执行带有输入参数的存储过程时，SQL Server 2005 提供了两种传递参数的方式。 直接按值传递：在执行存储过程的语句中，直接给出参数的值。当有多个参数时，给出参数的顺序与创建 存储过程的语句中的参数顺序一致，即参数传递的顺
13、序就是参数定义的顺序。使用这种方式执行存储过程 的代码如下所述。 EXEC 存储过程名 参数值 通过参数名传递：在执行存储过程的语句中，使用“参数名=参数值”的形式给出参数值。使用这种方式，参 数可按任意顺序给出。使用这种方式执行存储过程的代码如下所述。 EXEC 存储过程参数名=参数值 （3）使用默认参数值 执行存储过程时，如果没有指定参数，系统运行就会出错，如果希望不给出参数时也能正确运行，可以通过 设置参数的默认值来实现。 （4）输出参数 2019/5/238 通过定义输出参数，可从存储过程中返回一个或多个值。如果使用输出参数，必须在 CREATE PROCEDURE 语 句和 EXEC
14、UTE 语句中指定关键字 OUTPUT；如果忽略了 OUTPUT 关键字，存储过程仍然会执行但没有返回值。 （5）存储过程的返回值 存储过程在执行后都会返回一个整型值。如果执行成功，返回 0；否则返回-1-99 之间的随机数，也可以 使用 RETURN 语句来指定一个存储过程的返回值。 6.2 【案例 20】触发器 触发器（Trigger）是一种特殊类型的存储过程，与表紧密结合，只要对它所保护的数据进行修改，它就会 自动触发，包括对表进行 INSERT、UPDATE 和 DELETE 操作，通过实现复杂的业务规则，更有效地实施数据完整性。 相关知识 1触发器概述 （1）触发器的概念 触发器是一
15、个在修改指定表中的数据时特殊的存储过程。通过创建触发器可以强制实现不同表中的逻辑相关 数据的引用完整性或一致性，确保数据的完整性。在触发器中可以查询其他表，也可以执行更复杂的 T-SQL 语句。 触发器和引起触发器执行的 T-SQL 语句被当做一次事务处理，因此可以在触发器中回滚这个事务。如果发现引起 触发器执行的 T-SQL 语句执行了一个非法操作，则可以通过回滚事务使语句不能执行，回滚后 SQL Server 会自 2019/5/239 l l l l 动返回到此事务执行前的状态。 （2）触发器的作用 强化约束（Enforce Restriction）：触发器能实现由主键和外键所不能保证的
16、复杂的参照完整性和数据的 一致性，能够实现比 CHECK 约束更为复杂的限制。与 CHECK 约束不同，在触发器中可以引用其他表。 跟踪变化（Auditing Changes）：触发器可以侦测数据库内的操作，不允许数据库中未经许可指定的更新 和变化。 级联运行（Cascaded Operation）：触发器可以侦测数据库内的操作，并自动级联影响整个数据库的各项 内容。例如，某个表上的触发器中包含对另外一个表的数据操作（如删除、更新、插入），而该操作又导 致该表触发器被触发。 存储过程的调用（Stored Procedure invocation）：触发器可以调用一个或多个存储过程，更新相应数据
17、 库，还可以通过外部过程的调用在 DBMS（数据库管理系统）本身之外进行操作。 2DML 触发器 在 SQL Server 2005 系统中，按照触发事件的不同，可以把提供的触发器分成两大类型，即 DML 触发器和 DDL 触发器。 DML 事件包括对表或视图发出的 UPDATE、INSERT 或 DELETE 语句。DML 触发器是当数据库服务器中发生数据 2019/5/2310 l l l 操作语言（DML）事件时要执行的操作，用于在数据被修改时强制执行业务规则和扩展 SQL Server 2005 的约束、 默认值和规则的完整性检查逻辑。DML 触发器的主要作用是执行管理操作。 可以使用
18、 Transact-SQL 语句创建 DML 触发器，语法格式如下所示。 CREATETRIGGER 触发器名 ON table view FOR AFTER INSTEAD OF DELETE, INSERT ,UPDATE AS SQL 语句 上述 CREATE TRIGGER 的语法中，各主要参数含义如下。 触发器名：表示要创建的触发器的名称。 table view：表示在该触发器执行的表或视图，称为触发器表或触发器视图。可以选择是否指定表或视 图的所有者名称。 FOR、AFTER、INSTEAD OF：表示指定触发器触发的时机。 2019/5/2311 l l DELETE、INSER
19、T、UPDATE：指定在表或视图上执行哪些数据修改语句时将触发触发器的关键字，必须至少 指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字，如果指定的选项多于一个，需用 逗号分隔这些选项。 SQL 语句：表示指定触发器所执行的 Transact-SQL 语句。 3DDL 触发器 DDL 触发器是 SQL Server 2005 系统新增的功能，以前的版本只有 DML 触发器。DDL 触发器是一种特殊的触 发器，当服务器或数据库中发生数据定义语言（DDL）事件时将调用这些触发器，其可用于在数据库中执行管理 任务。 DDL 触发器与 DML 触发器都可以自动触发完成相应的操作，可以使用
20、 CREATE TRIGGER 语句创建。但是 DDL 触发器的触发事件主要是 CREATE、ALTER、DROP 以及 GRANT、DENY、REVOKE 等语句，并且触发的时间条件只有 AFTER，没有 INSTEAD OF 触发器。 创建 DDL 触发器的 CREATE TRIGGER 语句的基本语法形式如下所示。 CREATETRIGGER 触发器名 ON ALL SERVER DATABASE WITH ENCRYPTION 2019/5/2312 l l l FOR AFTER event_type AS Sql_statement 下面对上述语法中的各参数进行说明。 ALL SE
21、RVER：表示 DDL 触发器的作用域是整个服务器。 DATABASE：表示 DDL 触发器的作用域是整个数据库。 event_type：用于指定触发 DDL 触发器的事件。 4DELETED 表和 INSERTED 表 SQL Server 2005 为每个触发器语句都创建了两种特殊的表，由系统亲自创建和维护，这是两个逻辑表，分 别是 DELETED 表和 INSERTED 表。它们存放在内存而不是数据库中，用户不能对它们进行修改，触发器执行完成 后，与该触发器相关的这两个表也会被删除。这两个表的结构与被该触发器作用的表的结构相同。 DELETED 表存放的是由执行 DELETE 或 UPD
22、ATE 语句而要从表中删除的所有行。在执行 DELETE 或 UPDATE 操作 时，被删除的行从触发器的表中移动到 DELETE 表中，这两个表不会有共同的行。 INSERTED 表存放的是由执行 INSERT 或 UPDATE 语句而要向表中插入的所有行。在执行 INSERT 或 UPDATE 操 作时，新的行同时添加到触发器的表和 INSERT 表中，INSERT 表的内容是触发器的表中行的副本。 2019/5/2313 l l l 5DELETE 触发器 SQL Server 2005 系统按照触发器事件类型的不同，提供了 3 种不同类型的 DML 触发器，即 INSERT 类型、 D
23、ELETE 类型和 UPDATE 类型。INSERT 类型触发器已经在【案例 20】中介绍了，下面介绍另外两种触发器。 DELETE 触发器用于约束用户从数据库中删除数据，当针对目标数据库运行 DELETE 语句时，就能激活 DELETE 触发器，避免用户误删数据。DELETE 触发器通常用于防止那些确实要删除，但是可能会引起数据一致性问题的 情况，还可以用于级联删除操作的情况。 通常情况下，当用户执行 DELETE 语句时，SQL Server 2005 会从该表中删除这条记录，而且该记录不再存 在。这种行为在给表添加了 DELETE 触发器之后会有所不同。因为当激活 DELETE 触发器时
24、，从受影响的表中删除 的行将被放置到一个特殊的 DELETED 表中。 DELETED 表与 INSERTED 表相同。是一个临时表，保留已被删除数据 行的一个副本。DELETED 表还允许引用由初始化 DELETE 语句产生的日志数据。 使用 DELETE 触发器时，需要注意以下原则： 当某行记录被添加到 DELETED 表中时，就会从原数据库表消失，所以，DELETED 表和数据库表没有相同的行。 创建 DELETED 表时，空间是从内存中分配的。DELETED 表被存储在高速缓存中。 由于日志不记录 TRUNCATE TABLE 语句，所以为 DELETE 动作定义的触发器并不执行 TR
25、UNCATE TABLE 语句。 6UPDATE 触发器 2019/5/2314 UPDATE 触发器专门用于约束用户能修改的现有数据，当一个 UPDATE 语句在目标表上运行时，即可调用 UPDATE 触发器阻止修改或者输出警告消息，以引起操作人员的注意。 UPDATE 语句可以被看做两步操作：捕获数据前像的 UPDATE 语句和捕获数据后像的 INSERT 语句。当在定义 有触发器的表上执行 UPDATE 语句时，原始行（前像）被移入到 DELETED 表中，更新行（后像）被移入到 INSERTED 表中。触发器检查 DELETED 表和 INSERTED 表以及被更新的表来确定是否更新了
26、多行以及如何执行触发器动作。 7使用 SSMS 管理触发器 在“对象资源管理器”窗格中，依次展开服务器“数据库”结点“选课管理”数据库。 展开数据库中具有触发器的数据表，如“课程信息”，然后展开“触发器”结点。 右击“trig_删除课程信息”触发器，将弹出快捷菜单，如图 6-2-11 所示。 根据自己的操作需要来选择相应的命令。图 6-2-12 所示就是用户选择“修改”命令后打开的窗口。 2019/5/2315 图 6-2-11触发器快捷菜单 2019/5/2316 图 6-2-12修改“trig_删除课程信息”触发器效果 8使用 Transact-SQL 语句管理触发器 2019/5/231
27、7 （1）修改触发器 可以使用 ALTER TRIGGER 语句修改触发器的定义，语法格式如下所述。 ALTER TRIGGER trigger_name ON table view FOR AFTER INSTEAD OF DELETE, INSERT ,UPDATE AS SQL 语句 修改触发器语句 ALTER TRIGGER 中各参数的含义与创建触发器 CREATE TRIGGER 时相同，这里不再说明。 （2）禁用触发器 用户可以禁用、启用一个指定的触发器或一个表的所有的触发器。当禁用一个触发器后，它在表上的定义仍 然存在。但是，当对表执行 INSERT、UPDATE 或 DELET
28、E 语句时，并不执行触发器的动作，直到重新启动触发器为 止。 在修改表的 ALTER TABLE 语句中使用 DISABLE TRIGGER 子句，可以使该表上的某一触发器无效。当需要恢复 使用时，可以使用 ALTER TABLE 语句的 ENABLE TRIGGER 子句使触发器重新有效。 2019/5/2318 9使用系统存储过程查看触发器 触发器是特殊的存储过程，因此所有适用于存储过程的管理方式都适用于触发器。 （1）查看触发器 格式：sp_help 触发器名称 （2）显示触发器的文本 格式：sp_helptext 触发器名称 （3）显示有关表中各种依赖关系 格式：sp_depends表
29、名 （4）显示一个触发器所引用的表 格式：sp_depends 触发器名 【案例 20-6】使用系统存储过程查看“trig_删除课程信息”触发器的内容和文本内容，执行如下语句，效 果如图 6-2-14 所示。 sp_help GO trig_删除课程信息 sp_helptexttrig_删除课程信息 2019/5/2319 在线教务辅导网：http:/ 更多课程配套课件资源请访问在线教务辅导网 2019/5/2320 Date21 Date22 Date23 Date24 Date25 馋 死 Date26 Date27 Date28 Date29 Date30 Date31 Date32 Date33 Date34 Date35 Date36 Date37 Date38 PPT研究院 POWERPOINT ACADEMY Date39 Date40 Date41