存储过程和游标PPT演示文稿.ppt

《存储过程和游标PPT演示文稿.ppt》由会员分享，可在线阅读，更多相关《存储过程和游标PPT演示文稿.ppt（38页珍藏版）》请在三一文库上搜索。

1、7. 1存储过程概述,存储过程简介 SQL Server提供了一种方法：可以将一些预先编译的SQL语句集中起来由SQL Server数据库服务器来完成以实现某个任务，这就是存储过程。 存储过程常驻在SQL Server数据库服务器的内存中。,存储过程优点,存储过程分类,7.1.1存储过程的创建,使用Create procedure语句,CREATE PROC EDURE procedure_name parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPT

2、ION AS sql_statement,新存储过程的名称,过程中的参数,参数数据类型,参数的默认值,参数是返回参数,指定过程要执行的操作,过程中要包含的任意数目和类型的 Transact-SQL 语句,使用Create procedure语句,例1 创建存储过程，查询安徽表中所有内容，并按名字进行排序。 CREATE PROCEDURE exp1 as Select * From 安徽 Order by name,使用Create procedure语句,例2 创建存储过程，加入一条记录到安徽表中。 CREATE PROCEDURE exp2 ID int, AREA float, PERI

3、METER float, BNDRY_ID char(6), NAME char(8), DIQU char(8) as Insert into 安徽 Valus( ID, AREA, PERIMETER, BNDRY_ID, NAME, DIQU),存储过程参数的使用,例3 创建带参数的存储过程，完成输入某一个字，输出包含该字的记录。 CREATE PROCEDURE exp2 name varchar(8) As Select * From 安徽 Where NAME like %+ name+%,存储过程参数的使用,例4 创建带参数的存储过程，完成输入某行政区划代码，如果存在输出该县（市

4、）的信息，如果不存在，则显示“您输入错误！” CREATE PROCEDURE exp4 BNDRY_ID varchar(8) As Begin Declare bit_exist bit If exists(Select * From 安徽 Where BNDRY_ID = BNDRY_ID) Set bit_exist =1 else Set bit_exist =0 if bit_exist =1 Begin Select * From 安徽 Where BNDRY_ID = BNDRY_ID End Else Print 您输入错误！ end,存储过程参数的使用,例5 创建带参数的存

5、储过程，完成输入某行政区划代码前4位，统计该地区下辖县（市）有几个。 CREATE PROCEDURE exp5 BNDRY_ID varchar(8) As Select count(*) From 安徽 Where BNDRY_ID like BNDRY_ID+ %,加密存储过程,如果用户不想让其他人查看存储过程的定义文本，可以在定义存储过程的同时，对其进行加密，来保护代码的安全性，通过加密的存储过程可以正常使用，但是无法查看该存储过程的内容，在SQL Server Management Studio中也不能修改，只能通过ALTER语句来修改。 CREATE PROCEDURE exp6

6、With encryption as Select * From 安徽 Order by name Exec sp_helptext exp6,查看存储过程 源代码,存储过程的维护,存储过程修改,ALTER PROC EDURE procedure_name parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION AS sql_statement,存储过程修改,ALTER PROCEDURE exp6 as Select distinct DIQU

7、 From 安徽,存储过程删除,使用命令删除存储过程,DROP PROC EDURE procedure_name ,n ,DROP PROCEDURE exp6,7.2 分行处理程序游标,在数据库开发过程中，使用SELECT语句查询，得到一个结果集，对这个结果集中的不同数据行，可能要做不同的处理。 也就是要逐一处理每一个数据行。游标提供了一种比较好的解决方案。,7.2 分行处理程序游标,例 在图书表中查询出与数据库相关的图书，并对其中的部分书的价格进行调整。,7.2.2 使用游标,操作步骤: 1. 声明游标（变量）。 2. 打开游标。 3. 从一个游标中提取信息。 4. 关闭（释放）游标。,

8、1. 声明游标,语法格式： DECLARE SCROLL CURSOR FOR FOR READ ONLY | UPDATE OF ,.n,例7-15 声明只读游标。 declare cur_c cursor for select 书名,作者,定价 from 图书 for read only 例7-16 声明更新游标。 declare cur_c cursor for select 书名,作者,定价 from 图书 for update,1. 声明游标,2. 声明游标变量,语法格式： DECLARE CURSOR 建立游标变量与游标之间的关联。 方法1：先声明游标和游标变量，然后用SET语句将

9、游标赋给游标变量。 例：DECLARE c1 CURSOR FOR SELECT * FROM s DECLARE cur_var CURSOR SET cur_var = c1,3. 打开游标,游标声明后，如果要从游标中读取数据，必须打开游标。 语法格式： OPEN GLOBAL | 其中，GLOBAL表示要打开的是一个全局游标。,4. 读取游标中的数据,当游标被打开后，就可以从游标中逐行地读取数据。 语法格式： FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|nvar |RELATIVE n|nvar FROM GLOBAL | INTO ,.n,8.2.4.

