《第11章存储过程、触发器和游标讲义》由会员分享，可在线阅读，更多相关《第11章存储过程、触发器和游标讲义（63页珍藏版）》请在金锄头文库上搜索。
1、数据库原理与应用教程 SQL Server,第11章存储过程、触发器和游标,目的要求 1理解存储过程的基本概念 2了解存储过程的分类 3掌握存储过程的基本操作 4理解触发器的基本概念 5了解触发器的分类 6掌握触发器的基本操作 7了解游标的概念、分类和基本操作,第11章存储过程、触发器和游标,在SQL Server 2005应用操作中，存储过程、触发器和游标都扮演着相当重要的角色。 存储过程可以使用户对数据库的管理工作变得更容易。存储过程是SQL语句和可选流程控制语句的预编译集合，它以一个名称存储并作为一个单元处理，能够提高系统的应用效率和执行速度。SQL Server提供了许多系统存储过程以
2、管理SQL Server和显示有关数据库和用户的信息。,第11章存储过程、触发器和游标,触发器是一种特殊类型的存储过程。当有操作影响到触发器保护的数据时，触发器就会自动触发执行。触发器是与表紧密联系在一起的，它在特定的表上定义，并与指定的数据修改事件相对应，它是一种功能强大的工具，它可以扩展SQL Server完整性约束默认值对象和规则的完整性检查逻辑，实施更为复杂的数据完整性约束。 游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。,第11章存储过程、触发器和游标,11.1存储过程 当开发一个应用程序时，为
3、了易于修改和扩充，经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句，即是“过程”(Procedure) 。SQL Server 2005的存储过程（Stored Procedure）包含一些T-SQL语句并以特定的名称存储在数据库中。可以在存储过程中声明变量、有条件地执行以及其他各项强大的程序设计功能。,第11章存储过程、触发器和游标,11.1.1 存储过程概述 存储过程是一种数据库对象，独立存储在数据库内。存储过程可以接受输入参数、输出参数，返回单个或多个结果集以及返回值，由应用程序通过调用执行。存储过程是SQL Server中
4、一个非常有用的工具。SQL Server支持存储过程和系统过程。存储过程是独立存在于表之外的数据对象。可以由客户调用，也可以从另一个过程或触发器调用，参数可以被传递和返回，出错代码也可以被检验。,第11章存储过程、触发器和游标,11.1.1 存储过程概述 在性能方面,存储过程有如下优点: (1)预编译:存储过程预先编译好放在数据库内，减少编译语句所花的时间。 (2)缓存:编译好的存储过程会进入缓存，所以对于经常执行的存储过程，除了第一次执行外，其他次执行的速度会有明显提高。 (3)减少网络传输:特别对于处理一些数据的存储过程，不必像直接用T-SQL语句实现那样多次传送数据到客户端。 (4)更好
5、的利用服务器内存:特别对于处理中间数据量不大的情况，存储过程中可以利用存放在内存的表变量。,第11章存储过程、触发器和游标,11.1.1 存储过程概述 一般来讲，应使用SQL Server中的存储过程而不使用存储在客户计算机本地的 T-SQL 程序，其优势主要表现在： (1)允许模块化程序设计。 (2)允许更快速地执行。 (3)减少网络流量。 (4)可作为安全机制使用。,第11章存储过程、触发器和游标,11.1.2存储过程的类型 1.系统存储过程 存储过程在运行时生成执行方式，其后在运行时执行速度很快。SQL Server 2005中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程
6、被称为系统存储过程。系统过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为数据库系统管理员管理SQL Server提供支持。通过系统存储过程，SQL Server中的许多管理性或信息性的活动（如获取数据库和数据库对象的信息）都可以被顺利有效地完成。,第11章存储过程、触发器和游标,11.1.2存储过程的类型 2本地存储过程 本地存储过程也就是用户自行创建并存储在用户数据库中的存储过程，一般所说的存储过程指的就是本地存储过程。 用户创建的存储过程是由用户创建并能完成某一特定功能(如查询用户所需的数据信息)的存储过程。,第11章存储过程、触发器和游
7、标,11.1.2存储过程的类型 3临时存储过程 临时存储过程可分为以下两种： (1)本地临时存储过程 不论哪一个数据库是当前数据库，如果在创建存储过程时，其名称以“#”号开头，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程。 (2)全局临时存储过程 不论哪一个数据库是当前数据库，只要所创建的存储过程名称是以两个“#”号开头，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程。,第11章存储过程、触发器和游标,11.1.3 创建存储过程 1在SQL Server Management Studio中创建存储过程 步骤如下： (1)打开SQL Server Ma
8、nagement Studio，展开要创建存储过程的数据库，展开“可编程性”选项，可以看到存储过程列表中系统自动为数据库创建的系统存储过程。右键单击“存储过程”选项，选“新建存储过程”命令。 (2)出现创建存储过程的T-SQL命令，编辑相关的命令即可。 (3)命令编辑成功后，进行语法检查，然后单击“确定”按钮，至此一个新的存储过程建立成功。,第11章存储过程、触发器和游标,11.1.3 创建存储过程 2利用T-SQL语句创建存储过程 CREATE PROCEDURE创建存储过程，语法格式如下： CREATE PROC | PROCEDURE procedure_name ; number pa
9、rameter data_type VARYING = default OUT PUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ,.n FOR REPLICATION AS sql_statement .n ,第11章存储过程、触发器和游标,11.1.3 创建存储过程 在创建存储过程时，应当注意以下几点。 (1)存储过程最大不能超过128MB。 (2)用户定义的存储过程只能在当前数据库中创建，但是临时存储过程通常是在tempdb数据库中创建的。 (3)在一条T-SQL语句中CREATE PROCEDURE不能与其他T-S
10、QL 句一起使用。 (4)SQL Server允许在存储过程创建时引用一个不存在的对象，在创建的时候，系统只检查创建存储过程的语法。,第11章存储过程、触发器和游标,11.1.3 创建存储过程 例11-1在教学库创建无参存储过程，查询每个同学各门功课的平均成绩。 USE 教学库 GO CREATE PROCEDURE student_avg AS SELECT 学生号, avg(成绩) as 平均分 FROM 选课 GROUP BY 学生号 GO,第11章存储过程、触发器和游标,11.1.3 创建存储过程 例11-2在教学库创建带参数的存储过程，查询某个同学的基本信息。 USE 教学库 GO
11、CREATE PROCEDURE GetStudent number char(7) AS SELECT * FROM 学生 WHERE 学生号= number,第11章存储过程、触发器和游标,11.1.3 创建存储过程 例11-3在教学库创建带有参数和默认值（通配符）的存储过程，从学生表中返回指定的学生（提供姓名）的信息。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则返回所有学生的信息。 USE 教学库 GO CREATE PROCEDURE Student_Name name varchar(40) = % AS SELECT * FROM 学生 WHERE 姓名 LIKE na
12、me GO,第11章存储过程、触发器和游标,11.1.3 创建存储过程 例11-4在仓库库存数据库创建带OUTPUT参数的存储过程，用于计算指定的商品的平均价格，存储过程中使用一个输入参数（商品名）和一个输出参数（平均价格）。 USE 仓库库存 GO CREATE PROCEDURE Pname p_n varchar(20), aveage int OUTPUT AS SELECT aveage= avg(单价) FROM 商品 WHERE 商品名称=p_n GO,第11章存储过程、触发器和游标,11.1.4执行存储过程 执行存储过程使用T-SQL 语中的EXECUTE命令。如果存储过程是批
13、处理中的第一条语句，那么不使用EXECUTE关键字也可以执行该存储过程。对于存储过程的所有者或任何一名对此过程拥有EXECUTE权限的用户，都可以执行此存储过程。如果需要在启动SQL Server时，系统自动执行存储过程，可以使用sp_procoption进行设置。如果被调用的存储过程需要参数输入时，在存储过程名后逐一给定，每一个参数用逗号隔开，不必使用括号。如果没有使用参数名=default这种方式传入值，则参数的排列必须和建立存储过程所定义的次序对应，用来接受输出值的参数则必须加上OUTPUT。,第11章存储过程、触发器和游标,11.1.4执行存储过程 EXECUTE语句的语法格式为： E
14、XECUTEUTE return_status= procedure_name;number parameter=value | parameter= variable OUTPUT WITH RECOMPILE 例11-5执行存储过程student_avg。 EXECUTE student_avg,第11章存储过程、触发器和游标,11.1.4执行存储过程 例11-6执行带参数的存储过程GetStudent，查询学号为01010001的学生的基本信息。 EXECUTE GetStudent 01010001 例11-7执行带有参数和默认值（通配符）的存储过程Student_Name。 (1)显
15、示所有学生的信息： EXECUTE Student_Name (2)显示“王小明”所有学生的信息： EXECUTE Student_Name 王小明 例11-8执行带有输入和输出参数的存储过程Pname。 Declare avgage int EXECUTE Pname 冰箱,avgage OUTPUT Print 冰箱的平均价格：+str(avgage),第11章存储过程、触发器和游标,11.1.5查看、修改和删除存储过程 1查看存储过程 可以执行系统存储过程sp_helptext，用于查看创建存储过程的命令语句；也可以执行系统存储过程sp_help，用于查看存储过程的名称、拥有者、类型、创
16、建时间，以及存储过程中所使用的参数信息。其语法格式分别为： sp_helptext 存储过程名称 sp_help 存储过程名称,第11章存储过程、触发器和游标,11.1.5查看、修改和删除存储过程 1查看存储过程 例11-9查看存储过程Pname的相关信息。 (1) sp_helptext Pname 执行结果如图所示。,(2) sp_help Pname 执行结果如图所示。,第11章存储过程、触发器和游标,11.1.5查看、修改和删除存储过程 2修改存储过程 修改存储过程可以在SQL Server Management Studio中鼠标右击要修改的存储过程，选择“修改”命令进行，与创建时的步骤基本相同；也可以通过T-SQL中的ALTER语句来完成。语法格式如下： ALTER PROC | P
