存储过程和游标.ppt

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

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

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

3、6),NAME char(8),DIQU char(8)asInsert into 安徽Valus(ID,AREA,PERIMETER,BNDRY_ID,NAME,DIQU),存储过程参数的使用,例3 创建带参数的存储过程，完成输入某一个字，输出包含该字的记录。CREATE PROCEDURE exp2name varchar(8)AsSelect*From 安徽Where NAME like%+name+%,存储过程参数的使用,例4 创建带参数的存储过程，完成输入某行政区划代码，如果存在输出该县（市）的信息，如果不存在，则显示“您输入错误！”CREATE PROCEDURE exp4BNDR

4、Y_ID varchar(8)AsBeginDeclare bit_exist bitIf 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 EndElsePrint 您输入错误！end,存储过程参数的使用,例5 创建带参数的存储过程，完成输入某行政区划代码前4位，统计该地区下辖县（市）有几个。CREATE PROCEDURE exp5BNDRY_ID varc

5、har(8)AsSelect count(*)From 安徽 Where BNDRY_ID like BNDRY_ID+%,加密存储过程,如果用户不想让其他人查看存储过程的定义文本，可以在定义存储过程的同时，对其进行加密，来保护代码的安全性，通过加密的存储过程可以正常使用，但是无法查看该存储过程的内容，在SQL Server Management Studio中也不能修改，只能通过ALTER语句来修改。CREATE PROCEDURE exp6With encryptionasSelect*From 安徽Order by nameExec sp_helptext exp6,查看存储过程源代码,

6、存储过程的维护,存储过程修改,ALTER PROC EDURE procedure_name parameter data_type VARYING=default OUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS sql_statement,存储过程修改,ALTER PROCEDURE exp6asSelect distinct DIQU From 安徽,存储过程删除,使用命令删除存储过程,DROP PROC EDURE procedure_name,n,DROP PROCEDURE exp6,7.2 分行处理程序游标,在

7、数据库开发过程中，使用SELECT语句查询，得到一个结果集，对这个结果集中的不同数据行，可能要做不同的处理。也就是要逐一处理每一个数据行。游标提供了一种比较好的解决方案。,7.2 分行处理程序游标,例 在图书表中查询出与数据库相关的图书，并对其中的部分书的价格进行调整。,7.2.2 使用游标,操作步骤:1.声明游标（变量）。2.打开游标。3.从一个游标中提取信息。4.关闭（释放）游标。,1.声明游标,语法格式：DECLARE SCROLL CURSORFOR FOR READ ONLY|UPDATE OF,.n,例7-15 声明只读游标。declare cur_c cursor for sel

8、ect 书名,作者,定价 from 图书 for read only例7-16 声明更新游标。declare cur_c cursor for select 书名,作者,定价 from 图书 for update,1.声明游标,2.声明游标变量,语法格式：DECLARE CURSOR建立游标变量与游标之间的关联。方法1：先声明游标和游标变量，然后用SET语句将游标赋给游标变量。例：DECLARE c1 CURSOR FOR SELECT*FROM s DECLARE cur_var CURSOR SET cur_var=c1,3.打开游标,游标声明后，如果要从游标中读取数据，必须打开游标。语法

9、格式：OPEN GLOBAL|其中，GLOBAL表示要打开的是一个全局游标。,4.读取游标中的数据,当游标被打开后，就可以从游标中逐行地读取数据。语法格式：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|nvar|RELATIVE n|nvar FROM GLOBAL|INTO,.n,8.2.4.3 读取游标的数据（P207）,Fatch next|prior|first|last absolute n|relative n from 游标名 into 变量名 Next-返回当前行的下一行记录Prior-返回紧邻当前行前面的结果行Last-返回最后一行记录Abso

10、lute n-返回第n行记录，当n为负值则从最后一行算起。Relative-游标当前位置起向后第n行，若n为负值则向前第n行。,游标的操作,Declare cur1 scroll cursor forSelect*from 图书Where 书名 like%数据库%Open cur1Fetch first from cur1Fetch last from cur1Fetch prior from cur1Fetch absolute 2 from cur1Fetch relative 2 from cur1Close cur1Deallocate cur1,(1)使用CLOSE命令关闭游标,处理

11、完游标中数据后，必须关闭游标来释放数据结果集和定位于数据记录上的锁。语法格式:CLOSE GLOBAL|CLOSE 语句可以关闭游标，但不释放游标的数据结构。如果要再次使用游标，可用OPEN命令重新打开。,6.释放游标,用CLOSE命令关闭游标并没有释放游标占用的数据结构。使用DEALLOCATE命令将释放游标占用的数据结构，游标使用的任何资源也随之释放。语法格式：DEALLOCATE GLOBAL|游标的关闭指释放游标的结果集所占用的资源，游标的释放指释放游标占用的所有资源，当然也包括结果集占用的资源。,7.2 分行处理程序游标,例 在图书表中查询出与数据库相关的图书，并对其中的部分书的价格

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

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

14、of cur1,7.2.3 游标应用实例,1.用于游标的UPDATE语句的语法格式：UPDATE SET=|NULL|,n WHERE CURRENT OF 2.用于游标的DELETE语句的语法格式：DELETE FROM WHERE CURRENT OF,利用变量输出游标中的字段值,Declare S1 scroll cursor forSelect 姓名,班级,综合测评 from 学生Where 班级 like%地信%Declare sn intDeclare sc varchar(10)Declare ss varchar(10)Open S1Fetch absolute 4 from

15、S into sn,sc,ssSelect sn,sc,ssClose S1Deallocate S1,利用游标修改数据库中的数据,Select 姓名,班级,综合测评 from 学生Declare S2 scroll cursor forSelect 姓名,班级,综合测评 from 学生Where 班级 like%地信%Open S2Fetch first from SUpdate 学生 set 姓名=modifybycursor where current of S2Close S2Deallocate S2,实例11:利用游标删除数据库中的数据,Select 姓名,班级,综合测评 from

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

17、forSelect 姓名,班级,综合测评 from 学生Where 班级 like%地信%Open S4 If fetch_status=0Print 存在该记录ElsePrint 不存在该记录Close S4,实例13:利用游标逐条显示数据库中的记录,Declare S5 scroll cursor forSelect 姓名,班级,综合测评 from 学生Where 班级 like%地信%Open S5While fetch_status=0BeginFetch next from S5EndClose S5Deallocate S5,例8-20 将地信班中2003级的综合测评成绩上浮5%。

18、DECLARE c CHAR(4),g CHAR(4),s doubleDECLARE S6 CURSORFORSELECT 班级,年级,综合测评FROM 学生Where 班级 like%地信%,7.2.3 游标应用实例,OPEN S6FETCH NEXT FROM S6 INTO c,g,sWHILE fetch_status=0BEGINIF g like 2003UPDATE 学生SET 综合测评=综合测评*(1+0.05)WHERE CURRENT OF S6FETCH NEXT FROM S6 INTO c,g,sENDCLOSE S6DEALLOCATE S6GO,7.2.3 游标应用实例,