第10章-存储过程和触发器.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第10章 存储过程和触发器ppt课件（全）,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第10章 存储过程和触发器ppt课件（全）,*,第10章 存储过程、触发器,第10章 存储过程和触发器ppt课件（全）,存储过程、触发器和游标是SQL Server数据库的三个重要组成部分。SQL Server 2008使用它们从不同方面提高数据处理能力。,在SQL Server 2008中，可以像其他程序设计语言一样定义子程序，称为存储过程。存储过程是SQL Server 2008提供的最强大的工具之一。理解并运用它，可以创建健壮、安全且具有良好性能的数据库，可以为用户实现最复杂的商业事务。,触发器是一种特殊类型的存储过程：它通过事件触发而被自动执行。自动执行意味着更少的手工操作以及更小的出错机率。触发器用于强制复杂的完整性检查，审核更改，维护不规范的数据等等。SQL Server 2008允许DML语句和DDL语句创建触发器，可以引发AFTER或者INSTEAD OF触发事件。,游标主要用于实现一些不能使用面向集合的语句实现的操作。通过游标，SQL Server提供了一个对结果集进行逐行处理的能力。可以把游标看为一种特殊的指针，它可以指向结果集中的任意位置，在查询数据的同时对数据进行处理。,第10章 存储过程和触发器ppt课件（全）,本章学习目标：,了解存储过程、触发器和游标的基本概念与特点,掌握存储过程的基本类型和相关操作,掌握触发器的类型与相关操作,第10章 存储过程和触发器ppt课件（全）,10.1 存储过程,通过前面的学习，我们能够编写并运行T-SQL程序以完成各种不同的应用。保存T-SQL程序的方法有两种：一种是在本地保存程序的源文件，运行时先打开源文件再执行程序；另一种方法即将程序存储为存储过程，运行时调用存储过程执行。,因为存储过程是由一组T-SQL语句构成的，要使用存储过程，我们必需熟悉前面几章所讨论的基本的T-SQL语句，并且需要了解掌握一些关于函数、过程的概念。,第10章 存储过程和触发器ppt课件（全）,10.1.1 存储过程的基本概念,存储过程是事先编好的、存储在数据库中一组被编译了的T-SQL命令集合，这些命令用来完成对数据库的指定操作：存储过程可以接收用户的输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言(DDL)和数据操作语言(DML)语句，然后返回输出参数。,通过定义可以看到，存储过程起到了我们在其他语言中所说的子程序的作用，我们可以将经常执行的管理任务或者复杂的业务规则，预先用T-SQL语句写好并保存为存储过程,当需要数据库提供与该存储过程的功能相同的服务时，只需要使用EXECUTE命令，即可调用存储过程完成命令。,第10章 存储过程和触发器ppt课件（全）,储过程的优点：,1.减少网络流量：存储过程在数据库服务器端执行，只向客户端返回执行结果。因此可以将在网络中要发送的数百行代码，编写为一条存储过程，这样客户端只需要提交存储过程的名称和参数，即可实现相应功能，节省了网络流量，提高了执行的效率。此外，由于所有的操作都在服务器端完成，避免了在客户端和服务器端之间的多次往返。存储过程只需要将最终结果通过网络传输到客户端。,2.提高系统性能：一般T-SQL语句每执行一次就需要编译一次，而存储过程只在创建时进行编译，被编译后存放在数据库服务器的过程高速缓存中，当使用时，服务器不必再重新分析和编译它们。因此，当对数据库进行复杂操作时（如对多个表进行UPDATE、INSERT或DELETE操作时），可将这些复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用，节省了分析、解析和优化代码所需的CPU资源和时间。,3.安全性高：使用存储过程可以完成所有数据库操作，并且可授予没有直接执行存储过程中语句的权限的用户，也可执行该存储过程的权限。另外可以防止用户直接访问表，强制用户使用存储过程执行特定的任务。,4.可重用性：存储过程只需创建并存储在数据库中，以后即可任意在程序中调用该过程。存储过程可独立于程序源代码而单独修改，减少数据库开发人员的工作量。,5.可自动完成需要预先执行的任务：存储过程可以在系统启动时自动执行，完成一些需要预先执行的任务，而不必在系统启动后再进行人工操作。,第10章 存储过程和触发器ppt课件（全）,10.1.2 存储过程的类型,1.系统存储过程,2.扩展存储过程,3.用户存储过程,用户存储过程在用户数据库中创建，通常与数据库对象进行交互，用于完成特定数据库操作任务，可以接受和返回用户提供的参数，名称不能以sp_为前缀。,在SQL Server 2008中，用户存储过程有两种类型：Transact-SQL存储过程和存储过程。,Transact-SQL存储过程保存T-SQL语句的集合，可以接受和返回用户提供的参数，也可以从数据库向客户端应用程序返回数据CLR存储过程是指对Microsoft.NET Framework公共语言运行时方法的引用，可以接受和返回用户提供的参数。它们在.NET Framework程序集中是作为类的公共静态方法实现的,第10章 存储过程和触发器ppt课件（全）,10.1.3 用户存储过程的创建与执行,1.创建和执行用户存储过程实例,创建用户存储过程是通过编辑代码实现的。下面通过一个实例介绍创建用户存储过程的一般步骤。,【例10-1】创建名为snoquery,的存储过程：通过用户输入学生学号来查询学生的姓名、年龄、性别和所属院系。,（,1）启动SSMS，展开服务器。,（2）展开所需的“数据库”文件夹，展开要在其中创建存储过程的数据库。本例中，我们展开stuinfo数据库。,（3）展开“可编程性”文件夹，在“存储过程”文件夹上右击鼠标，在弹出的快捷菜单中选择“新建存储过程”项。,（4）系统弹出T-SQL语句编写窗口，其中的代码是创建存储过程的格式说明。我们输入以下T-SQL代码：,CREATE PROCEDURE snoquery,xuehao char(10),AS,SELECT sno 学号,sname 学生姓名,sage 年龄,ssex 性别,sdept 所属院系,FROM student,WHERE sno=xuehao,（5）代码输入结束后，只要将以上代码在“查询分析器”里执行一次，系统就会在当前数据库中创建一个名为snoquery的存储过程。点击刷新按钮，选择stuinfo数据库，在左边的树型列表中选择“存储过程”，就可以看到属于dbo（database owner）的存储过程dbo.snoquery。,第10章 存储过程和触发器ppt课件（全）,【例10-2】使用存储过程snoquery查询学号为“20070102”学生的信息。,T-SQL语句为：,EXECUTE snoquery 20070102,2.创建存储过程的T-SQL语句,基本语法格式为：,CREATE PROCEDURE-定义存储过程名称,参数名称 数据类型-定义参数及其数据类型,=defaultOUTPUT,n1 定义参数的属性,AS,SQL语句,n2-执行的操作,第10章 存储过程和触发器ppt课件（全）,3.运行存储过程的T-SQL语句,存储过程创建完成后，可以使用EXECUTE语句调用它。,基本语法格式如下：,EXECUTE存储过程名称,参数名称=value|variable OUTPUT|DEFAULT,n1,第10章 存储过程和触发器ppt课件（全）,10.1.4 存储过程的查看、修改和删除,展开所选数据库数据库“可编程性”文件夹“存储过程”文件夹，即可以看到数据库的系统存储过程和用户存储过程；系统存储过程方便用户管理数据库的有关对象。,sp_help：用于查看有关存储过程的名称列表。向用户报告有关数据库对象、用户定义数据类型或所提供的数据类型的摘要信息；,sp_helptext,：用于显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的过程定义代码。,我们可以利用下面的语句查看存储过程的信息：,EXECUTE sp_help 存储过程名称,用于查看存储过程的对象信息,EXECUTE sp_helptext 存储过程名称,用于查看存储过程的代码文本信息,第10章 存储过程和触发器ppt课件（全）,【例10-3】查看存储过程snoquery的对象信息和T-SQL代码。,查看对象信息的T-SQL语句为：,USE stuinfo,EXECUTE sp_help snoquery,待查看的存储过程必须在当前数据库中，因此，要使用USE stuinfo语句打开数据库。可以看到存储过程的相关信息及其中的参数信息。,查看代码信息的T-SQL语句为：,USE stuinfo,EXECUTE sp_helptext snoquery,可以看到存储过程snoquery的详细T-SQL代码。,第10章 存储过程和触发器ppt课件（全）,2.修改存储过程,基本语法格式：,ALTER PROCEDURE,参数名称 数据类型,=defaultOUTPUT,n1,AS,SQL语句,n2,各参数的操作与创建存储过程相同。,第10章 存储过程和触发器ppt课件（全）,【例10-4】修改存储过程snoquery：通过用户输入学生姓名来查询学生的姓名、年龄、性别和所属院系。修改完成后查询学生王小华的信息。,T-SQL语句为：,ALTER PROCEDURE snoquery,name nchar(10),AS,SELECT sno 学号,sname 学生姓名,sage 年龄,ssex 性别,sdept 所属院系,FROM student,WHERE sname=name,GO,EXECUTE snoquery N王小华,第10章 存储过程和触发器ppt课件（全）,3.删除存储过程,当不再使用存储过程时，可以在SSMS中选择对应的数据库和存储过程，单击“删除”按钮，也可以使用DROP PROCEDURE语句可以将其永久从数据库中删除。在删除之前，需要确认该存储过程没有任何函数依赖关系。,语法格式为：,DROP PROCEDURE ,n,【例10-5】删除存储过程snoquery,T-SQL语句为：,USE stuinfo,DROP PROCEDURE snoquery,第10章 存储过程和触发器ppt课件（全）,10.2 触发器,SQL Server 2008提供两种主要机制来强制使用业务规则和数据完整性：约束和触发器。,我们使用ALTER TABLE和CREATE TABLE语句声明字段的域完整性，使用PRIMARY KEY 和 FOREIGN KEY约束实现表之间的参照完整性。对于数据库中约束所不能保证的复杂的参照完整性和数据的一致性我们使用触发器来实现。,第10章 存储过程和触发器ppt课件（全）,10.2.1 触发器概述,1.触发器的功能,在SQL Server内部，触发器被看作是存储过程，它与存储过程所经历的处理过程类似。但是触发器没有输入参数和输出参数，因而不能被显示调用。它作为语句的执行结果自动引发，而存储过程则是通过存储过程名称而被直接调用。,触发器与表格紧密相连，当用户对表进行诸如UPDATE、INSERT和DELETE这些操作时，系统会自动执行触发器所定义的SQL语句，从而确保对数据的处理符合由这些SQL 语句所定义的规则。,除此之外，触发器还有其它许多不同的功能：,强化约束：触发器能够实现比CHECK,语句更为复杂的约束：,触发器可以很方便地引用其他表的列，去进行逻辑上的检查；,触发器是在,CHECK,之后执行的；,触发器可以插入，删除，更新多行。,跟踪变化：触发器可以侦测数据库内的操作从而禁止数据库中未经许可的更新和变化，确保输入表中的数据的有效性。例如在库存系统中，触发器可以检测到当实际库存下降到了需要再进货的临界量，就给出管理员相应提示信息或自动生成给供应商的订单；,级联运行：触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的不同表中的各项内容。如：设置一个触发器，当,student表中删除一个学号信息时，对应的sc表中相应的学号信息也被改写为NULL或删除相关学生记录；,调用存储过程：为了响应数据库更新，触发器可以调用一个或多个存储过程。,第10章 存储过程和触发器ppt课件（全）,2.触发器的种类,SQL Server 2008支持两种类型的触发器：DML触发器和DDL触发器。,DML触发器：如果用户要通过数据操作语言(DML)编辑数据，则执行 DML 触发器。DML 事件是针对表或视图的 INSERT、UPDATE和DELETE 语句，即DML触发器在数据修改时被执行。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务自动回滚；,DDL触发器为了响应各种数据定义语言(DDL)事件而激发。DDL事件主要与以关键字 CREATE、ALTER 和 DROP 开头的 T-SQL 语句对应。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作,第10章 存储过程和触发器ppt课件（全）,10.2.2 DML触发器的创建和应用,1.DML触发器的分类,触发器有很多用途，对于DML触发器来说，最常见的用途就是强制业务规则。例如，当客户下订单时，DML触发器可用于检查是否有充足的资金。如果检查完成，就可以完成进一步的操作，或者返回错误信息，对更新进行回滚。,在实际应用中，DML触发器分为两类：,（1）AFTER触发器：这类触发器是在记录已经被改变完，相关事务提交后，才会被触发执行。主要是用于记录变更后的处理或检查，一旦发现错误，可以用ROLLBACK TRANSACTION语句来回滚本次的操作。对同一个表达操作，可定义多个AFTER触发器，并定义各种触发器执行的先后顺序。,（2）INSTEAD OF触发器：这类触发器并不去执行其所定义的操作（INSERT、UPDATE、DELETE），而去执行触发器本身所定义的操作。这类触发器一般是用来取代原本的操作，在记录变更之前被触发的。,第10章 存储过程和触发器ppt课件（全）,2.触发器中的逻辑（虚拟）表,当表被修改，无论是插入、修改还是删除，在数据行中所操作确切的记录，都保存在两个系统的逻辑表中，这两个逻辑表是inserted（插入）表和deleted（删除）表。,这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。当触发器的工作完成之后，这两个表将会从内存中删除。,inserted表里存放的是更新前的记录：对于INSERT操作来说，INSERT触发器执行，新的记录插入到触发器表和inserted表中。很显然，只在进行INSERT和UPDATE触发器时，inserted表中有数据，而在DELETE触发器中inserted表是空的。,Deleted表里存放的是已从表中删除的记录：对于DELETE操作来说，DELETE触发器执行，被删除的旧记录存放到Deleted表中。,UPDATE操作等价于插入一条新记录，同时删除旧记录。对于UPDATE操作来说，UPDATE触发器执行，表中原记录被移动到Deleted表中（更新完后即被删除），修改过的记录插入到Inserted表中。,inserted和deleted表的结构与触发器所在数据表的结构是完全一致的。它们的操作和普通表的操作也一致。例如，若要检索 deleted 表中的所有值，则使用语句：,SELECT*FROM deleted,第10章 存储过程和触发器ppt课件（全）,3.创建DML触发器的语法规则,创建DML触发器的语法规则如下：,CREATE TRIGGER 触发器名称,ON table|view,-指定操作的对象为表或视图，视图只能被 INSTEAD OF 触发器引用,FOR|AFTER|INSTEAD OF-触发器的类型,INSERT ,UPDATE ,DELETE -指定数据修改操作，,AS,SQL语句,n,第10章 存储过程和触发器ppt课件（全）,【例10-6】创建触发器stu_delete，实现如下功能：当按照学号删除student表中的某学生记录后，对应的该学生在sc表中的记录也被自动删除。,T-SQL语句为：,USE stuinfo,GO,CREATE TRIGGER stu_Delete ON student,FOR DELETE,AS,DELETE FROM sc WHERE sno=(SELECT sno FROM deleted),执行后，我们查询student表和sc表，如图10-6所示，可以看到两个表中均存在学号为20070102的学生记录。,在student表中执行数据删除语句：,DELETE FROM student WHERE sno=20070102,student表中有一行受影响而sc表中有三行数据受影响。说明设定的触发器被触发，sc表中的相应数据被自动删除。,第10章 存储过程和触发器ppt课件（全）,通过以上AFTER触发器的例子我们可以验证只有在成功执行触发T-SQL 语句之后，才会激活AFTER触发器。判断执行成功的标准是：执行了所有与已更新对象或已删除对象相关联的引用级联操作和约束检查。,以删除表中记录为例，整个执行分为以下步骤：,（1）当系统接收到一个要执行student表删除操作的T-SQL语句时，系统将要删除的记录存放在删除表Deleted中；,（2）把数据表student中的相应记录删除；,（3）删除操作激活了事先编制的AFTER触发器，系统执行AFTER触发器中AS定义后的T-SQL语句；,（4）触发器执行完毕之后，删除内存中的Deleted表，退出整个操作。若干触发器语句执行失败，则整个过程回滚，恢复到初始状态第10章 存储过程和触发器ppt课件（全）,10.2.3 DDL触发器的创建和应用,DDL 触发器可用于回滚违反规则的结构更改、审核结构更改或以合适的形式响应结构更改。DDL 触发器同DML触发器一样，在响应事件时执行。,可以使用与DML触发器相似的 T-SQL 语法创建DDL 触发器，他们具有以下区别：,DML触发器响应INSERT、UPDATE和DELETE语句的操作，而DDL触发器响应CREATE、ALTER和DROP语句的操作；,只有在执行完T-SQL 语句后才会触发DDL触发器，即SQL Server仅支持AFTER类型的DDL触发器；,系统不会为DDL触发器创建Inserted表和Deleted表。,第10章 存储过程和触发器ppt课件（全）,1.创建DDL触发器的语法规则,CREATE TRIGGER 触发器名称,ON ALL SERVER|DATABASE,-指定触发器的作用域,FOR|AFTER,-触发器的类型,事件类型|事件组,n -指定数据修改操作,AS,SQL语句,n,第10章 存储过程和触发器ppt课件（全）,2.DDL触发器的应用,【例10-7】创建服务器范围的DDL触发器，当创建数据库是，系统返回提示信息：“DATABASE CREATED”。,T-SQL语句为：,CREATE TRIGGER trig_create,ON ALL SERVER,FOR CREATE_DATABASE,AS,PRINT DATABASE CREATED,运行创建触发器后，使用语句：,CREATE DATABASE demo,运行结果，消息栏内出现我们设定的“DATABASE CREATED”,第10章 存储过程和触发器ppt课件（全）,10.2.4 查看、修改和删除触发器,1.查看数据库中已有触发器,查看表中已有哪些触发器，这些触发器究竟对表有哪些操作，我们需要能够查看触发其信息。查看触发器有两种常用方法：,（1）使用SQL Server 2008的SSMS查看触发器信息,在SQL Server 2008中，展开服务器和数据库，此处我们选择展开stuinfo数据库。选择表student，展开触发器选项，即看到我们在例10-6中建立的触发器stu_delete。右击触发器stu_delete，从弹出的快捷菜单中选择修改，即可看到触发器的源代码。,第10章 存储过程和触发器ppt课件（全）,（2）使用系统存储过程查看触发器,由于触发器为一种特殊的存储过程，我们可以使用前面介绍的系统存储过程sp_help和sp_helptext来查看触发器信息。,sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间等。格式为：EXECUTE sp_help 触发器名称；,sp_helptext：用于查看触发器的T-SQL代码信息。格式为：EXECUTE sp_helptext,触发器名称,查看数据库中所有触发器信息要使用,sysobjects,表来辅助完成，语句为：,SELECT*FROM sysobjects WHERE xtype=TR,第10章 存储过程和触发器ppt课件（全）,2.修改数据库中已有触发器,修改触发器也可以在SQL Server 2008的SSMS中完成，步骤与查看触发器信息一致。,使用T-SQL语句修改触发器要区分是DML类触发器还是DDL类触发器，修改格式分别为：,（1）修改DML触发器,ALTER TRIGGER 触发器名称,ON table|view,FOR|AFTER|INSTEAD OF,INSERT ,UPDATE ,DELETE ,AS,SQL语句,n,（2）修改DDL触发器,ALTER TRIGGER 触发器名称,ON ALL SERVER|DATABASE,FOR|AFTER,事件类型|事件组,n,AS,SQL语句,n,第10章 存储过程和触发器ppt课件（全）,3.删除触发器：,系统提供三种方法来删除触发器：,（1）在SQL Server 2008的SSMS中完成，右击待删除的触发器，从弹出的快捷菜单中选择删除命令。,（2）删除触发器所在的表。在删除表时，系统会自动删除与该表相关的触发器。,（3）使用T-SQL语句DROP TRIGGER删除触发器。,基本语句格式为：,DROP TRIGGER 触发器名称,n,第10章 存储过程和触发器ppt课件（全）,