存储过程与触发器

存储过程与触发器第1页，共26页，2023年，2月20日，星期四9.1存储过程问题要把完成某功能的SQL做成类似C语言的函数，供需要时调用，如何做？1.什么是存储过程？存储过程是SQLServer服务器上一组预编译的Transact-SQL语句，它们被集合在一起用于完成某项任务，它可以接受参数、返回参数值，并且可以嵌套调用。9.1.1存储过程概述第2页，共26页，2023年，2月20日，星期四9.1.1存储过程概述2.存储过程的功能特点模块化编程创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。执行速度快存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。减少网络通信量有了存储过程后，在网络上只要一条语句就能执行一个存储过程。保证系统安全性通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。第3页，共26页，2023年，2月20日，星期四3.SQLServer存储过程的类型包括：系统存储过程用户定义存储过程临时存储过程扩展存储过程9.1.1存储过程概述第4页，共26页，2023年，2月20日，星期四系统存储过程：是指由系统提供的存储过程，主要存储在master数据库中并以sp_为前缀，。通过系统存储过程，SQLServer中的许多管理性或信息性的活动都可以完成(例如使用sp_depends、sp_helptext可以了解数据库对象、数据库信息)。尽管系统存储过程被放在master数据库中，仍可以在其他数据库中对其进行调用(调用时，不必在存储过程名前加上数据库名)。用户自定义存储过程：是由用户创建并能完成某一特定功能(例如查询用户所需数据信息)的存储过程。它处于用户创建的数据库中，存储过程名前没有前缀sp_。9.1.1存储过程概述第5页，共26页，2023年，2月20日，星期四存储过程和视图的比较视图存储过程语句只能是SELECT语句可以包含程序流、逻辑以及SELECT语句输入、返回结果不能接受参数，只能返回结果集可以有输入输出参数，也可以有返回值典型应用多个表的连接查询完成某个特定的较复杂的任务9.1.1存储过程概述第6页，共26页，2023年，2月20日，星期四1.创建和执行不带参数的存储过程（1）使用企业管理器创建存储过程（2）使用CREATEPROCEDURE语句创建存储过程CREATEPROC[EDURE][所有者.]存储过程名

ASSQL语句[,...n]执行：

[EXEC[UTE]]存储过程名9.1.2存储过程的创建与使用第7页，共26页，2023年，2月20日，星期四【例1】创建并执行存储过程proc_reader，查询读者姓名及其所在单位名称。CREATEPROCproc_readerASSELECTReader.ReaderName,Department.DepartmentNameFROMReader,DepartmentWHEREReader.DepartmentNo=Department.DepartmentNoGOEXECproc_reader存储过程创建后，存储过程的名称存放在sysobject表中，文本存放在syscomments表中。9.1.2存储过程的创建与使用第8页，共26页，2023年，2月20日，星期四2.创建和执行带参数的存储过程（1）使用CREATEPROCEDURE创建带参数存储过程

CREATEPROC[EDURE][所有者.]存储过程名

