《存储过程和触发器》PPT课件.ppt

2019/7/24,1,第十章 存储过程和触发器,2019/7/24,2,10.1存储过程,10.1.1 存储过程概述,存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。 一般用来处理需要与数据库进行频繁交互的复杂的业务。因为存储过程具有以下优点：,存储过程在服务器端运行，执行速度快。 封装业务逻辑也可以使数据库操作人员与应用系统开发人员的分工更明确，支持模块化设计。,2019/7/24,3,存储过程执行一次后，其执行计划就驻留在高速缓冲存储器，如果需要再次调用，只需要从高速缓冲存储器中调用，提高系统性能。 存储过程可以作为安全机制。通过授予用户对存储过程的操作权限来实现安全机制。,使体现企业规则的运算程序放入数据库服务器中，以便集中控制。,2019/7/24,4,10.1.2 存储过程的分类,1系统存储过程 系统存储过程存储在master数据库中，以sp_开头，可以在其他数据库中进行调用。 如：sp_help就是取得指定对象的相关信息。 2扩展存储过程 在SQL Server环境之外执行动态链接库称为扩展存储过程，以XP_开头,用来调用操作系统提供的功能。 3用户自定义的存储过程 由用户创建并能完成某一特定功能的存储过程。包括Transact-SQL和CLR两种类型。 （1）Transact-SQL存储过程是指保存的Transact-SQL语句集合，可以接受和返回用户提供参数。也可以从数据库向客户端应用程序返回数据。 （2）CLR在本书不做详细介绍。,2019/7/24,5,10.1.3 建立存储过程,1. 使用对象资源管理器建立存储过程,2. 使用T-SQL命令建立存储过程,语法形式如下： CREATE PROCEDURE procedure_name parameter data_type VARYING =defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION FOR REPLICATION AS sql_statement .n ,2019/7/24,6,【例题10.1】创建一个不带参数的存储过程，名称为pro_stu，用于检索学生的姓名、性别和家庭住址。 USE score GO -判断pro_stu存储过程是否存在，若存在，则删除 If exists(select name from sysobjects where name=pro_stu and type=p) DROP PROCEDURE pro_stu Go -建立存储过程 CREATE PROC pro_stu As SELECT student_name, sex, address FROM student,2019/7/24,7,【例题10.2】创建一个带参数的存储过程，名称为pro_class，在执行存储过程的时候输入班级编号，然后检索该班级的学生的详细信息。 USE score GO -判断pro_class存储过程是否存在，若存在，则删除 If exists(select name from sysobjects where name=pro_class and type=p) DROP PROCEDURE pro_class Go -建立存储过程 CREATE PROC pro_class c_no char(8) As SELECT * FROM student WHERE class_id=c_no,2019/7/24,8,10.1.4 存储过程的执行,语法形式如下： EXECUTE return_status = procedure_name|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE ,2019/7/24,9,1.无参存储过程的执行,【例题10.3】执行在例题10.1中创建的存储过程pro_stu。 EXEC pro_stu,在执行带参存储过程的时候参数有以下几种传递方式：,2.带参存储过程的执行,（1）顺序法,2019/7/24,10,CREATE PROC pro_update s_id char(10), c_id char(5), result int -参数 AS UPDATE score SET grade=result WHERE student_id=s_id AND course_id=c_id / *顺序法执行存储过程，把0801101号学生1001号课程的成绩修改为85分*/ EXEC pro_update 0801101, 1001, 85,【例题10.4】建立一个存储过程pro_update，通过指定学号和课程编号来把成绩表中的成绩修改为指定的某个值，然后执行该存储过程。,2019/7/24,11,EXEC pro_update s_id=0801101, result=85,c_id=1001 说明：提示法中的参数顺序可以与建立存储过程时的参数顺序不一致，而顺序法必须保持一致。,（2）提示法,【例题10.5】通过提示法来执行存储过程pro_update。,2019/7/24,12,建立存储过程时，可以指定参数的默认值，默认值必须为常量或者为NULL。,（3）使用默认参数,【例题10.6】建立一个存储过程pro_insert，用来完成向课程表中插入记录的功能，然后执行该存储过程并检查执行结果。 CREATE PROC pro_insert c_id char(5), c_name char(20), period int=64, c_type char(10)=NULL AS INSERT course VALUES( c_id ,c_name,period,c_type ) GO -执行存储过程pro_insert EXEC pro_insert 1006, c+程序设计 GO -查看插入结果 SELECT * FROM course,2019/7/24,13,在建立存储过程时，通过定义输出参数，可以从存储过程中返回一个或多个值。在执行存储过程时，可以将结果返回给返回参数。定义输出参数需要在参数定义的数据类型后使用关键字OUTPUT，或省写为OUT。,（4）使用带OUTPUT的返回参数,【例题10.7】建立一个带参数的存储过程，用于求两个整数的和，其中和定义为OUTPUT参数，然后执行存储过程并输出和。 CREATE PROC pro_sum a int ,b int, sum int output AS SET sum=a+b GO -执行存储过程 DECLARE s int EXEC pro_sum 20 ,15,s output PRINT s,2019/7/24,14,存储过程被执行以后可以返回一个整数类型的代码值，该值用来记录存储过程的执行情况。若返回值为0，表示执行成功；否则表示执行不成功。,1）用一个变量来捕捉存储过程执行后返回值。 2）使用RETURN语句为存储过程指定一个确定的返回值，以用来反映存储过程执行的结果情况。,捕捉存储过程返回值的方法,2019/7/24,15,【例题10.9】用变量来捕捉存储过程执行后返回值。建立一个存储过程，完成查询指定学生指定科目的成绩。然后执行该存储过程并捕捉执行是否成功。 CREATE PROC pro_grade s_id char(10), c_id char(5) AS SELECT grade FROM score WHERE student_id=s_id AND course_id=c_id GO DECLARE status int -声明变量,用来存放执行结果的状态 EXEC status=pro_grade 0801101,1001 SELECT status -输出状态值,2019/7/24,16,【例题10.10】使用RETURN语句为存储过程指定一个确定的返回值。建立一个存储过程，完成查询指定学生指定科目的成绩，如果成绩大于60分，返回1；如果等于60分，返回0；小于60分，则返回-1。然后执行该存储过程并输出返回值。 CREATE PROC pro_return s_id char(10), c_id char(5) AS DECLARE result int SELECT result=grade FROM score WHERE student_id=s_id AND course_id=c_id IF result60 RETURN 1 IF result=60 RETURN 0 IF result60 RETURN -1 GO DECLARE status int -声明变量,用来存放执行结果的状态 EXEC status=pro_return 0801101,1001 SELECT 状态=status -输出状态值,2019/7/24,17,10.1.6 查看、修改和删除存储过程,1.修改存储过程,语法形式如下： ALTER PROCEDURE procedure_name parameter data_type VARYING =defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n ,2019/7/24,18,【例题10.11】修改存储过程pro_sum，使其功能更改为输出两个数相加的和，不使用返回参数。,USE score GO ALTER PROC pro_sum a int ,b int AS DECLARE sum int SET sum=a+b PRINT sum,2019/7/24,19,2.查看存储过程,（1）使用对象资源管理器查看,（2）使用T-SQL命令查看,1）查看存储过程的参数及其数据类型 格式：sp_help name 2）查看存储过程的源代码 格式：sp_helptext name 3）查看和存储过程相关的数据库对象 格式：sp_depends name,2019/7/24,20,【例题10.12】查看存储过程pro_return的定义文本。 EXEC sp_helptext pro_return,例题10.13】查看存储过程pro_return的所有者、类型及参数。 EXEC sp_help pro_return,【例题10.14】查看存储过程pro_return的相关性。 EXEC sp_depends pro_return,2019/7/24,21,3.删除存储过程,（1）使用对象资源管理器删除存储过程,（2）使用T-SQL语句删除存储过程,语法格式： DROP PROCEDURE procedure_name ,n,【例题10.15】删除存储过程pro_return。 DROP PROC pro_return,2019/7/24,22,10.2 触发器,10.2.1 触发器的概述,Microsoft SQL Server 2005提供了两种主要机制来强制执行业务规则和数据完整性：约束和触发器。,触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。具体表现为： 触发器可以通过数据库中的相关表进行级联更改。 触发器可以防止恶意或错误的UPDATE、 INSERT、 DELETE 、CREATE和ALTER操作，这些限制比用 CHECK 约束所定义的更复杂。 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。 一个表可以有多个同类触发器允许采取多个不同的操作来响应同一个修改语句。,2019/7/24,23,10.2.2 触发器的类型,1.DML触发器,DML触发器是当数据库服务器中发生数据操作语言事件时所执行的操作 .,DML触发器有三种类型：AFTER触发器、INSTEAD OF触发器和CLR触发器。,两大类触发器：DML触发器和DDL触发器。,2019/7/24,24,指定用触发器中的操作代替触发语句的操作，也就是该触发器并不执行所定义的操作（INSERT、 DELETE、 UPDATE），而是执行触发器本身的SQL语句。可以为基于一个或多个表的视图定义INSTEAD OF触发器，而这些触发器可能够扩展视图可支持的数据类型。,（2）INSTEAD OF触发器,表示只有在执行了指定的操作（INSERT、 DELETE、 UPDATE）之后触发器才被激活，执行触发器中的SQL 语句。若只指定FOR， 则默认为AFTER 触发器，且该类型触发器仅能在表上创建。,（1）AFTER触发器,2019/7/24,25,DDL触发器是一种特殊的触发器，当服务器或数据库中发生数据定义语言（DDL，CREATE、DROP、ALTER等）事件时将激活这些触发器。他们可以用于在数据库中执行管理任务，譬如，审核以及规范数据库操作。DDL触发器无法作为INSTEAD OF触发器使用。,2.DDL触发器,如果要执行以下操作，可以考虑使用DDL触发器。 防止他人对数据库架构进行修改。 希望数据库发生某种情况以响应数据库架构中的更改。 要记录数据库架构中的更改或事件。,2019/7/24,26,(1) 使用对象资源管理器建立DML触发器,10.2.3 DML触发器,1. 建立DML触发器,(2) 使用T-SQL语句建立DML触发器 语法格式： CREATE TRIGGER trigger_name ON table|view WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS sql_statement.n ,2019/7/24,27,【例题10.16】在student表中创建了一个触发器，当往该表中成功插入记录时，输出提示信息。然后执行插入记录操作，检验触发器。 -判断insert_stu触发器是否存在，若存在，则删除 IF EXISTS (SELECT name FROM sysobjects WHERE name = insert_stu AND type = TR) DROP TRIGGER insert_stu GO -建立触发器 CREATE TRIGGER insert_stu ON student FOR INSERT AS PRINT 插入记录成功 -插入记录，激活触发器 INSERT student(student_id,student_name,sex,class_id) VALUES(0801109,吴盼盼,男,08011),2019/7/24,28,（1）inserted表和deleted表,2DML触发器的应用,在执行触发器时，SQL Server 会为触发器建立两个临时表：deleted 表和 inserted，它们的结构和触发器所在的表的结构相同,Inserted表存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。 Deleted表存储DELETE和UPDATE语句所影响的行的复本。在执行DELETE或UPDATE语句时，记录从触发器表中删除，并传输到deleted表中。Deleted表和触发器表通常没有相同的行。,2019/7/24,29,注意一：执行 UPDATE 语句时，会被视为插入或删除事务，旧的行值会保留一份副本在deleted表中，而新的行值的副本则保留在触发器表与inserted表。 注意二：inserted和deleted表中的值只限于在触发器中使用。一旦触发器完成就无法再使用。,2019/7/24,30,【例题10.17】建立一个触发器，在修改数据时激活该触发器。然后执行修改操作，查看inserted和deleted表中的数据。 -建立触发器 CREATE TRIGGER update_stu ON student FOR insert, update,delete AS SELECT * FROM inserted SELECT * FROM deleted -修改数据，激活触发器 UPDATE student SET sex=男 WHERE student_id=0801101,2019/7/24,31,当往表中插入记录时，INSERT触发器被执行。一般情况下，INSERT触发器被用来更新时间标记字段，或者验证被触发器监控的字段中的数据是否满足要求，以确保数据完整性。,（2）INSERT触发器,2019/7/24,32,【例题10.18】建立一个触发器，当向成绩表中插入一条记录时，检查被插入的成绩是否大于等于0和小于等于100。若不满足该条件，不允许插入记录。 CREATE TRIGGER check_insert ON score AFTER insert AS DECLARE 成绩 int SELECT 成绩=grade FROM inserted IF 成绩=0 AND 成绩=100 PRINT 插入成功 ELSE BEGIN PRINT 成绩值超出范围,不允许插入 ROLLBACK TRANSACTION END -插入记录，检验insert触发器的作用 INSERT grade(student_id,course_id,grade) VALUES(0801107,1001,-10),2019/7/24,33,(3)UPDATE触发器 修改触发器和插入触发器的工作过程基本上一致，修改一条记录等于插入了一条新的记录并且删除一条旧的记录。,2019/7/24,34,【例题10.19】假设成绩信息被成功录入到表中以后将允许修改，建立一个触发器，禁止其修改成绩。 CREATE TRIGGER check_update ON score AFTER update AS IF update(grade) BEGIN PRINT 修改无效,成绩被录入后,不允许修改 ROLLBACK TRANSACTION END -修改成绩值，检查 update触发器的作用 UPDATE score set grade=80 WHERE student_id=0801101 AND course_id=1001,2019/7/24,35,(4)DELETE触发器 DELETE触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。 第二种情况是执行可删除主记录的子记录的级联删除操作。可以使用这样的触发器从主销售记录中删除所有的定单项。,2019/7/24,36,【例题10.20】在学生表中建立一个触发器，若删除学生记录，则把该学生在成绩表中的成绩记录一并删除。 CREATE TRIGGER check_delete ON student AFTER delete AS DELETE score WHERE student_id in (SELECT student_id FROM deleted) -删除学生记录，检查 delete触发器的作用 DELETE student WHERE student_id=0801103 -查看成绩表 grade中0801103号学生的成绩是否被删除 SELECT * FROM grade,2019/7/24,37,10.2.4 DDL触发器 1、建立DDL触发器 CREATE TRIGGER trigger_name ON ALL SERVER|DATABASE WITH ENCRYPTION FOR | AFTER event_type|event_group,n AS sql_statement.n ,2019/7/24,38,参数说明： ALL SERVER 指明触发器的作用域为当前服务器。如果指定了此参数，则只要当前服务器中的任何位置出现event_type或event_group，就会激活触发器。 DATABASE 指明触发器的作用域为当前数据库。如果指定了此参数，则只要当前数据库中的任何位置出现event_type或event_group，就会激活触发器。 event_type 将导致激活DDL触发器的T-SQL语言的名称。event_type选项有：CREATE_TABLE、CREATE_ DATABASE 、ALTER_TABLE等等。 event_group 预定义的T-SQL语言事件分组的名称。执行任何属于event_group的T-SQL语言事件，都将激活DDL触发器。 event_group有：DDL_SERVER_SECURITY_EVENTS代表所有以服务器为目标的各类DDL语法语法事件，而 DDL_TABLE_VIEW_EVENTS代表了针对数据表、视图表、索引与统计的DDL事件。,2019/7/24,39,【例题10.21】使用 DDL 触发器来防止数据库score中的表被任意修改或删除。 USE score GO CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS BEGIN PRINT 禁止修改或删除表 ROLLBACK END -修改表，检验safety触发器的功能 ALTER TABLE student ADD f_name char(8),2019/7/24,40,【例题10.22】建立 DDL 触发器来防止在当前服务器下建立数据库。 CREATE TRIGGER forbid_create ON ALL SERVER FOR CREATE_DATABASE AS BEGIN PRINT 禁止建立数据库 ROLLBACK END -建立数据库，检验forbid_create触发器的功能 CREATE DATABASE book,2019/7/24,41,10.2.5查看、修改和删除触发器,1. 查看触发器 （）使用对象资源管理器查看触发器信息 （）使用系统存储过程查看触发器,2019/7/24,42,（1）sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。 格式：sp_help 触发器名称 （2） sp_helptext：用于查看触发器的正文信息 格式：sp