存储过程触发器函数

第1页第8章存储过程、触发器和函数本章概述本章要点本章内容第2页本章概述如何提高Transact-SQL语句的执行效率呢？如何加强数据库中数据完整性的机制呢？这些问题的解决都依赖于数据库的编程对象。典型的数据库编程对象包括视图、存储过程、触发器、函数等。存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。触发器是一种特殊类型的存储过程，可以实现自动化的操作。用户定义函数是由用户根据应用程序的需要而定义的可以完成特定操作的函数。本章将全面介绍存储过程、触发器、用户定义函数等特点和使用方式。第3页本章要点存储过程的特点、类型和作用使用CREATEPROCEDURE语句创建存储过程存储过程的执行方式DML触发器的特点和创建方式DML触发器的工作原理使用CREATETRIGGER语句创建DML触发器DDL触发器的特点和创建方式用户定义函数的类型和特点使用CREATEFUNCTION语句创建用户定义函数第4页本章内容8.1存储过程8.2触发器8.3用户定义函数8.4本章小结8.1存储过程存储过程可以提高应用程序的设计效率和增强系统的安全性。本节将全面介绍存储过程的特点、类型、创建、执行等内容。第5页存储过程的特点和类型存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。在MicrosoftSQLServer2008系统中，可以使用Transact-SQL语言编写存储过程，也可以使用CLR方式编写存储过程。使用CLR编写存储过程是MicrosoftSQLServer2008系统与.NET框架紧密集成的一种表现形式。第6页类型在MicrosoftSQLServer2008系统中，提供了3种基本的存储过程类型用户定义的存储过程扩展存储过程系统存储过程。第7页使用CREATEPROCEDURE语句在MicrosoftSQLServer2008系统中，可以使用CREATEPROCEDURE语句创建存储过程。需要强调的是，必须具有CREATEPROCEDURE权限才能创建存储过程，存储过程是架构作用域中的对象，只能在本地数据库中创建存储过程。在创建存储过程时，应该指定所有的输入参数、执行数据库操作的编程语句、返回至调用过程或批处理表明成功或失败的状态值、捕捉和处理潜在错误的错误处理语句。第8页创建存储过程创建存储过程，需确定存储过程的三个组成部分:1．所有的输入参数以及传给调用者的输出参数2．被执行的针对数据库的操作语句包括调用其它存储过程的语句3．返回给调用者的状态值以指明调用是成功还是失败

在创建存储过程之前应该考虑到以下几个方面：1．在一个批处理中CreateProcedure语句不能与其它SQL语句合并在一起。2．数据库所有者具有默认的创建存储过程的权限它可把该权限传递给其它的用户。3．存储过程作为数据库对象其命名必须符合命名规则。4．只能在当前数据库中创建属于当前数据库的存储过程

创建存储过程创建存储过程的语法规则CREATEPROCEDURE存储过程名

@参数名类型[=default][output][,…n][with{recompile|encryption|recompile,encryption}]ASsql_statementRecompile:指定每次执行时都要重新编译Encryption:指定对存储过程的正文进行加密，防止别的用户看到编写的存储过程脚本。定义无参数的存储过程例1：在study数据库中，创建一个名称为myproc1的存储过程，该存储过程的功能是从数据表studnet中查询所有男同学的信息。GOCREATEPROCEDUREmyproc1ASSELECT*FROMsWHEREsex='男'GO定义具有参数的存储过程。例2：创建一个名称为InsertRecord的存储过程，功能是向数据表student中插入一条记录，新值由参数提供。USEstudyGOCREATEPROCEDUREInsertRecord@snochar(6),@snchar(20),@agenumeric(5),@sexchar(2),@deptchar(10)ASINSERTINTOsVALUES(@sno,@sn,@sex,@age,@dept)GO

定义具有参数默认值的存储过程。例3：创建一个名称为InsertRecordDefa的存储过程，其功能是向student表中插入一条记录，新值由参数提供，若未提供系别dept值时，由参数的默认值代替。USEstudyGOCREATEPROCEDUREInsertRecordDefa@snochar(6),@snchar(20),@agenumeric(5),@sexchar(2),@deptchar(10)='无'ASINSERTINTOstudentVALUES(@sno,@sn,@sex,@age,@dept)定义能够返回值的存储过程。例4：创建一个名称为Query_Study的存储过程，其功能是从student表中根据学号查询某一同学的姓名和系别。CREATEPROCEDUREQuery_Study@snochar(6),@snchar(20)OUTPUT,@deptchar(10)OUTPUTASSELECT@sn=sn,@dept=deptFROMstudentWHEREsno=@sno其语法规则为：DROPPROCEDURE{procedure}}[,…n]如：将存储过程mynewproc从数据库中删除。

dropproceduremynewproc删除存储过程使用EXECUTE命令(可以简写为EXEC)1.执行存储过程myproc(无参调用)

EXECmyproc2.执行存储过程InsertRecord(带参调用)EXECInsertRecord‘S1’,‘王大利’,‘男’,18,‘计算机系’或者：把值放入变量中，使用变量间接的提供参数值执行存储过程3.执行存储过程InsertRecordDefa

