存储过程与触发器.ppt

第9章 存储过程、触发器,9.1 存储过程,问题 要把完成某功能的SQL做成类似C语言的函数，供需要时调用，如何做？ 1.什么是存储过程？ 存储过程是SQL Server服务器上一组预编译的Transact-SQL语句，它们被集合在一起用于完成某项任务，它可以接受参数、返回参数值，并且可以嵌套调用。,9.1.1 存储过程概述,9.1.1 存储过程概述,2. 存储过程的功能特点 模块化编程 创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。 执行速度快 存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。 减少网络通信量 有了存储过程后，在网络上只要一条语句就能执行一个存储过程。 保证系统安全性 通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。,3.SQL Server存储过程的类型包括： 系统存储过程 用户定义存储过程 临时存储过程 扩展存储过程,9.1.1 存储过程概述,系统存储过程： 是指由系统提供的存储过程，主要存储在master数据库中并以sp_为前缀，。 通过系统存储过程，SQL Server中的许多管理性或信息性的活动都可以完成(例如使用sp_depends、sp_helptext可以了解数据库对象、数据库信息) 。 尽管系统存储过程被放在master数据库中，仍可以在其他数据库中对其进行调用(调用时，不必在存储过程名前加上数据库名)。 用户自定义存储过程： 是由用户创建并能完成某一特定功能(例如查询用户所需数据信息)的存储过程。 它处于用户创建的数据库中，存储过程名前没有前缀sp_。,9.1.1 存储过程概述,存储过程和视图的比较,9.1.1 存储过程概述,1. 创建和执行不带参数的存储过程 （1）使用企业管理器创建存储过程 （2）使用CREATE PROCEDURE语句创建存储过程 CREATE PROCEDURE 所有者.存储过程名 AS SQL 语句,.n 执行： EXECUTE 存储过程名,9.1.2 存储过程的创建与使用,【例1】创建并执行存储过程proc_reader，查询读者姓名及其所在单位名称。,存储过程创建后，存储过程的名称存放在sysobject表中，文本存放在syscomments表中。,9.1.2 存储过程的创建与使用,2. 创建和执行带参数的存储过程 （1）使用CREATE PROCEDURE创建带参数存储过程 CREATE PROCEDURE 所有者.存储过程名 参数 数据类型 =defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS SQL 语句,.n （2）执行带参数存储过程 EXECUTE 整型变量=存储过程名|存储过程变量 参数=参量值|变量 OUTPUT|DEFAULT,.n WITH RECOMPLILE,9.1.2 存储过程的创建与使用,在SQL查询分析器中输入命令： DECLARE readerno char(10) EXEC proc_bookborrowed readerno= 101 运行的结果：,CREATE PROC proc_bookborrowed (readerno char(10) AS SELECT Reader.ReaderName, Borrow.BookNo,Book.Bookname FROM Reader,Borrow,Book WHERE Reader.ReaderNo=Borrow.ReaderNo AND Book.BookNo=Borrow.BookNo AND Reader.ReaderNo = readerno,【例2】创建一个带有输入参数的存储过程 proc_bookborrowed，查询指定编号的读者所借图书信息。,9.1.2 存储过程的创建与使用,【例3】创建一个带有输入和输出参数的存储过程proc_booknumber，查询指定编号的读者所借图书的册数。输入参数为readerno，将其默认值设置为“101”。,CREATE PROC proc_booknumber(readerno char(10)= 101, booknumber int OUTPUT) AS SELECT ReaderName, booknumber = COUNT(BookNo) FROM Reader INNER JOIN Borrow ON Reader.ReaderNo = Borrow.ReaderNo WHERE Reader.ReaderNo = readerno,在SQL查询分析器中输入命令： DECLARE readerno char(10), booknumber int EXEC proc_booknumber 203 , booknumber OUTPUT EXEC proc_booknumber DEFAULT, booknumber OUTPUT,9.1.2 存储过程的创建与使用,执行时，参数可以由位置标识，也可以由名字标识。,例如，定义一个具有3个参数的存储过程： CREATE PROC myproc val1 int, val2 int, val3 int AS . 参数以位置传递： EXEC myproc 10,20,15 参数以名字传递，每个值由对应的参数名引导： EXEC myproc val2=20,val1=10,val3=15 按名字传递参数比按位置传递参数具有更大的灵活性。但是，按位置传递参数却具有更快的速度。,9.1.2 存储过程的创建与使用,（1）使用系统存储过程查看和修改存储过程 查看创建存储过程的文本 sp_helptext procedure_name 查看存储过程一般信息 sp_help procedure_name 查看存储过程引用表情况 sp_depends procedure_name 存储过程列表 sp_stored_procedures,9.1.3查看、修改和删除存储过程,1. 使用企业管理器查看、修改和删除存储过程,2. 使用T-SQL语句查看、修改和删除存储过程,EXEC sp_helptext proc_bookborrowed -查看存储过程的定义 EXEC sp_depends proc_bookborrowed -查看存储过程的相关性 EXEC sp_help proc_bookborrowed -查看存储过程的参数,（2）使用ALTER PROCEDURE 语句修改存储过程 ALTER PROCEDURE 所有者.存储过程名;整数 参数 数据类型 =defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION AS SQL 语句,.n （3）删除存储过程 DROP PROCEDURE 所有者.存储过程名,.n,9.1.3查看、修改和删除存储过程,【例4】修改例8-2中的存储过程，要求加密存储过程的定义文本，查询指定姓名的读者所借图书信息。 ALTER PROC proc_bookborrowed (readername varchar(60) WITH ENCRYPTION AS SELECT Reader.ReaderName, Borrow.BookNo,Book.BookName FROM Reader,Borrow,Book WHERE Reader.ReaderNo=Borrow.ReaderNo and Book.BookNo=Borrow.BookNo and Reader.ReaderName = readername 执行存储过程，查询读者“王明”借阅图书的信息。 DECLARE readername varchar(60) EXEC proc_bookborrowed readername=王明 使用系统存储过程sp_helptext来查看存储过程proc_bookborrowed的定义，在查询分析器中输入下述语句： EXEC sp_helptext proc_bookborrowed,带输出参数的存储过程举例,CREATE PROCEDURE MathTutor m1 smallint, m2 smallint, result smallint OUTPUT AS SET result = m1* m2 DECLARE answer smallint EXECUTE MathTutor 5,6, answer OUTPUT SELECT The result is: , answer The result is: 30,存储过程 的结果,执行存储过程,创建存储过程,9.2.1触发器概述 触发器是一种特殊类型的存储过程。 触发器主要是通过事件进行触发而被执行的。当对某一表进行UPDATE、INSERT、DELETE操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。 触发器是除了约束、默认值、规则外，用来维护数据完整性的另一种手段。,9.2 触发器,在触发器的执行过程中，SQL Server建立和管理两个临时的虚拟表：Deleted表和Inserted表。这两个表包含了在激发触发器的操作中插入或删除的所有记录。 INSERT 触发器工作原理 执行插入操作时激活触发器，将插入的元组拷贝到逻辑表inserted DELETE 触发器工作原理 执行删除操作时激活触发器，将删除的元组保存在逻辑表deleted UPDATE触发器工作原理 执行更新操作时激活触发器，把要被更新的数据移入deleted表，更新数据插入到表inserted,触发器工作原理,9.2.1 触发器概述,9.2.2创建触发器,Create Trigger 触发器名 ON 表名 WITH ENCRYPTION FOR Delete, Insert , Update AS SQL语句组,【例5】创建一个触发器，当图书管理系统数据库的Reader表中插入新数据成功后，利用触发器产生提示信息“成功插入一条记录”,-创建触发器 CREATE TRIGGER tr_reader ON Reader FOR INSERT AS DECLARE err int SELECT err=error IF( err=0) BEGIN PRINT 成功插入一条记录 END RETURN -测试触发器 INSERT into Reader(ReaderNO, ReaderName) Values(301,马帅) 会显示“成功插入一条记录”,9.2.2创建触发器,【例6】在图书管理系统数据库的Reader 表和Borrow表之间具有参照关系，要求当删除Reader表中的记录时，激活触发器tr_Delete，在Borrow表中也删除相匹配的记录行。,CREATE TRIGGER tr_Delete ON Reader FOR DELETE AS DECLARE 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 FROM Reader WHERE ReaderNo =301,DELETE FROM STUDENT WHERE Sno=101,CREATE TRIGGER trg_Student_Delete ON Student FOR DELETE AS delete from grade where Sno=(select Sno from deleted),student,grade,使用触发器维护数据完整性（删除）,9.2.3管理触发器,（1）查看触发器信息： 查看触发器一般信息 sp_help 触发器名称 查看触发器的定义 sp_helptext 触发器名称 查看触发器所引用的表或者指定表所涉及的触发器 sp_depends 触发器名称,（2）修改触发器 Alter Trigger 触发器名 ON 表名 WITH ENCRYPTION FOR Delete, Insert , Update As SQL语句组 【例7】修改触发器tr_reader，当执行添加、更新或删除Reader表的数据时，激活该触发器，显示deleted和inserted临时表中的数据。 ALTER TRIGGER tr_reader ON Reader FOR INSERT,UPDATE,DELETE AS SELECT * FRO