SQL存储过程与触发器.ppt

存储过程与触发器,存储过程,一、存储过程的特点 在大型数据库应用中，随着功能的不断完善，整个系统也越来越复杂。大量的情况是许多SQL语句代码被许多次反复使用，如果每一次使用都输入相同的代码，效率会很低。数据库管理系统提供了一种方法，可以把一组常用的SQL语句集中起来，编译成可执行代码，存储在数据库服务器上，这样可以方便完成特定任务，这种方法就是存储过程。,存储过程是SQL语句和流程控制语句的集合，以一个名字保存，并作为一个单元来处理。存储过程是数据库中的一个独立的对象，保存在数据库中，可以由应用程序来调用执行，大大简化应用程序的开发，因此是SQL服务器端开发的主要手段之一。 如果不使用存储过程，那么当客户端输入SQL语句之后，它会发送到数据库服务器，由服务器进行下列操作： 语法分析：检查所输入的SQL语句，例如关键字拼写是否正确。,语义检查：检查SQL语句中所涉及的表、列是否存在。 优化分析：选择如何把数据从磁盘调入内存快速获取数据行的方法。 可执行代码：一组由数据库服务器解释执行的代码。 当同样的SQL再次执行时，则需要重新进行前面4个步骤。这样的操作浪费了时间和空间、降低了数据库服务器的效率。如果使用了存储过程，当再次执行前面的SQL语句时，不再需要执行语法分析、语义检查和优化分析，直接调用以前生成好,的可执行代码，显然这样的执行效率要高。 存储过程具有以下特点： 具有立即访问数据库的能力。 它是数据库服务器端的执行代码，在服务器端执行操作，减少网络通讯，提高执行效率。 保证数据库安全，自动完成预先定义的作业。 存储过程分为系统过程和用户定义的存储过程。系统存储过程是一组特殊的存储过程，它们在安装SQL时自动建立，存储在,Master中。所有系统存储过程名称都以SP_开始的。例如前面介绍过的SP_helpdb（显示所有数据库的信息）。系统存储过程为系统管理员提供管理SQL的支持，并且通过系统存储过程能够得到系统信息或完成管理工作。 用户存储过程是指创建在每个用户自己数据库中的存储过程。这种存储过程的名字由用户命名，且名称前面没有前缀SP_。 例1：在企业管理器中查看常用的系统存储过程。,具体操作如下： 打开企业管理器，打开数据库，master,存储过程。 例2：通过存储过程来查询当前数据库的信息。 SP_Helpdb 或 EXEC SP_Helpdb 二、使用创建存储过程向导创建存储过程 例如：创建对于“课程表”进行插入操作的存储过程。,具体操作如下： 在企业管理器中单击工具栏中运行向导，在弹出的选择向导对话框中展开“数据库创建存储过程向导”，弹出欢迎使用创建存储过程向导对话框。 以下操作看演示。 那么如何执行创建完的存储过程呢？在查询分析器中输入下列内容： 插入课程表 c009,ejb实用技术，孙高 然后执行即可。为了验证这个存储过程的作用，在查询分析器中执行下列SQL语句。,SELECT * FROM 课程表 WHERE 课程号=C009 三、使用SQL语句创建存储过程 语法格式： CREATE PROC (,） AS |语句块,其中： 是存储过程的输入输出参数。 说明参数类型。 或是定义存储过程内容的语句。 例如：建立判断男生与女生的平均奖学金高低的存储过程。 如果学生基本表中信息男生奖学金的平均值高于女生奖学金的平均值，则显示“男生的奖学金平均值高于女生的奖学金平均值”，,否则显示“男生的奖学金平均值不高于女生的奖学金平均值”，创建存储过程的SQL语句如下： CREATE PROC 按照性别判断平均奖学金高低 AS IF （SELECT AVG（奖学金） FROM 学生基本表 WHERE 性别=男） （SELECT AVG（奖学金）,FROM 学生基本表 WHERE 性别=女） PRINT 男生的奖学金平均值高于女生的奖学金平均值 ELSE PRINT 男生的奖学金平均值不高于女生的奖学金平均值 这样存储过程就创建完毕。 执行存储过程有两种方式： 存储过程名 EXEC 存储过程名,例如：显示男生奖学金平均值、女生奖学金平均值并且给出判断信息。 CREATE PROC 按照性别判断平均奖学金高低1 AS BEGIN DECLARE X NUMERIC(8,2),Y UNMERIC(8,2) SET X=(SELECT AVG（奖学金） FROM 学生基本表 WHERE 性别=男） SET Y=(SELECT AVG（奖学金） FROM 学生基本表 WHERE 性别=女）,PRINT X PRINT Y IF (XY) PRINT 男生的奖学金平均值高于女生的奖学金平均值 ELSE PRINT 男生的奖学金平均值不高于女生的奖学金平均值 END,四、管理存储过程 使用SQL语句可以更为有效地管理存储过程。 例如：建立一个查询指定学号奖学金信息的存储过程“查询奖学金”，输入参数为学号，如果没有输入学号，则输出提示信息。 建立这个存储过程的SQL 语句如下： CREATE PROC 查询奖学金 xh char(4)=null AS IF XH IS NULL BEGIN,PRINT 请输入要查询奖学金的学号 RETURN END ELSE BEGIN SELECT 学号，姓名，奖学金 FROM 学生基本表 WHERE 学号=xh END 这里要注意的是，这个存储过程有一个输入,参数XH，使用XH表示，同时定义了数据类型为CHAR（4），这与学生基本表中学号列的定义一样的。当定义了输入参数之后，可以在存储过程体中采用XH来使用。 执行存储过程： 查询奖学金 x101 或者 查询奖学金 请输入要查询奖学金的学号 X101,修改存储过程，其语法格式如下： ALTER PROC ( AS SQL语句|语句块 例如：修改存储过程“查询奖学金”，要求增加输出性别信息。,ALTER PROC 查询奖学金 xh char(4)=null AS IF XH IS NULL BEGIN PRINT 请输入要查询奖学金的学号 RETURN END ELSE BEGIN SELECT 学号，姓名，性别，奖学金 FROM 学生基本表 WHERE 学号=xh END,删除存储过程： DROP PROCEDURE 例如：删除存储过程“查询奖学金” DROP PROCEDURE 查询奖学金 存储过程的综合练习： DROP PROC 查询奖学金 GO USE 学生信息库 GO CREATE PROC 查询奖学金 xh char(4)=null AS IF XH IS NULL BEGIN,PRINT 请输入要查询奖学金的学号 RETURN END ELSE BEGIN SELECT 学号，姓名，奖学金 FROM 学生基本表 WHERE 学号=xh END GO,EXEC 查询奖学金 X101 GO 该实例是在学生信息库中给出学生的学号，查出奖学金，在该批处理中，包括了删除存储过程、建立存储过程、执行存储过程的SQL语句。 此外，SQL还提供了查看存储过程的系统存储过程。 SP_help :输出指定存储过程的参数和数据类型。,SP_helptext ：输出定义指定存储过程内容的源代码。 SP_depends :输出与指定存储过程相关的数据库对象。 例如：查看存储过程“查询奖学金”的源代码。 SP_HELPTEXT 查询奖学金 希望同学们对以上存储过程有一个全面的理解和认识。,触发器,一、触发器的概念 触发器是一种特殊类型的存储过程，这是因为触发器也包含了一组SQL语句。但是触发器又与存储过程明显不同，例如触发器可以自动执行。 当有操作影响到触发器保护的数据时，触发器就自动触发执行。因此触发器是在特定表上进行定义的，该表也称为触发器表。对有操作针对触发器表时，例如在表中插入、删除、修改数据时，如果该表有相应操作类型的触发器，那么触发器就,自动触发执行。 在SQL中，有三种类型的触发器，即INSERT类型、UPDATE类型和DELETE类型。当向某一个表插入数据时，若该表有INSERT类型的触发器，那么该触发器就触发执行；当向一个表进行修改操作时，若该表有UPDATE类型触发器，那么该触发器就触发执行；当删除一个表的数据时，若该表有DELETE类型的触发器，那么同样该触发器触发执行。,触发器与存储过程相比，触发器有其特殊性，这主要体现在以下几个方面： 自动执行：当对指定数据进行修改时，触发器自动执行，不需要按照名称手动执行。 连续执行：由于触发器自动执行的特性，有些时候会出现触发器的瀑布触发，在表T1上定义了触发器S1（这里有对表T2的DML操作），在表Tn上定义了触发器Sn，对表T1的一个操作，会引起T1至Tn共n张表的操作。 强制限制：当对数据库对象施加指定操作时会自动触发指定的动作，利用这样的机制可以强制限制某些操作，实际上，前面介绍的表的约束条件都是通过触发器来实现的。,二、触发器的工作原理 参照完整性触发器保持外键的值与主键的值一致。当数据操作影响一列时，触发器用称为触发器检查表的临时工作表来比较新的列值与相关的键。编写触发器时，比较的数据存放在临时的触发器检查表中。触发器检查表为INSERTED和DELETED，其中INSERTSD保存的是插入的数，DELETED保存的是删除的数据。 触发器检查表INSERTED和DELETED是用来检查一些数据操作的印象和触发器的动作设置条件。它不能直接地改变触发器检查表中的数据，但是能够使用SELECT语句来检测INSERT、DELETE和UPDATE等操作的影响：,在INSERT语句执行期间，新行被添加到触发器表的同时，也被添加到INSERTED表中； 在DELETED语句执行期间，从触发器表中被删除的行转移到了DELETED表中，也就是说DELETED表此时存放的是从触发器表中删除的行； UPDATE操作实际上是两个动作的连续，第一个动作是删除触发器表中的旧数据，这时的旧数据转移到DELETED表中；第二个动作是插入新数据，在插入到触发器表的同时，也插入到INSERTED表中。,三、触发器的创建 CREATE TRIGGER ON | FOR INSERT|UPDATE|DELETE AS SQL语句|语句块 其中： 是要新建的触发器名称. |是想要建立触发器,的数据库对象名称. |是定义触发动作的代码. 例1:创建INSERT类型的触发器. 假设建立一个按照院系名称统计学生奖学金总额的表, 表的名称为”按照学院统计奖学金总额表”,创建表的SQL语句如下: CREATE TABLE 按照学院统计奖学金总额表 (院系名称 CHAR(20) NOT NULL, 奖学金总额 MONEY,CONSTRAINT PK_奖学金总额表 PRIMARY KEY NONCLUSTERED(院系名称) ) 由于目前学生基本表中只有三个学院的学生,所以使用下列SQL语句对”按照学院统计奖学金总额表”进行初始化: INSERT INTO 按照学院统计奖学金总额表 VALUES(信息学院,0) INSERT INTO 按照学院统计奖学金总额表 VALUES(商学院,0),INSERT INTO 按照学院统计奖学金总额表 VALUES(法学院,0) 下面要在学生基本表中建立一个INSERT类型的触发器,每当往学生基本表中插入一行数据时,要自动更新按照学院统计奖学金总额表中对应院系的奖学金总额列值. 创建触发器的SQL语句如下: CREATE TRIGGER 奖学金总额触发器 ON 学生基本表 FOR INSERT AS,UPDATE 按照学院统计奖学金总额表 SET 奖学金总额=奖学金总额+奖学金 FROM INSERTED -触发器检查表 WHERE 按照学院统计奖学金总额表.院系名称=INSERTED.院系名称 在查看这个触发器作用之前,首先查看按照学院统计奖学金总额表中数据行,使用下列SQL语句: SELECT * FROM 按照学院统计奖学金总额表 下面开始往学生基本表中插入三行数据.,INSERT INTO 学生基本表(学号,姓名,性别,出生日期,院系名称,奖学金) VALUES(T001,张小小,男,1988-10-2,信息学院,1500) INSERT INTO 学生基本表(学号,姓名,性别,出生日期,院系名称,奖学金) VALUES(T002,李小小,男,1988-11-3,法学院,3000) INSERT INTO 学生基本表(学号,姓名,性别,出生日期,院系名称,奖学金) VALUES(T003,汪小小,男,1989-2-23,信息学院,1000),这时重新查询按照学院统计奖学金总额表中数据. 就可看到结果了. 这说明所创建的触发器在起作用. 例2: 创建UPDATE类型的触发器 下面在按照学院统计奖学金总额表中建立一个UPDATE类型的触发器,要求每当”奖学金总额”列被修改时,显示修改前后的信息. 假设这个触发器的名称为”奖学金总额修改触发器”, 则创建它的SQL语句如下:,CREATE TRIGGER 奖学金总额修改触发器 ON 按照学院统计奖学金总额表 FOR UPDATE AS DECLARE OLD MONEY,NEW MONEY SELECT OLD=奖学金总额 FROM DELETED SELECT NEW=奖学金总额 FROM INSERTED PRINNT 修改之前 总额是:,PRINT CONVERT(VARCHTR(10),OLD) PRINT 修改之后 总