存储过程与触发器.ppt

第9章存储过程、触发器,9.1存储过程,问题要把完成某功能的SQL做成类似C语言的函数，供需要时调用，如何做？1.什么是存储过程？存储过程是SQLServer服务器上一组预编译的Transact-SQL语句，它们被集合在一起用于完成某项任务，它可以接受参数、返回参数值，并且可以嵌套调用。,9.1.1存储过程概述,9.1.1存储过程概述,2.存储过程的功能特点模块化编程创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。执行速度快存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。减少网络通信量有了存储过程后，在网络上只要一条语句就能执行一个存储过程。保证系统安全性通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。,3.SQLServer存储过程的类型包括：系统存储过程用户定义存储过程临时存储过程扩展存储过程,9.1.1存储过程概述,系统存储过程：是指由系统提供的存储过程，主要存储在master数据库中并以sp_为前缀，。通过系统存储过程，SQLServer中的许多管理性或信息性的活动都可以完成(例如使用sp_depends、sp_helptext可以了解数据库对象、数据库信息)。尽管系统存储过程被放在master数据库中，仍可以在其他数据库中对其进行调用(调用时，不必在存储过程名前加上数据库名)。用户自定义存储过程：是由用户创建并能完成某一特定功能(例如查询用户所需数据信息)的存储过程。它处于用户创建的数据库中，存储过程名前没有前缀sp_。,9.1.1存储过程概述,存储过程和视图的比较,9.1.1存储过程概述,1.创建和执行不带参数的存储过程（1）使用企业管理器创建存储过程（2）使用CREATEPROCEDURE语句创建存储过程CREATEPROCEDURE所有者.存储过程名ASSQL语句,.n执行：EXECUTE存储过程名,9.1.2存储过程的创建与使用,【例1】创建并执行存储过程proc_reader，查询读者姓名及其所在单位名称。,CREATEPROCproc_readerASSELECTReader.ReaderName,Department.DepartmentNameFROMReader,DepartmentWHEREReader.DepartmentNo=Department.DepartmentNoGOEXECproc_reader,存储过程创建后，存储过程的名称存放在sysobject表中，文本存放在syscomments表中。,9.1.2存储过程的创建与使用,2.创建和执行带参数的存储过程（1）使用CREATEPROCEDURE创建带参数存储过程CREATEPROCEDURE所有者.存储过程名参数数据类型=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASSQL语句,.n（2）执行带参数存储过程EXECUTE整型变量=存储过程名|存储过程变量参数=参量值|变量OUTPUT|DEFAULT,.nWITHRECOMPLILE,9.1.2存储过程的创建与使用,在SQL查询分析器中输入命令：DECLAREreadernochar(10)EXECproc_bookborrowedreaderno=101运行的结果：,CREATEPROCproc_bookborrowed(readernochar(10)ASSELECTReader.ReaderName,Borrow.BookNo,Book.BooknameFROMReader,Borrow,BookWHEREReader.ReaderNo=Borrow.ReaderNoANDBook.BookNo=Borrow.BookNoANDReader.ReaderNo=readerno,【例2】创建一个带有输入参数的存储过程proc_bookborrowed，查询指定编号的读者所借图书信息。,9.1.2存储过程的创建与使用,【例3】创建一个带有输入和输出参数的存储过程proc_booknumber，查询指定编号的读者所借图书的册数。输入参数为readerno，将其默认值设置为“101”。,CREATEPROCproc_booknumber(readernochar(10)=101,booknumberintOUTPUT)ASSELECTReaderName,booknumber=COUNT(BookNo)FROMReaderINNERJOINBorrowONReader.ReaderNo=Borrow.ReaderNoWHEREReader.ReaderNo=readerno,在SQL查询分析器中输入命令：DECLAREreadernochar(10),booknumberintEXECproc_booknumber203,booknumberOUTPUTEXECproc_booknumberDEFAULT,booknumberOUTPUT,9.1.2存储过程的创建与使用,执行时，参数可以由位置标识，也可以由名字标识。,例如，定义一个具有3个参数的存储过程：CREATEPROCmyprocval1int,val2int,val3intAS.参数以位置传递：EXECmyproc10,20,15参数以名字传递，每个值由对应的参数名引导：EXECmyprocval2=20,val1=10,val3=15按名字传递参数比按位置传递参数具有更大的灵活性。但是，按位置传递参数却具有更快的速度。,9.1.2存储过程的创建与使用,（1）使用系统存储过程查看和修改存储过程查看创建存储过程的文本sp_helptextprocedure_name查看存储过程一般信息sp_helpprocedure_name查看存储过程引用表情况sp_dependsprocedure_name存储过程列表sp_stored_procedures,9.1.3查看、修改和删除存储过程,1.使用企业管理器查看、修改和删除存储过程,2.使用T-SQL语句查看、修改和删除存储过程,EXECsp_helptextproc_bookborrowed-查看存储过程的定义EXECsp_dependsproc_bookborrowed-查看存储过程的相关性EXECsp_helpproc_bookborrowed-查看存储过程的参数,（2）使用ALTERPROCEDURE语句修改存储过程ALTERPROCEDURE所有者.存储过程名;整数参数数据类型=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTIONASSQL语句,.n（3）删除存储过程DROPPROCEDURE所有者.存储过程名,.n,9.1.3查看、修改和删除存储过程,【例4】修改例8-2中的存储过程，要求加密存储过程的定义文本，查询指定姓名的读者所借图书信息。ALTERPROCproc_bookborrowed(readernamevarchar(60)WITHENCRYPTIONASSELECTReader.ReaderName,Borrow.BookNo,Book.BookNameFROMReader,Borrow,BookWHEREReader.ReaderNo=Borrow.ReaderNoandBook.BookNo=Borrow.BookNoandReader.ReaderName=readername执行存储过程，查询读者“王明”借阅图书的信息。DECLAREreadernamevarchar(60)EXECproc_bookborrowedreadername=王明使用系统存储过程sp_helptext来查看存储过程proc_bookborrowed的定义，在查询分析器中输入下述语句：EXECsp_helptextproc_bookborrowed,带输出参数的存储过程举例,CREATEPROCEDUREMathTutorm1smallint,m2smallint,resultsmallintOUTPUTASSETresult=m1*m2DECLAREanswersmallintEXECUTEMathTutor5,6,answerOUTPUTSELECTTheresultis:,answerTheresultis:30,存储过程的结果,执行存储过程,创建存储过程,9.2.1触发器概述触发器是一种特殊类型的存储过程。触发器主要是通过事件进行触发而被执行的。当对某一表进行UPDATE、INSERT、DELETE操作时，SQLServer就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器是除了约束、默认值、规则外，用来维护数据完整性的另一种手段。,9.2触发器,在触发器的执行过程中，SQLServer建立和管理两个临时的虚拟表：Deleted表和Inserted表。这两个表包含了在激发触发器的操作中插入或删除的所有记录。INSERT触发器工作原理执行插入操作时激活触发器，将插入的元组拷贝到逻辑表insertedDELETE触发器工作原理执行删除操作时激活触发器，将删除的元组保存在逻辑表deletedUPDATE触发器工作原理执行更新操作时激活触发器，把要被更新的数据移入deleted表，更新数据插入到表inserted,触发器工作原理,9.2.1触发器概述,9.2.2创建触发器,CreateTrigger触发器名ON表名WITHENCRYPTIONFORDelete,Insert,UpdateASSQL语句组,【例5】创建一个触发器，当图书管理系统数据库的Reader表中插入新数据成功后，利用触发器产生提示信息“成功插入一条记录”,-创建触发器CREATETRIGGERtr_readerONReaderFORINSERTASDECLAREerrintSELECTerr=errorIF(err=0)BEGINPRINT成功插入一条记录ENDRETURN-测试触发器INSERTintoReader(ReaderNO,ReaderName)Values(301,马帅)会显示“成功插入一条记录”,9.2.2创建触发器,【例6】在图书管理系统数据库的Reader表和Borrow表之间具有参照关系，要求当删除Reader表中的记录时，激活触发器tr_Delete，在Borrow表中也删除相匹配的记录行。,CREATETRIGGERtr_DeleteONReaderFORDELETEASDECLAREdelcountINTDECLAREreadnoCHAR(10)SELECTdelcount=COUNT(*)FROMdeletedIFdelcount0BEGIN-从临时表deleted中获取要删除的读者编号SELECTreadno=ReaderNoFROMdeleted-从Borrow表中删除该员工的销售记录DELETEFROMBorrowWHEREReaderNo=readnoEND,-测试触发器INSERTintoBorrowValues(301,01001,2010-01-13,2010-3-13)DELETEFROMReaderWHEREReaderNo=301,DELETEFROMSTUDENTWHERESno=101,CREATETRIGGERtrg_Student_DeleteONStudentFORDELETEASdeletefromgradewhereSno=(selectSnofromdeleted),student,grade,使用触发器维护数据完整性（删除）,9.2.3管理触发器,（1）查看触发器信息：查看触发器一般信息sp_help触发器名称查看触发器的定义sp_helptext触发器名称查看触发器所引用的表或者指定表所涉及的触发器sp_depends触发器名称,（2）修改触发器AlterTrigger触发器名ON表名WITHENCRYPTIONFORDelete,Insert,UpdateAsSQL语句组【例7】修改触发器tr_reader，当执行添加、更新或删除Reader表的数据时，激活该触发器，显示deleted和inserted临时表中的数据。ALTERTRIGGERtr_readerONReaderFORINSERT,UPDATE,DELETEASS