(含默认值调用)

EXECInsertRecordDefa'S10','高平','女',184.执行存储过程Query_Study(含有输出参数)

DECLARE@snchar(20)DECLARE@deptchar(10)EXECQuery_Study'S10',@snOUTPUT,@deptOUTPUTPrint@sn--或者select@snprint@dept执行存储过程CREATEPROCEDUREdisRecordASdeclare@snochar(6),@snchar(20),@sexchar(2),@agesmallintdeclarespcursorforselects_no,s_name,s_sex,year(getdate())-year(s_birthday)fromstudentwheres_sex='女'openspfetchspinto@sno,@sn,@sex,@agewhile@@fetch_status=0beginprint@sno+@sn+@sex+convert(char,@age)fetchspinto@sno,@sn,@sexendclosespdeallocatesp存储过程的执行过程存储过程创建之后，在第一次执行时需要经过语法分析阶段、解析阶段、编译阶段和执行阶段。第20页查看存储过程的信息在MicrosoftSQLServer2008系统中，可以使用系统存储过程和目录视图查看有关存储过程的信息。第21页8.2触发器MicrosoftSQLServer2008系统提供了两种强制业务逻辑和数据完整性的机制，即约束技术和触发器技术。第22页触发器的概念和类型触发器是一种特殊类型的存储过程，它包括了大量的Transact-SQL语句。但是触发器又与存储过程不同，存储过程可以由用户直接调用执行，而触发器不能被直接调用执行，它只能自动执行。

按照触发事件的不同，可以把MicrosoftSQLServer2008系统提供的触发器分成两种类型，即DML触发器和DDL触发器。第23页DML触发器当数据库中发生数据操纵语言(DML)事件时将调用DML触发器。DML事件包括在指定表或视图中修改数据的INSERT、UPDATE、DELETE语句。在DML触发器中，可以执行查询其他表的操作，也可以包含更加复杂的Transact-SQL语句。在执行触发器操作过程中，如果检测到错误发生，则整个触发事件语句和触发器操作的事务自动回滚。第24页DML触发器的类型按照触发器事件类型的不同，可以把MSSQLServer2008系统提供的DML触发器分成3种类型，即INSERT类型UPDATE类型

DELETE类型第25页创建DML触发器CREATETRIGGERtrigger_nameONtable_name|view_nameWITHENCRYPTION{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASsql_statementAFTER指定在对数据表的相关操作之后，触发器被触发。若指定FOR关键字，则默认是AFTER设置。INSTEADOF指定执行触发器而不是执行语句第26页

例：创建一个触发器，当向表student中插入一条记录时，自动显示表中的记录。CREATETRIGGERChange_DisplayONstudnetFORINSERT,UPDATE,DELETEASSELECT*FROMstudent该触发器建立完毕后，当执行如下操作时将会显示数据表s中的全部记录。EXECInsertRecordDefa@sno='S11',@sn='张建峰',@age=17,@sex='男'第28页使用sp_helptext系统存储过程使用sp_helptext系统存储过程查看定义的存储过程信息execsp_helptext‘存储过程名’DML触发器的工作原理

①向表中插入数据时，INSERT触发器触发执行。

②当INSERT触发器触发时，新的记录增加到触发器表中和inserted表中。

③

inserted表是一个逻辑表，保存了所插入记录的备份，允许用户参考INSERT语句中数据。触发器可以检查inserted表，来确定该触发器的操作是否应该执行和如何执行。④在inserted表中的记录，总是触发器表中一行或多行记录的冗余。第29页DELETE触发器当触发一个DELETE触发器时，被删除的记录放在一个特殊的deleted表中。deleted表是一个逻辑表，用来保存已经从表中删除的记录。该deleted表允许参考原来的DELETE语句删除的已经记录在日志中的数据。第30页UPDATE触发器修改一条记录就等于插入一条新记录同时删除一条旧记录。UPDATE语句也可以看成是由删除一条记录的DELETE语句和增加一条记录的INSERT语句组成。当在某一个有UPDATE触发器表的上面修改一条记录时，表中原来的记录移动到deleted表中，修改过的记录插入到了inserted表中。触发器可以检查deleted表和inserted表以及被修改的表，以便确定是否修改了多个行和应该如何执行触发器的操作。第31页DDL触发器DDL触发器触发事件主要是CREATE、ALTER、DROP以及GRANT、DENY、REVOKE等语句，并且触发的时间条件只有AFTER，没有INSTEADOF。CREATETRIGGERCREATETRIGGERtrigger_nameON{ALLSERVER|DATABASE}WITHENCRYPTION{FOR|AFTER}{event_type}ASsql_statement定义一个DDL触发器删除表的操作失败8.3用户定义函数用户定义函数可以使用Transact-SQL语言编写，也可以使用.NET编程语言来编写。每次使用用户定义函数时均无需重新解析和重新优化，从而大大缩短了执行时间。减少网络流量，基于某种无法用单一标量表达式表示的复杂约束来过滤数据的操作，可以表示为函数。然后，该函数可以在WHERE子句中调用，以减少发送至客户端的数字或行数。结构所有的用户