项目4存储过程与触发器管理.pptVIP

项目4：存储过程与触发器管理任务4-1：存储过程的创建 任务4-2：存储过程的执行与管理任务4-3：触发器的创建与使用 任务4-1：存储过程的创建 1、概述 1）存储过程的基本概念 SQL Server的存储过程类似于编程语言中的过程。在使用Transact-SQL语言编程的过程中，可以将某些多次调用以实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名调用它们，这些过程就叫做存储过程。 存储过程可以实现： 接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。 包含用于在数据库中执行操作（包括调用其他过程）的编程语句。 向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。 2）存储过程的分类 系统存储过程：由SQL Server 2005提供，用户可以直接使用。SQL Server 2005 中的许多管理活动都是通过一些系统存储过程完成的。 用户定义的存储过程：用户自定义存储过程用于实现用户自己所需要实现的操作。 3）存储过程的优点 实现模块化程序设计。 存储过程内可引用其他存储过程，可以简化一系列复杂语句。 可以减少网络通信流量。 存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。 可以作为一种安全机制。 2、创建存储过程 1）使用SQL Server Management Studio创建存储过程 教材P157。 2）使用T-SQL语句创建存储过程 CREATE PROC [ EDURE ] procedure_name [ { @parameter data_type } [ = default ] [ OUTPUT ]] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] AS sql_statement 说明：教材P157~P158。 任务4-2：存储过程的执行与管理 1、执行存储过程 存储过程可以通过EXECUTE语句来执行，其语法格式如下： [ [ EXEC [ UTE ] ] { [ @return_status = ] { procedure_name] | @procedure_name_var } [ [ @parameter = ] { value | [ OUTPUT ] | [ DEFAULT ] ] [ ,...n ] [ WITH RECOMPILE ] 说明：教材P159~P160。 2、管理存储过程 1）查看存储过程 创建存储过程之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用系统存储过程sp_help、sp_helptext、sp_depends来查看用户自定义存储过程。 课堂练习 （1）创建一个存储过程【proc_1】，其功能是用来查找所有女销售员的清单。然后执行该存储过程。 （2）创建一个带有输入参数的存储过程【proc_2】，其功能是通过一个给定的销售员编号，显示出该销售员的详细信息。然后执行该存储过程。 （3）创建一个带有输入参数和输出参数的存储过程【proc_3】，其功能是通过一个给定的产品编号，查询出该产品的价格和库存量，并通过输出参数进行返回。然后执行该存储过程。 任务4-3：触发器的创建和使用 1、概述 触发器（trigger）是一种特殊的存储过程，它不同于一般的存储过程。一般的存储过程通过过程名被直接调用，而触发器主要是通过事件进行触发而被执行。 触发器是一个功能强大的工具，与表紧密连接，可以看作是表格定义的一部分。当用户修改（INSERT、UPDATE或DELETE）指定表或视图中的数据时，该表中的相应的触发器就会自动执行。 触发器基于一个表创建，但可以操作多个表。触发器常用来实现复杂的商业规则。但是，不管触发器所进行的操作多复杂，触发器都只作为一个独立的单元被执行，被看作一个事务。如果在执行触发器的过程中发生了错误，则整个事务将会自动回滚。 2、触发器的分类 DML 触发器 AFTER 触发器 INSTEAD OF 触发器 CLR 触发器 DDL 触发器 3、创建触发器 1）使用SQL Server Management Studio创建触发器 教材P166。 2）使用T-SQL语句创建触发器 ① 创建DML触发器 创建触发器时需指定： 触发器名称。 在其上定义触发器的表或视图。 触发器将何时激发。 激活触发器的数据修改语句。有效选项为 INSERT、UPDATE 或 DELETE。多个数据修改语句可激活同一个触发器。例如，触发器可由 INSERT 或