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