存储过程和触发器.ppt

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