存储过程和触发器.ppt
数据库技术及应用,第10章 存储过程和触发器,主要内容,一、存储过程概述二、创建存储过程三、执行存储过程四、查看、修改和删除存储过程五、触发器概述六、创建触发器七、激活触发器八、查看、修改和删除触发器,一、存储过程概述,存储过程是什么？是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中两类存储过程：系统提供的存储过程和用户自定义的存储过程系统存储过程主要存储在master数据库中并以sp_为前缀可以接受参数、输出参数、返回单个或多个结果集以及返回值,为什么要使用存储过程实现了模块化编程能够实现较快的执行速度能够减少网络流量可以提高数据库的安全性,二、创建存储过程,使用向导创建存储过程使用企业管理器创建存储过程使用Transact-SQL命令创建存储过程,使用Transact-SQL命令创建存储过程使用CREATE PROCEDURE命令创建存储过程在一个批处理中，CREATE PROCEDURE语句不能与其他SQL语句合并在一起只能在当前数据库中创建属于当前数据库的存储过程,创建存储过程时，需要确定存储过程的三个组成部分所有的输入参数以及传给调用者的输出参数执行数据库操作（包括调用其它过程）的编程语句返回给调用者的状态值，以指明调用是成功还是失败,语法格式CREATE PROC EDURE procedure_name;number parameter data_type VARYING=default OUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement.n,参数说明OUTPUT：表明该参数是一个返回参数。使用 OUTPUT 参数可将信息返回给调用过程ENCRYPTION：表示 SQL Server 加密了 syscomments 表，该表的text字段是包含 CREATE PROCEDURE 语句的存储过程文本。使用该关键字无法通过查看 syscomments 表来查看存储过程内容sql_statement：是存储过程中要包含的任意数目和类型的 Transact-SQL语句存储过程创建后，其名称存储在 sysobject表中，文本存储在syscomments表中,举例不带参数的存储过程1001_ProcWithNoParam.SQL带输入参数的存储过程1002_ProcInputParam.SQL带输入输出参数的存储过程1003_ProcInputOutputParam.SQL,三、执行存储过程,直接执行存储过程可以使用EXECUTE命令来执行 EXEC UTE return_status=procedure_name;number|procedure_name_var parameter=value|variable OUTPUT|DEFAULT,.n WITH RECOMPILE,如果存储过程是批处理中的第一条语句，那么不使用 EXECUTE 关键字也可以执行该存储过程如果使用 parameter=value 的形式，那么可以按任何顺序提供参数。还可以省略那些已提供默认值的参数如果以 parameter=value 形式提供了一个参数，就必须按此种形式提供后面所有的参数如果没有以 parameter=value 形式提供参数，则必须按照 CREATE PROCEDURE 语句中给出的顺序提供参数,return_status是一个可选的整型变量，保存存储过程的返回状态。这个变量在用于 EXECUTE 语句前，必须在批处理、存储过程或函数中声明过SQL Server 目前使用返回值 0 到-14 来表示存储过程的执行状态。除非特别指明，所有系统存储过程返回 0 值表示成功，返回非零值则表示失败,如果使用 OUTPUT 参数，目的是在调用批处理或过程的其它语句中使用其返回值，则参数值必须作为变量传递（即 parameter=variable）。在执行过程之前，必须声明变量的数据类型并赋值,举例在SQL Server中执行存储过程在Delphi中调用存储过程=1004_ExecProcWithDelphi,四、查看、修改和删除存储过程,查看存储过程存储过程被创建以后，它的名字存储在系统表sysobjects中，它的源代码存放在系统表syscomments中使用 sp_helptext查看存储过程的源代码sp_helptext usp_DeleteAStudent修改存储过程使用ALTER PROCEDURE语句修改存储过程,删除存储过程使用DROP PROCEDURE命令删除存储过程,五、触发器概述,触发器是什么？是一种特殊类型的存储过程，但它不等同于存储过程触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server就会自动执行触发器所定义的SQL语句,为什么要使用触发器？能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性实现比CHECK语句更复杂的约束可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题,触发器的种类AFTER触发器只有执行某一操作(INSERT、UPDATE、DELETE)之后，触发器才被触发且只能在表上定义可以为表的同一操作定义多个AFTER触发器INSTEAD OF 触发器表示并不执行其所定义的操作(INSERT、UPDATE、DELETE)，仅是执行触发器本身既可在表上，也可以在视图上定义INSTEAD OF触发器,触发器的两个特殊的表每个触发器有两个特殊的表：插入表(INSERTED)和删除表(DELETED)这两个表是逻辑表，是由系统管理的，存储在内存中，不是存储在数据库中，这两个表是只读的这两个表的结构总是与被该触发器作用的表有相同的表结构这两个表是动态驻留在内存中的，当触发器工作完成，这两个表也被删除这两个表主要保存因用户操作而被影响到的原数据值或新数据值,插入表INSERTED的功能对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入操作，那么对向该表插入的所有行来说，都有一个相应的副本存放到插入表中即插入表就是用来存储向原表插入的内容,删除表DELETED的功能对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除操作，则将所有的删除行存放至删除表中即删除表就是用来存储从原表删除的内容,注意：更新操作包括两个部分，即先将更新的内容去掉，然后将新值插入因此，对一个定义了更新类型触发器的表来讲，当进行更新操作时，在删除表DELETED中存放了旧值，然后在插入表INSERTED中存放新值,Transact-SQL语句 Inserted表 Delete表 INSERT 所要添加的行 空 DELETE 空 删除的行 UPDATE 新的行 旧的行,六、创建触发器,语法格式 CREATE TRIGGER trigger_name ON table|view WITH ENCRYPTION FOR|AFTER|INSTEAD OF DELETE,INSERT,UPDATE WITH APPEND NOT FOR REPLICATION ASsql_statement.n,CREATE TRIGGER 语句必须是批处理的第一个语句尽管TRUNCATE TABLE 语句如同没有 WHERE 从句的 DELETE 语句，但是由于 TRUNCATE TABLE 语句没有被记入日志，所以该语句不被触发 DELETE 型触发器如果仅指定 FOR 关键字，则 AFTER 是默认设置,两种方法创建触发器使用企业管理器创建触发器使用查询分析器创建触发器,当创建一个触发器时，四个部分触发器的名称在其上定义触发器的表触发器将何时激发激活触发器的数据修改语句。如INSERT、DELETE、UPDATE,七、激活触发器,触发器不是通过名字来执行的，而是在相应的SQL语句被执行时自动触发的=1005_trDelOnStudent.SQL=1006_trInsertedDeleted.SQL=1007_TriggerInsertSC.SQL,八、查看、修改和删除触发器,查看触发器使用 sp_helptext查看触发器的源代码 修改触发器使用alter trigger命令修改触发器正文删除触发器使用命令DROP TRIGGER删除触发器所在的表时，SQL Server将会自动删除与该表相关的触发器企业管理器中删除触发器,
