《存储过程与触发器讲稿.ppt》由会员分享，可在线阅读，更多相关《存储过程与触发器讲稿.ppt（26页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、存储过程与触发器存储过程与触发器第一页，讲稿共二十六页哦9.1 存储过程存储过程v 问题 要把完成某功能的SQL做成类似C语言的函数，供需要时调用，如何做？v 1.什么是存储过程？存储过程是SQL Server服务器上一组预编译的Transact-SQL语句，它们被集合在一起用于完成某项任务，它可以接受参数、返回参数值，并且可以嵌套调用。9.1.1 存储过程概述存储过程概述 第二页，讲稿共二十六页哦9.1.1 存储过程概述存储过程概述 2.存储过程的功能特点v模块化编程创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。v执行速度快存储过程第一次被执行后，就驻留在内存中。以后执行就省去
2、了重新分析、优化、编译的过程。v减少网络通信量有了存储过程后，在网络上只要一条语句就能执行一个存储过程。v保证系统安全性通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。第三页，讲稿共二十六页哦v3.SQL Server存储过程的类型包括：系统存储过程 用户定义存储过程 临时存储过程 扩展存储过程9.1.1 存储过程概述存储过程概述 第四页，讲稿共二十六页哦系统存储过程：v 是指由系统提供的存储过程，主要存储在master数据库中并以sp_为前缀，。v 通过系统存储过程，SQL Server中的许多管理性或信息性的活动都可以完成(例如使用sp
3、_depends、sp_helptext可以了解数据库对象、数据库信息)。v 尽管系统存储过程被放在master数据库中，仍可以在其他数据库中对其进行调用(调用时，不必在存储过程名前加上数据库名)。用户自定义存储过程：v 是由用户创建并能完成某一特定功能(例如查询用户所需数据信息)的存储过程。v 它处于用户创建的数据库中，存储过程名前没有前缀sp_。9.1.1 存储过程概述存储过程概述 第五页，讲稿共二十六页哦存储过程和视图的比较存储过程和视图的比较 视图视图存储过程存储过程语句语句只能是只能是SELECT语句语句可以包含程序流、逻辑可以包含程序流、逻辑以及以及SELECT语句语句输入、输入、
4、返回结果返回结果不能接受参数，只能不能接受参数，只能返回结果集返回结果集可以有输入输出参数，可以有输入输出参数，也可以有返回值也可以有返回值典型应用典型应用多个表的连接查询多个表的连接查询完成某个特定的较复杂完成某个特定的较复杂的任务的任务9.1.1 存储过程概述存储过程概述 第六页，讲稿共二十六页哦1.创建和执行不带参数的存储过程（1）使用企业管理器创建存储过程（2）使用CREATE PROCEDURE语句创建存储过程CREATE PROCEDURE 所有者.存储过程名 AS SQL 语句,.n执行：EXECUTE 存储过程名9.1.2 存储过程的创建与使用存储过程的创建与使用第七页，讲稿共
5、二十六页哦【例例1】创建并执行存储过程创建并执行存储过程proc_reader，查询读者姓名及其所在，查询读者姓名及其所在单位名称。单位名称。CREATE PROC proc_readerASSELECT Reader.ReaderName,Department.DepartmentNameFROM Reader,DepartmentWHERE Reader.DepartmentNo=Department.DepartmentNoGOEXEC proc_readerv 存储过程创建后，存储过程的名称存放在sysobject表中，文本存放在syscomments表中。9.1.2 存储过程的创建与
6、使用存储过程的创建与使用第八页，讲稿共二十六页哦2.创建和执行带参数的存储过程（1）使用CREATE PROCEDURE创建带参数存储过程 CREATE PROCEDURE 所有者.存储过程名 参数 数据类型=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS SQL 语句,.n（2）执行带参数存储过程EXECUTE 整型变量=存储过程名|存储过程变量参数=参量值|变量 OUTPUT|DEFAULT,.n WITH RECOMPLILE9.1.2 存储过程的创建与使用存储过程的创建
7、与使用第九页，讲稿共二十六页哦v 在SQL查询分析器中输入命令：DECLARE readerno char(10)EXEC proc_bookborrowed readerno=101v 运行的结果：CREATE PROC proc_bookborrowed(readerno char(10)ASSELECT Reader.ReaderName,Borrow.BookNo,Book.Bookname FROM Reader,Borrow,BookWHERE Reader.ReaderNo=Borrow.ReaderNo AND Book.BookNo=Borrow.BookNo AND Rea
8、der.ReaderNo=readerno【例2】创建一个带有输入参数的存储过程 proc_bookborrowed，查询指定编号的读者所借图书信息。9.1.2 存储过程的创建与使用存储过程的创建与使用第十页，讲稿共二十六页哦【例3】创建一个带有输入和输出参数的存储过程proc_booknumber，查询指定编号的读者所借图书的册数。输入参数为readerno，将其默认值设置为“101”。CREATE PROC proc_booknumber(readerno char(10)=101,booknumber int OUTPUT)ASSELECT ReaderName,booknumber=C
9、OUNT(BookNo)FROM Reader INNER JOIN Borrow ON Reader.ReaderNo=Borrow.ReaderNoWHERE Reader.ReaderNo=readernov在在SQL查询分析器中输入命令：查询分析器中输入命令：DECLARE readerno char(10),booknumber int EXEC proc_booknumber 203,booknumber OUTPUT EXEC proc_booknumber DEFAULT,booknumber OUTPUT 9.1.2 存储过程的创建与使用存储过程的创建与使用第十一页，讲稿共二
10、十六页哦执行时，参数可以由位置标识，也可以由名字标识。执行时，参数可以由位置标识，也可以由名字标识。v 例如，定义一个具有3个参数的存储过程：CREATE PROC myproc val1 int,val2 int,val3 intAS.v 参数以位置传递：EXEC myproc 10,20,15v 参数以名字传递，每个值由对应的参数名引导：EXEC myproc val2=20,val1=10,val3=15v 按名字传递参数比按位置传递参数具有更大的灵活性。但是，按位置传递参数却具有更快的速度。9.1.2 存储过程的创建与使用存储过程的创建与使用第十二页，讲稿共二十六页哦（1）使用系统存储
11、过程查看和修改存储过程v查看创建存储过程的文本 sp_helptext procedure_namev查看存储过程一般信息 sp_help procedure_namev查看存储过程引用表情况 sp_depends procedure_namev 存储过程列表 sp_stored_procedures9.1.3查看、修改和删除存储过程查看、修改和删除存储过程1.使用企业管理器查看、修改和删除存储过程2.使用T-SQL语句查看、修改和删除存储过程第十三页，讲稿共二十六页哦v EXEC sp_helptext proc_bookborrowed -查看存储过程的定义v EXEC sp_depend
12、s proc_bookborrowed -查看存储过程的相关性v EXEC sp_help proc_bookborrowed -查看存储过程的参数第十四页，讲稿共二十六页哦（2）使用ALTER PROCEDURE 语句修改存储过程ALTER PROCEDURE 所有者.存储过程名;整数参数 数据类型=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION AS SQL 语句,.n（3）删除存储过程DROP PROCEDURE 所有者.存储过程名,.n9.1.3查看、修改和删除存储过程查看、修改和删除存储过程第十五页，讲稿共二十六页哦【例4】修改例8-2中的存储过
13、程，要求加密存储过程的定义文本，查询指定姓名的读者所借图书信息。ALTER PROC proc_bookborrowed(readername varchar(60)WITH ENCRYPTIONASSELECT Reader.ReaderName,Borrow.BookNo,Book.BookName FROM Reader,Borrow,BookWHERE Reader.ReaderNo=Borrow.ReaderNo and Book.BookNo=Borrow.BookNo and Reader.ReaderName=readernamev 执行存储过程，查询读者“王明”借阅图书的信息
14、。DECLARE readername varchar(60)EXEC proc_bookborrowed readername=王明v 使用系统存储过程sp_helptext来查看存储过程proc_bookborrowed的定义，在查询分析器中输入下述语句：v EXEC sp_helptext proc_bookborrowed第十六页，讲稿共二十六页哦带输出参数的存储过程举例CREATE PROCEDURE MathTutor m1 smallint,m2 smallint,result smallint OUTPUTAS SET result=m1*m2DECLARE answer sm
15、allintEXECUTE MathTutor 5,6,answer OUTPUTSELECT The result is:,answerThe result is:30第十七页，讲稿共二十六页哦9.2.1触发器概述v触发器是一种特殊类型的存储过程。v触发器主要是通过事件进行触发而被执行的。当对某一表进行UPDATE、INSERT、DELETE操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。v触发器是除了约束、默认值、规则外，用来维护数据完整性的另一种手段。9.2 触发器触发器第十八页，讲稿共二十六页哦在触发器的执行过
16、程中，SQL Server建立和管理两个临时的虚拟表：Deleted表和Inserted表。这两个表包含了在激发触发器的操作中插入或删除的所有记录。vINSERT 触发器工作原理 执行插入操作时激活触发器，将插入的元组拷贝到逻辑表insertedvDELETE 触发器工作原理 执行删除操作时激活触发器，将删除的元组保存在逻辑表deletedvUPDATE触发器工作原理 执行更新操作时激活触发器，把要被更新的数据移入deleted表，更新数据插入到表inserted触发器工作原理9.2.1 触发器概述触发器概述第十九页，讲稿共二十六页哦9.2.2创建触发器创建触发器v Create Trigge
17、r 触发器名ON 表名 WITH ENCRYPTION FOR Delete,Insert ,Update AS SQL语句组第二十页，讲稿共二十六页哦【例5】创建一个触发器，当图书管理系统数据库的Reader表中插入新数据成功后，利用触发器产生提示信息“成功插入一条记录”-创建触发器CREATE TRIGGER tr_reader ON Reader FOR INSERTAS DECLARE err int SELECT err=error IF(err=0)BEGIN PRINT 成功插入一条记录 ENDRETURN-测试触发器INSERT into Reader(ReaderNO,Rea
18、derName)Values(301,马帅)会显示“成功插入一条记录”9.2.2创建触发器创建触发器第二十一页，讲稿共二十六页哦【例例6 6】在图书管理系统数据库的在图书管理系统数据库的Reader Reader 表和表和BorrowBorrow表之间具有参照关系，要求当删除表之间具有参照关系，要求当删除ReaderReader表中的记录时，激活触发器表中的记录时，激活触发器tr_Deletetr_Delete，在，在BorrowBorrow表中也删除相匹配的记录行。表中也删除相匹配的记录行。CREATE TRIGGER tr_Delete ON Reader FOR DELETE AS DE
19、CLARE delcount INT DECLARE readno CHAR(10)SELECT delcount=COUNT(*)FROM deleted IF delcount0 BEGIN -从临时表 deleted 中获取要删除的读者编号 SELECT readno=ReaderNo FROM deleted -从Borrow表中删除该员工的销售记录 DELETE FROM Borrow WHERE ReaderNo=readno END-测试触发器测试触发器INSERT into Borrow Values(301,01001,2010-01-13,2010-3-13)DELETE
20、FROM Reader WHERE ReaderNo=301 第二十二页，讲稿共二十六页哦DELETE FROM STUDENT WHERE Sno=101CREATE TRIGGER trg_Student_Delete ON StudentFOR DELETEAS delete from gradewhere Sno=(select Sno from deleted)studentgrade使用触发器维护数据完整性（删除）第二十三页，讲稿共二十六页哦9.2.3管理触发器（1）查看触发器信息：查看触发器一般信息sp_help 触发器名称 查看触发器的定义sp_helptext 触发器名称 查
21、看触发器所引用的表或者指定表所涉及的触发器sp_depends 触发器名称第二十四页，讲稿共二十六页哦（2）修改触发器 Alter Trigger 触发器名 ON 表名 WITH ENCRYPTION FOR Delete,Insert ,Update As SQL语句组【例7】修改触发器tr_reader，当执行添加、更新或删除Reader表的数据时，激活该触发器，显示deleted和inserted临时表中的数据。ALTER TRIGGER tr_readerON Reader FOR INSERT,UPDATE,DELETEAS SELECT*FROM inserted SELECT*FROM deleted9.2.3管理触发器第二十五页，讲稿共二十六页哦（5）删除触发器：DROP TRIGGER 触发器名称DROP TRIGGER tr_Delete,tr_reader_for_all（3）使用sp_rename系统存储过程修改触发器的名字：sp_rename oldname,newnameEXEC sp_rename tr_reader,tr_reader_for_all（4）禁止或启用触发器ALTER TABLE 表名ENABLE|DISABLE TRIGGER ALL|触发器名称,n9.2.3管理触发器第二十六页，讲稿共二十六页哦