10、3 读取游标的数据（P207）,Fatch next | prior | first | last absolute n | relative n from 游标名 into 变量名 Next-返回当前行的下一行记录 Prior-返回紧邻当前行前面的结果行 Last-返回最后一行记录 Absolute n-返回第n行记录，当n为负值则从最后一行算起。 Relative -游标当前位置起向后第n行，若n为负值则向前第n行。,游标的操作,Declare cur1 scroll cursor for Select * from 图书 Where 书名 like %数据库% Open cur1 Fet

11、ch first from cur1 Fetch last from cur1 Fetch prior from cur1 Fetch absolute 2 from cur1 Fetch relative 2 from cur1 Close cur1 Deallocate cur1,(1) 使用CLOSE命令关闭游标,处理完游标中数据后，必须关闭游标来释放数据结果集和定位于数据记录上的锁。 语法格式: CLOSE GLOBAL | CLOSE 语句可以关闭游标，但不释放游标的数据结构。如果要再次使用游标，可用OPEN命令重新打开。,6. 释放游标,用CLOSE命令关闭游标并没有释放游标占用的

12、数据结构。使用DEALLOCATE命令将释放游标占用的数据结构，游标使用的任何资源也随之释放。 语法格式： DEALLOCATE GLOBAL | 游标的关闭指释放游标的结果集所占用的资源，游标的释放指释放游标占用的所有资源，当然也包括结果集占用的资源。,7.2 分行处理程序游标,例 在图书表中查询出与数据库相关的图书，并对其中的部分书的价格进行调整。 Declare cur1 scroll cursor for select 书名,作者,定价 from 图书 where 书名 like %数据库% for update of 定价,7.2 分行处理程序游标,例 在图书表中查询出与数据库相关的

13、图书，并对其中的部分书的价格进行调整。 Declare cur1 scroll cursor for select 书名,作者,定价 from 图书 where 书名 like %数据库% for update of 定价 Open cur1,7.2 分行处理程序游标,例 在图书表中查询出与数据库相关的图书，并对其中的部分书的价格进行调整。 Declare cur1 scroll cursor for select 书名,作者,定价 from 图书 where 书名 like %数据库% for update of 定价 Open cur1 Fetch absolute 9 from cur1

14、,7.2 分行处理程序游标,例 在图书表中查询出与数据库相关的图书，并对其中的部分书的价格进行调整。 Declare cur1 scroll cursor for select 书名,作者,定价 from 图书 where 书名 like %数据库% Open cur1 Fetch absolute 9 from cur1 Update 图书 set 定价=30 where current of cur1,7.2.3 游标应用实例,1. 用于游标的UPDATE语句的语法格式： UPDATE SET = |NULL|,n WHERE CURRENT OF 2. 用于游标的DELETE语句的语法格

15、式： DELETE FROM WHERE CURRENT OF ,利用变量输出游标中的字段值,Declare S1 scroll cursor for Select 姓名,班级,综合测评 from 学生 Where 班级 like %地信% Declare sn int Declare sc varchar(10) Declare ss varchar(10) Open S1 Fetch absolute 4 from S into sn,sc,ss Select sn,sc,ss Close S1 Deallocate S1,利用游标修改数据库中的数据,Select 姓名,班级,综合测评 f

16、rom 学生 Declare S2 scroll cursor for Select 姓名,班级,综合测评 from 学生 Where 班级 like %地信% Open S2 Fetch first from S Update 学生 set 姓名=modifybycursor where current of S2 Close S2 Deallocate S2,实例11: 利用游标删除数据库中的数据,Select 姓名,班级,综合测评 from 学生 Declare S3 scroll cursor for Select 姓名,班级,综合测评 from 学生 Where 班级 like %地

17、信% Open S3 Fetch absolute 3 from S3 Delete from 职工 where current of S3 Close S3 Deallocate S3,使用fetch_status: 该全局变量/配置函数返回被最后FETCH语句执行的游标的状态，返回类型为int： 0：FETCH语句成功 1:FETCH语句失败或此行不在结果集中 2：被提取的行不存在,实例12: 利用游标判断记录是否存在,Declare S4 scroll cursor for Select 姓名,班级,综合测评 from 学生 Where 班级 like %地信% Open S4 If f

18、etch_status=0 Print 存在该记录 Else Print 不存在该记录 Close S4,实例13: 利用游标逐条显示数据库中的记录,Declare S5 scroll cursor for Select 姓名,班级,综合测评 from 学生 Where 班级 like %地信% Open S5 While fetch_status=0 Begin Fetch next from S5 End Close S5 Deallocate S5,例8-20 将地信班中2003级的综合测评成绩上浮5%。 DECLARE c CHAR(4), g CHAR(4), s double DECLARE S6 CURSOR FOR SELECT 班级, 年级,综合测评 FROM 学生 Where 班级 like %地信%,7.2.3 游标应用实例,OPEN S6 FETCH NEXT FROM S6 INTO c, g, s WHILE fetch_status = 0 BEGIN IF g like 2003 UPDATE 学生 SET 综合测评 =综合测评 * (1 + 0.05) WHERE CURRENT OF S6 FETCH NEXT FROM S6 INTO c, g, s END CLOSE S6 DEALLOCATE S6 GO,7.2.3 游标应用实例,