[@参数 数据类型[=default][OUTPUT]][,...n][WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION][FORREPLICATION]ASSQL语句[,...n]（2）执行带参数存储过程[EXEC[UTE]][@整型变量=]存储过程名|@存储过程变量[[@参数=]参量值|@变量[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPLILE]9.1.2存储过程的创建与使用第9页，共26页，2023年，2月20日，星期四在SQL查询分析器中输入命令：

DECLARE@readernochar(10)EXECproc_bookborrowed@readerno='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存储过程的创建与使用第10页，共26页，2023年，2月20日，星期四【例3】创建一个带有输入和输出参数的存储过程proc_booknumber，查询指定编号的读者所借图书的册数。输入参数为@readerno，将其默认值设置为“101”。CREATEPROCproc_booknumber(@readernochar(10)='101',@booknumberintOUTPUT)ASSELECTReaderName,@booknumber=COUNT(BookNo)FROMReaderINNERJOINBorrowONReader.ReaderNo=Borrow.ReaderNoWHEREReader.ReaderNo=@readerno在SQL查询分析器中输入命令：

DECLARE@readernochar(10),@booknumberintEXECproc_booknumber'203',@booknumberOUTPUTEXECproc_booknumberDEFAULT,@booknumberOUTPUT

9.1.2存储过程的创建与使用第11页，共26页，2023年，2月20日，星期四执行时，参数可以由位置标识，也可以由名字标识。例如，定义一个具有3个参数的存储过程：CREATEPROCmyproc@val1int,@val2int,@val3intAS...参数以位置传递：EXECmyproc10,20,15参数以名字传递，每个值由对应的参数名引导：EXECmyproc@val2=20,@val1=10,@val3=15按名字传递参数比按位置传递参数具有更大的灵活性。但是，按位置传递参数却具有更快的速度。9.1.2存储过程的创建与使用第12页，共26页，2023年，2月20日，星期四（1）使用系统存储过程查看和修改存储过程查看创建存储过程的文本

sp_helptextprocedure_name查看存储过程一般信息

sp_helpprocedure_name查看存储过程引用表情况

sp_dependsprocedure_name存储过程列表

sp_stored_procedures9.1.3查看、修改和删除存储过程1.使用企业管理器查看、修改和删除存储过程2.使用T-SQL语句查看、修改和删除存储过程第13页，共26页，2023年，2月20日，星期四EXECsp_helptextproc_bookborrowed--查看存储过程的定义EXECsp_dependsproc_bookborrowed--查看存储过程的相关性EXECsp_helpproc_bookborrowed--查看存储过程的参数第14页，共26页，2023年，2月20日，星期四（2）使用ALTERPROCEDURE语句修改存储过程ALTERPROC[EDURE][所有者.]存储过程名[;整数][@参数 数据类型[=default][OUTPUT][,...n][WITHRECOMPILE|ENCRYPTION]ASSQL语句[,...n]（3）删除存储过程DROPPROC[EDURE][所有者.]存储过程名[,...n]9.1.3查看、修改和删除存储过程第15页，共26页，2023年，2月20日，星期四【例4】修改例8-2中的存储过程，要求加密存储过程的定义文本，查询指定姓名的读者所借图书信息。ALTERPROCproc_bookborrowed(@readernamevarchar(60))WITHENCRYPTIONASSELECTReader.ReaderName,Borrow.BookNo,Book.BookNameFROMReader,Borrow,BookWHEREReader.ReaderNo=Borrow.ReaderNoandBook.BookNo=Borrow.BookNoandReader.ReaderName=@readername执行存储过程，查询读者“王明”借阅图书的信息。DECLARE@readernamevarchar(60)EXECproc_bookborrowed@readername='王明'使用系统存储过程sp_helptext来查看存储过程proc_bookborrowed的定义，在查询分析器中输入下述语句：EXECsp_helptextproc_bookborrowed第16页，共26页，2023年，2月20日，星期四带输出参数的存储过程举例CREATEPROCEDUREMathTutor@m1smallint,@m2smallint,@resultsmallintOUTPUTASSET@result=@m1*@m2DECLARE@answersmallintEXECUTEMathTutor5,6,@answerOUTPUTSELECT'Theresultis:',@answerTheresultis:30存储过程的结果执行存储过程创建存储过程第17页，共26页，2023年，2月20日，星期四9.2.1触发器概述触发器是一种特殊类型的存储过程。触发器主要是通过事件进行触发而被执行的。当对某一表进行UPDATE、INSERT、DELETE操作时，SQLServer就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器是除了约束、默认值、规则外，用来维护数据完整性的另一种手段。9.2触发器第18页，共26页，2023年，2月20日，星期四在触发器的执行过程中，SQLServer建立和管理两个临时的虚拟表：Deleted表和Inserted表。这两个表包含了在激发触发器的操作中插入或删除的所有记录。INSERT触发器工作原理执行插入操作时激活触发器，将插入的元组拷贝到逻辑表insertedDELETE触发器工作原理执行删除操作时激活触发器，将删除的元组保存在逻辑表deletedUPDATE触发器工作原理执行更新操作时激活触发器，把要被更新的数据移入deleted表，更新数据插入到表inserted触发器工作原理9.2.1触发器概述第19页，共26页，2023年，2月20日，星期四9.2.2创建触发器CreateTrigger

触发器名

ON

表名

[WITHENCRYPTION]

FOR{[Delete][,][Insert][,][Update]}

ASSQL语句组第20页，共26页，2023年，2月20日，星期四【例5】创建一个触发器，当图书管理系统数据库的Reader表中插入新数据成功后，利用触发器产生提示信息“成功插入一条记录”--创建触发器CREATETRIGGERtr_readerONReaderFORINSERTASDECLARE@errintSELECT@err=@@errorIF(@err=0)BEGINPRINT'成功插入一条记录'ENDRETURN--测试触发器INSERTintoReader(ReaderNO,ReaderName)Values('301','马帅')会显示“成功插入一条记录”9.2.2创建触发器第21页，共26页，2023年，2月20日，星期四【例6】在图书管理系统数据库的Reader表和Borrow表之间具有参照关系，要求当删除Reader表中的记录时，激活触发器tr_Delete，在Borrow表中也删除相匹配的记录行。CREATETRIGGERtr_DeleteONReaderFORDELETEASDECLARE@delcountINTDECLARE@readnoCHAR(10)SELECT@delcount=COUNT(*)FROMdeletedIF@delcount>0BEGIN--从临时表deleted中获取要删除的读者编号SELECT@readno=ReaderNoFROMdeleted--从Borrow表中删除该员工的销售记录

DELETEFROMBorrowWHEREReaderNo=@readnoEND--测试触发器INSERTintoBorrowValues('301','01001','2010-01-13','2010-3-13')DELETEFROMReaderWHEREReaderNo='301'第22页，共26页，2023年，2月20日，星期四DELETEFROMSTUDENTWHERESno=‘101’CREATETRIGGERtrg_Student_DeleteONStudentFORDELETEASdeletefromgradewhereSno=(selectSnofromdeleted)studentgrade使用触发器维护数据完整性（删除）第23页，共26页，2023年，2月20日，星期四9.2.3管理触发器（1）查看触发器信息：查看触发器一般信息

sp_help‘触发器名称’ 查看触发器的定义

sp_helptext‘触发器名称’