《SQL Server 2005 数据库应用技术 教学课件 ppt 作者 刘宏 第8章 使用存储过程和游标》由会员分享，可在线阅读，更多相关《SQL Server 2005 数据库应用技术 教学课件 ppt 作者 刘宏 第8章 使用存储过程和游标（29页珍藏版）》请在金锄头文库上搜索。
1、8.1 建立和执行存储过程,8.1.1 存储过程,存储过程是指封装了可以重用代码的模块或者例程。在应用程序开发中，开发人员通常将能够完成一定功能需要反复使用的代码序列通过过程或者函数的方式模块化，这种程序模块以一个整体的方式被反复调用，通过输入参数接收信息，输出参数返回结果和执行状态。存储过程与过程和函数相似，是用T-SQL语言写成的代码序列，主要在应用程序和数据库之间完成特定数据操作，提供数据交互接口。,8.1.2 使用存储过程的优点,使用存储过程的优点: 减少了网络通信量 加快了系统运行速度 提高了系统适应性 加强了系统安全性 增强了代码重用性,8.1.3 存储过程的分类,（1）系统存储过
2、程 系统存储过程是在安装SQL Server 2005时，系统创建的存储过程，以“sp_”为前缀，存储在master数据库中。 （2）扩展存储过程 扩展存储过程允许用户使用编程语言创建用户的外部例程，它们以动态链接库的形式存在，其前缀为“xp_”，扩展存储过程实现数据库实例在自己的地址空间上动态加载和运行动态链接库。,8.1.3 存储过程的分类,（3）用户自定义存储过程 由数据库用户为完成特定数据库操作功能编写的存储过程，存储在当前数据库中，可以由用户按照标识符的原则取名，通常冠以“pr_”前缀。,8.1.4 使用T-SQL创建存储过程,在SQL Server中可以使用CREATE PROCE
3、DURE语句创建存储过程，其基本语法格式如下： CREATE PROCEDURE 存储过程名 参数名 数据类型 = 默认值 OUT PUT ,.n WITH ENCRYPTION AS SQL语句,8.1.4 使用T-SQL创建存储过程,其中各个参数的含义如下： 存储过程名：新建存储过程的名称，必须遵循表示符规则，一般冠以“pr_”前缀。 参数名：存储过程的输入或者输出参数，必须以开头，且要遵循标识符规则。 默认值：为参数指定默认值，默认值允许使用通配符。 OUTPUT：指定参数类型为输出参数，否则为输入参数。 WITH ENCRYPTION：存储过程的定义以模糊方式存储，对存储过程加密。 S
4、QL语句：完成特定功能的SQL语句序列。,8.1.5 使用SSMS创建存储过程,在SQL Server Management Studio中创建存储过程的步骤演示。,8.1.6 执行存储过程,（1）使用EXECUTE语句执行存储过程 SQL Server 2005可以使用EXECUTE语句执行存储过程，基本语法格式如下： EXECUTE 存储过程名 参数1=值,n参数2 OUTPUT,n 其中各个参数的含义如下： 参数1：存储过程中定义的输入参数名称，参数名称可以省略。 参数2：用于接收来自存储过程的返回参数，使用前必须先定义。,8.1.6 执行存储过程,（2）使用SSMS执行存储过程 使用S
5、QL Server Management Studio执行存储过程的基本步骤演示。,8.2 管理和维护存储过程,8.2.1 修改存储过程,（1）用ALTER PROCEDURE 修改存储过程的语法格式如下： ALTER PROCEDURE 存储过程名 参数名 数据类型=默认值OUTPUT,n WITH ENCRIPTION AS SQL语句,8.2.1 修改存储过程,（2）通过删除和创建修改存储过程。 ALTER PROCEDURE实际上是重建了一个新的存储过程，因此可以通过删除存储过程再重新创建存储过程的方法修改存储过程。 （3）使用SSMS修改存储过程 在SQL Server Manage
6、ment Studio中修改存储过程步骤演示。,8.2.2 查看存储过程,查看存储过程主要包括查看存储过程的定义、存储过程的有关信息以及与存储过程有关的依赖关系。 （1）查看存储过程的定义 存储过程的定义存储在sys.sql_modules中，可以用SELECT语句查看，也可以用系统存储过程sp_helptext查看。 （2）查看存储过程的信息 存储过程的信息存储在sys.objects、sys.procedures 等系统表中，可以用SELECT语句查询，也可以用系统存储过程sp_help查看。,8.2.2 查看存储过程,（3）查看存储过程的依赖关系 存储过程的依赖关系存储在系统表sys.s
7、ql_dependencies中，通过SELECT语句可以查看，也可以用系统存储过程sp_depends 查看，sp_depends查看存储过程依赖的语法格式如下： EXECUTE sp_depends objname = 存储过程名称,8.2.3 删除存储过程,对于不再需要的存储过程可以使用DROP PROCEDURE语句将其删除，基本语法格式如下： DROP PROCEDURE 存储过程名,n,8.2.4 使用SSMS管理和维护存储过程,使用SQL Server Management Studio管理和维护存储过程演示。,8.3 使用游标,8.3.1 游标,游标实质是系统开设的一个存储缓冲
8、区，用于存放T-SQL语句执行的结果集，提供了一种可以让应用程序从包括多条数据记录的结果集中每次提取一条记录的机制，实现逐行控制数据的能力，它由结果集和定位标记构成。 通过游标，可以对结果集进行定位、检索和修改。在SQL Server 2005中，游标的生命周期包含声明游标、打开游标、使用游标、关闭游标和释放游标5个阶段。,8.3.2 使用游标的优点,允许程序对由查询语句SELECT返回的行集合中的每一行数据执行相同或不同的操作，而不是对整个行集合执行同一个操作。 提供对基于游标位置的表中的行进行更新和删除的能力。 游标作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使
9、这两种处理方式通过游标沟通起来。,8.3.3 声明游标,使用游标前首先要声明游标，声明游标时会为游标指定获取数据时所使用的SELECT语句。声明游标的语法格式如下： DECLARE 游标名称 INSENSITIVE SCROLL CURSOR FOR T-SQL语句 FOR READ ONLYUPDATE OF 列名,n 其中各个参数的意义如下： INSENSITIVE：定义游标时，为游标的数据创建一个临时表，所有的操作都在临时表中完成，不允许修改，不能及时体现基表的变化。 SCROLL：可以对行实现所有的定位操作，否则只能进行NEXT定位操作。 T-SQL语句：生成数据集的SQL语句。 RE
10、AD ONLY：表示只读游标。 UPDATE OF 列名,n：表示游标可以修改数据，“OF 列名”指定了可以修改的数据列。“,n”表示可以修改多列，每列之间用“，”分隔。,8.3.4 打开游标,在使用游标读取数据之前，必须打开游标。打开游标的语法格式如下： OPEN 游标名游标变量名,8.3.5 使用游标检索数据,创建并打开游标后，就可以从游标中获取数据。读取游标中某一行数据的语法格式如下： FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | nvar | RELATIVE n nvar FROM 游标名 | 游标变量名 INTO 变量名 ,.n
11、,8.3.5 使用游标检索数据,其中参数含义如下： NEXT | PRIOR | FIRST | LAST：表示下一行、前一行、第一行和最后一行 ABSOLUTE n | nvar ：如果n或者nvar的值为正数，表示从开始行向后的第n或者nvar行，如果为负数，表示结束行向前第n或者nvar行。 RELATIVE n | nvar：表示从当前行向前或者向后的n或者nvar行。 INTO 变量名：将提取的数据放入变量名称中。,8.3.6 关闭游标,在游标使用完毕之后，必须关闭游标。关闭游标并不改变它的定义，还可以使用OPEN命令再次打开游标。关闭游标的语法格式如下： CLOSE 游标名 | 游标变量名,8.3.7 释放游标,关闭游标的操作并不能释放游标所占用的系统资源，为了回收游标占用的资源应当释放游标，释放相关内存。释放游标的语法格式如下： DEALLOCATE 游标名游标变量名,8.3.8 使用游标修改数据,（1）更新数据 更新数据语法形式如下： UPDATE 表名|视图名 SET 列名 = 新列值 .n WHERE CURRENT OF 游标名 被更新的行依赖于游标位置的当前值。,8.3.8 使用游标修改数据,（2）删除数据 删除数据语法形式如下： DELETE 表名|视图名 WHERE CURRENT OF 游标名,
