存储过程和游标.ppt
17.1存储过程概述存储过程概述 存储过程简介存储过程简介 SQL Server提供了一种方法：可以将一些预先编译提供了一种方法：可以将一些预先编译的的SQL语句集中起来由语句集中起来由SQL Server数据库服务器来完数据库服务器来完成以实现某个任务，这就是存储过程。成以实现某个任务，这就是存储过程。存储过程常驻在存储过程常驻在SQL Server数据库服务器的内存中。数据库服务器的内存中。2存储过程优点存储过程优点3存储过程分类存储过程分类4存储过程的创建存储过程的创建使用使用Create procedure语句语句 CREATE PROC EDURE procedure_name parameter data_type VARYING =default OUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS sql_statement新存储过程的名称过程中的参数参数数据类型参数的默认值参数是返回参数指定过程要执行的操作过程中要包含的任意数目和类型的 Transact-SQL 语句5使用使用使用使用Create procedureCreate procedure语句语句语句语句例1 创建存储过程，查询安徽表中所有内容，并按名字进行排序。CREATE PROCEDURE exp1asSelect*From 安徽Order by name6使用使用使用使用Create procedureCreate procedure语句语句语句语句例2 创建存储过程，加入一条记录到安徽表中。CREATE PROCEDURE exp2ID int,AREA float,PERIMETER float,BNDRY_ID char(6),NAME char(8),DIQU char(8)asInsert into 安徽Valus(ID,AREA,PERIMETER,BNDRY_ID,NAME,DIQU)7存储过程参数的使用存储过程参数的使用存储过程参数的使用存储过程参数的使用例3 创建带参数的存储过程，完成输入某一个字，输出包含该字的记录。CREATE PROCEDURE exp2name varchar(8)AsSelect*From 安徽Where NAME like%+name+%8存储过程参数的使用存储过程参数的使用存储过程参数的使用存储过程参数的使用例4 创建带参数的存储过程，完成输入某行政区划代码，如果存在输出该县（市）的信息，如果不存在，则显示“您输入错误！”CREATE PROCEDURE exp4BNDRY_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 您输入错误！end9存储过程参数的使用存储过程参数的使用存储过程参数的使用存储过程参数的使用例5 创建带参数的存储过程，完成输入某行政区划代码前4位，统计该地区下辖县（市）有几个。CREATE PROCEDURE exp5BNDRY_ID varchar(8)AsSelect count(*)From 安徽 Where BNDRY_ID like BNDRY_ID+%10加密存储过程加密存储过程加密存储过程加密存储过程如果用户不想让其他人查看存储过程的定义文本，可以如果用户不想让其他人查看存储过程的定义文本，可以在定义存储过程的同时，对其进行加密，来保护代码的在定义存储过程的同时，对其进行加密，来保护代码的安全性，通过加密的存储过程可以正常使用，但是无法安全性，通过加密的存储过程可以正常使用，但是无法查看该存储过程的内容，在查看该存储过程的内容，在SQL Server Management Studio中也不能修改，只能通过中也不能修改，只能通过ALTER语句来修改。语句来修改。CREATE PROCEDURE exp6With encryptionasSelect*From 安徽Order by nameExec sp_helptext exp6查看存看存储过程程源代源代码11存储过程的维护存储过程的维护存储过程的维护存储过程的维护存储过程修改存储过程修改 ALTER PROC EDURE procedure_name parameter data_type VARYING =default OUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS sql_statement12存储过程修改存储过程修改存储过程修改存储过程修改ALTER PROCEDURE exp6asSelect distinct DIQU From 安徽安徽13存储过程删除存储过程删除存储过程删除存储过程删除使用命令删除存储过程使用命令删除存储过程 DROP PROC EDURE procedure_name ,n DROP PROCEDURE exp6147.2 分行处理程序分行处理程序游标游标 在在数数据据库库开开发发过过程程中中，使使用用SELECT语语句句查查询询，得得到到一一个个结结果果集集，对对这这个个结结果果集集中中的的不不同同数数据据行行，可可能能要要做做不不同的处理。同的处理。也也就就是是要要逐逐一一处处理理每每一一个个数数据据行行。游游标标提提供供了了一一种种比比较好的解决方案。较好的解决方案。157.2 分行处理程序分行处理程序游标游标例例 在在图图书书表表中中查查询询出出与与数数据据库库相相关关的的图图书书，并并对对其其中中的的部部分分书的价格进行调整。书的价格进行调整。167.2.2 使用游标使用游标操作步骤操作步骤:1.声明游标（变量）。声明游标（变量）。2.打开游标。打开游标。3.从一个游标中提取信息。从一个游标中提取信息。4.关闭（释放）游标。关闭（释放）游标。171.声明游标声明游标语法格式：语法格式：DECLARE SCROLL CURSORFOR FOR READ ONLY|UPDATE OF ,.n 18例例7-15 声明只读游标。声明只读游标。declare cur_c cursor for select 书名书名,作者作者,定价定价 from 图书图书 for read only例例7-16 声明更新游标。声明更新游标。declare cur_c cursor for select 书名书名,作者作者,定价定价 from 图书图书 for update 1.声明游标声明游标192.声明游标变量声明游标变量语法格式：语法格式：DECLARE CURSOR建立游标变量与游标之间的关联。建立游标变量与游标之间的关联。方方法法1：先先声声明明游游标标和和游游标标变变量量，然然后后用用SET语语句句将将游游标标赋赋给给游标变量。游标变量。例：例：DECLARE c1 CURSOR FOR SELECT*FROM s DECLARE cur_var CURSOR SET cur_var=c1 203.打开游标打开游标 游标声明后，如果要从游标中读取数据，必须打开游标。游标声明后，如果要从游标中读取数据，必须打开游标。语法格式：语法格式：OPEN GLOBAL|其中，其中，GLOBAL表示要打开的是一个全局游标。表示要打开的是一个全局游标。214.读取游标中的数据读取游标中的数据 当游标被打开后，就可以从游标中逐行地读取数据。当游标被打开后，就可以从游标中逐行地读取数据。语法格式：语法格式：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|nvar|RELATIVE n|nvar FROM GLOBAL|INTO,.n 228.2.4.3 读取游标的数据（P207）Fatch next|prior|first|last absolute n Fatch next|prior|first|last absolute n|relative n from|relative n from 游标名游标名 into into 变量名变量名 Next-Next-返回当前行的下一行记录返回当前行的下一行记录Prior-Prior-返回紧邻当前行前面的结果行返回紧邻当前行前面的结果行Last-Last-返回最后一行记录返回最后一行记录Absolute n-Absolute n-返回第返回第n n行记录，当行记录，当n n为负值则从最后一为负值则从最后一行算起。行算起。Relative-Relative-游标当前位置起向后第游标当前位置起向后第n n行，若行，若n n为负值则为负值则向前第向前第n n行。行。23游标的操作游标的操作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 cur124(1)使用使用CLOSE命令关闭游标命令关闭游标 处处理理完完游游标标中中数数据据后后，必必须须关关闭闭游游标标来来释释放放数数据据结结果果集集和定位于数据记录上的锁。和定位于数据记录上的锁。语法格式语法格式:CLOSE GLOBAL|CLOSE 语语句句可可以以关关闭闭游游标标，但但不不释释放放游游标标的的数数据据结结构构。如果要再次使用游标，可用如果要再次使用游标，可用OPEN命令重新打开。命令重新打开。256.释放游标释放游标 用用CLOSE命命令令关关闭闭游游标标并并没没有有释释放放游游标标占占用用的的数数据据结结构构。使使用用DEALLOCATE命命令令将将释释放放游游标标占占用用的的数数据据结结构构，游游标标使用的任何资源也随之释放。使用的任何资源也随之释放。语法格式：语法格式：DEALLOCATE GLOBAL|游游标标的的关关闭闭指指释释放放游游标标的的结结果果集集所所占占用用的的资资源源，游游标标的的释释放放指指释释放放游游标标占占用用的的所所有有资资源源，当当然然也也包包括括结结果果集集占占用用的的资资源。源。267.2 分行处理程序分行处理程序游标游标例例 在在图图书书表表中中查查询询出出与与数数据据库库相相关关的的图图书书，并并对对其其中中的的部部分书的价格进行调整。分书的价格进行调整。Declare cur1 scroll cursor forselect 书名书名,作者作者,定价定价 from 图书图书where 书名书名 like%数据库数据库%for update of 定价定价277.2 分行处理程序分行处理程序游标游标例例 在在图图书书表表中中查查询询出出与与数数据据库库相相关关的的图图书书，并并对对其其中中的的部部分书的价格进行调整。分书的价格进行调整。Declare cur1 scroll cursor forselect 书名书名,作者作者,定价定价 from 图书图书where 书名书名 like%数据库数据库%for update of 定价定价Open cur1287.2 分行处理程序分行处理程序游标游标例例 在图书表中查询出与数据库相关的图书，并对其中的部在图书表中查询出与数据库相关的图书，并对其中的部分书的价格进行调整。分书的价格进行调整。Declare cur1 scroll cursor forselect 书名书名,作者作者,定价定价 from 图书图书where 书名书名 like%数据库数据库%for update of 定价定价Open cur1Fetch absolute 9 from cur1297.2 分行处理程序分行处理程序游标游标例例 在图书表中查询出与数据库相关的图书，并对其在图书表中查询出与数据库相关的图书，并对其中的部分书的价格进行调整。中的部分书的价格进行调整。Declare cur1 scroll cursor forselect 书名书名,作者作者,定价定价 from 图书图书where 书名书名 like%数据库数据库%Open cur1Fetch absolute 9 from cur1Update 图书图书 set 定价定价=30 where current of cur1307.2.3 游标应用实例游标应用实例 1.用于游标的用于游标的UPDATE语句的语法格式：语句的语法格式：UPDATE SET =|NULL|,n WHERE CURRENT OF 2.用于游标的用于游标的DELETE语句的语法格式：语句的语法格式：DELETE FROM WHERE CURRENT OF 31利用变量输出游标中的字段值利用变量输出游标中的字段值Declare S1 scroll cursor forSelect 姓名姓名,班级班级,综合测评综合测评 from 学生学生Where 班级班级 like%地信地信%Declare sn intDeclare sc varchar(10)Declare ss varchar(10)Open S1Fetch absolute 4 from S into sn,sc,ssSelect sn,sc,ssClose S1Deallocate S132利用游标修改数据库中的数据利用游标修改数据库中的数据Select 姓名姓名,班级班级,综合测评综合测评 from 学生学生Declare S2 scroll cursor forSelect 姓名姓名,班级班级,综合测评综合测评 from 学生学生Where 班级班级 like%地信地信%Open S2Fetch first from SUpdate 学生学生 set 姓名姓名=modifybycursor where current of S2Close S2Deallocate S233实例11:利用游标删除数据库中的数据Select 姓名姓名,班级班级,综合测评综合测评 from 学生学生Declare S3 scroll cursor forSelect 姓名姓名,班级班级,综合测评综合测评 from 学生学生Where 班级班级 like%地信地信%Open S3Fetch absolute 3 from S3Delete from 职工职工 where current of S3Close S3Deallocate S334使用使用fetch_status:fetch_status:该全局变量该全局变量/配置函数返回被最后配置函数返回被最后FETCHFETCH语语句执行的游标的状态，返回类型为句执行的游标的状态，返回类型为intint：1.1.0 0：FETCHFETCH语句成功语句成功2.2.1:FETCH1:FETCH语句失败或此行不在结果集中语句失败或此行不在结果集中3.3.2 2：被提取的行不存在：被提取的行不存在35实例12:利用游标判断记录是否存在Declare S4 scroll cursor forSelect 姓名姓名,班级班级,综合测评综合测评 from 学生学生Where 班级班级 like%地信地信%Open S4 If fetch_status=0Print 存在该记录存在该记录ElsePrint 不存在该记录不存在该记录Close S436实例13:利用游标逐条显示数据库中的记录Declare S5 scroll cursor forSelect 姓名姓名,班级班级,综合测评综合测评 from 学生学生Where 班级班级 like%地信地信%Open S5While fetch_status=0BeginFetch next from S5EndClose S5Deallocate S537例例8-20 将地信班中将地信班中2003级的综合测评成绩上浮级的综合测评成绩上浮5%。DECLARE c CHAR(4),g CHAR(4),s doubleDECLARE S6 CURSORFORSELECT 班级班级,年级年级,综合测评综合测评FROM 学生学生Where 班级班级 like%地信地信%7.2.3 游标应用实例游标应用实例 38OPEN 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 S6GO7.2.3 游标应用实例游标应用实例
