存储过程与触发器

存储过程与触发器存储过程概述存储过程的使用和管理触发器概述触发器的使用和管理使用触发器和存储过程维护数据完整性本讲内容存储过程概述问题要把完成某功能的SQL做成类似C语言的函数，供需要时调用，如何做？什么是存储过程？是一组被编译在一起的T-SQL语句的集合，它们被集合在一起以完成一个特定的任务。存储过程的分类系统存储过程扩展存储过程（提供从SQLServer到外部程序的接口，以便进行各种维护活动）用户自定义的存储过程使用存储过程的作用1.模块化编程创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。2.执行速度快存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。3.减少网络通信量有了存储过程后，在网络上只要一条语句就能执行一个存储过程。4.保证系统安全性通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。存储过程和视图的比较视图存储过程语句只能是SELECT语句可以包含程序流、逻辑以及SELECT语句输入、返回结果不能接受参数，只能返回结果集可以有输入输出参数，也可以有返回值典型应用多个表的连接查询完成某个特定的较复杂的任务存储过程使用和管理创建和执行存储过程管理存储过程修改和删除存储过程简单语法：CREATEPROCEDURE存储过程的名字ASSQL语句创建存储过程的步骤：1、编写符合要求的SQL语句；2、测试SQL语句是否正确；3、按照存储过程的语法，创建该存储过程；4、执行存储过程，验证其正确性。创建存储过程一个最简单的存储过程例子CREATEPROCEDUREpro_studentASSELECTsno,snameFROMstudentSELECTsno,snameFROMstudent;?与使用简单SQL语句实现有何不同？EXECUTEpro_student;在一个过程中可定义一个或几个参数。参数可以是用户自定义数据类型。参数名只能作为过程的局部名称。SQLServer中要求参数名前冠以@符号。带参数的存储过程语法：CREATEPROCEDURE存储过程名字@参数名数据类型[=默认值][,…n]ASSQL语句作用：可以返回用户输入的查询条件的信息。带输入参数的存储过程的创建CREATEPROCEDUREp_student_sno@givensnochar(5)ASSELECTsno,snameFROMstudentWHEREsno=@givensno;执行EXECp_student_sno@givensno='95001'EXECp_student_sno'95002';带一个输入参数的存储过程例子带两个输入参数的存储过程例子USEpubsGOCREATEPROCEDUREau_info@lastnamevarchar(40),@firstnamevarchar(20)ASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestAIONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idWHEREau_fname=@firstnameANDau_lname=@lastnameGO另见P288（该存储过程的调用参见联机帮助）语法：CREATEPROCEDURE存储过程名字@参数名数据类型[=默认值][,…n]OUTPUTASSQL语句注意：1、输出参数必须位于所有的输入参数之后；2、输出参数中保存的是存储过程执行完成时的当前值。带输出参数的存储过程的创建带输出参数的存储过程举例例1:CREATEPROCEDUREMathTutor@m1smallint,@m2smallint,@resultsmallintOUTPUTASSET@result=@m1*@m2GODECLARE@answersmallintEXECUTEMathTutor5,6,@answerOUTPUTSELECT'Theresultis:',@answerTheresultis:30存储过程的结果执行存储过程创建存储过程例2：创建一个带输出参数的存储过程，根据用户输入的学号，返回学生姓名、性别和年龄。CREATEPROCEDUREp_student@学号CHAR(5),@姓名VARCHAR(20)OUTPUT,@性别CHAR(2)OUTPUT,@年龄SMALLINTOUTPUTASSELECT@姓名=sname,@性别=ssex,@年龄=sageFROMstudentWHEREsno=@学号带输出参数的存储过程举例执行存储过程：DECLARE@姓名VARCHAR(20),@性别CHAR(2),@年龄SMALLINTEXECp_student95001,@姓名OUTPUT,@性别OUTPUT,@年龄OUTPUTSELECT@姓名,@性别,@年龄?本例中执行存储过程时若定义@学号CHAR(2)执行结果会如何？修改存储过程修改存储过程语法：ALTERPROC[EDURE]存储过程名[WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION]ASSQL语句ALTERPROCpro_student@givensnochar(5)WITHENCRYPTIONASSELECTsno,snameFROMstudentWHEREsno=@givensno;查看创建存储过程的文本sp_helptextprocedure_name查看存储过程一般信息sp_helpprocedure_name修改存储过程名称sp_renameold_name,new_name查看存储过程引用表情况sp_dependsprocedure_name管理存储过程删除存储过程删除存储过程语法：DROPPROCEDURE存储过程名[,…n]DROPPROCpro_student介绍触发器思考：当学生报名选修SQLServer实用技术课程时，WillNum应自动加1，如何处理？什么是触发器？定义：触发器是一种特殊的存储过程，在对表或视图执行UPDATE、INSERT或DELETE操作时自动触发执行。分类：分为：INSERT、UPDATE、DELETE三种类型。触发器的作用在指定的表中数据发生变化时被调用以响应INSERT、UPDATE或DELETE事件强制执行业务规则INSERT触发器工作原理DELETE触发器工作原理UPDATE触发器工作原理触发器工作原理Inserted和Deleted介绍触发器语句中使用了两个特殊的表：Inserted和Deleted，它们由系统自动创建和管理。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；但不能直接对表中的数据进行更改。Inserted用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。Deleted表用于存储DELETE和UPDATE语句所影响的行的复本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到deleted表中。Deleted表和触发器表通常没有相同的行。INSERT触发器工作原理向定义了INSERT触发器的表发送INSERT语句INSERT[OrderDetails]VALUES(10523,2,19.00,5,0.2)OrderDetailsOrderID105221052310524ProductID10417UnitPrice31.009.6530.00Quantity7924Discount0.20.150.0519.0020.210523INSERT语句记录到日志中inserted10523219.0050.2执行触发器动作OrderDetailsOrderID105221052310524ProductID10417UnitPrice31.009.6530.00Quantity7924Discount0.20.150.0519.0020.210523TriggerCode:USENorthwindCREATETRIGGEROrdDet_InsertON[OrderDetails]FORINSERTASUPDATEPSETUnitsInStock=(P.UnitsInStock–I.Quantity)FROMProductsASPINNERJOINInsertedASIONP.ProductID=I.ProductIDUPDATEPSETUnitsInStock=(P.UnitsInStock–I.Quantity)FROMProductsASPINNERJOINInsertedASIONP.ProductID=I.ProductIDProductsProductIDUnitsInStock……123415106520215向定义了INSERT触发器的表发送INSERT语句INSERT语句记录到日志中执行触发器动作123DELETE触发器工作原理向定义DELETE触发器的表发送DELETE语句Deleted4DairyProductsCheeses0x15…日志记录DELETE语句CategoriesCategoryID123CategoryNameBeveragesCondimentsConfectionsDescriptionSoftdrinks,coffees…Sweetandsavory…Desserts,candies,…Picture0x15…0x15…0x15…0x15…CheesesDairyProducts4DELETECategoriesWHERECategoryID=4USENorthwindCREATETRIGGERCategory_DeleteONCategoriesFORDELETEASUPDATEPSETDiscontinued=1FROMProductsASPINNERJOINdeletedASdONP.CategoryID=d.CategoryIDProductsProductIDDiscontinued……12340000执行触发器动作21UPDATEPSETDiscontinued=1FROMProductsASPINNERJOINdeletedASdONP.CategoryID=d.CategoryID向定义DELETE触发器的表发送DELETE语句日志记录DELETE语句执行触发器动作123UPDATE触发器工作原理向定义UPDATE触发器的表发送UPDATE语句UPDATEEmployeesSETEmployeeID=17WHEREEmployeeID=2在日志中以INSERT和DELETE语句的方式记录UPDATE语句EmployeesEmployeeIDLastNameFirstNameTitleHireDate1234DavolioBarrLeverlingPeacockNancyAndrewJanetMargaretSalesRep.RSalesRep.SalesRep.~~~~~~~~~~~~2FullerAndrewVicePres.~~~inserted17FullerAndrewVicePres.~~~deleted2FullerAndrewVicePres.~~~执行触发器动作USENorthwindGOCREATETRIGGEREmployee_UpdateONEmployeesFORUPDATEASIFUPDATE(EmployeeID)BEGINTRANSACTIONRAISERROR('Transactioncannotbepr