游标:游标说白了, 就是先取一个结果集,然后后对这个结果集逐条进行操作
一般复杂的存储过程,都会有游标的出现,他的用处主要有:
- 定位到结果集中的某一行。
- 对当前位置的数据进行读写。
- 可以对结果集中的数据单独操作,而不是整行执行相同的操作。
- 是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。
定义:
- 游标是对数据查询结果集的一种访问机制,允许用户对结果集进行逐条访问,即单条数据。
- 访问对象是,结果集
- 可以理解为定义在特定结果集上的指针,控制这个指针,遍历数据集或制定特定的行--对其进行读取或写入
作用:
- 定位到结果集中的某一行,对当期位置的数据进行读写
- 数据读取出来一般放到临时数据库里,放到内存(缺点:内存空间有限,不可太大,内存空间不足)
- 适用于数据量小的情况
分类:静态游标、动态游标、只进游标、键集驱动游标
- 静态游标
- 游标操作的是结果集,对数据库如何操作,结果集都不会改变
- 如果想获取操作后的结果集,则需关闭游标后重新打开游标
- 在滚动时,监测不到表数据的变化,消耗的资源相对较少——性能高
- 动态游标
- 与静态游标相对,前后滚动,结果集会获取所有的改变,提取时,行数据、顺序、成员都会发生变化
- 对数据可见
- api函数或T-SQL where current of 子句通过游标进行更新
- 游标对外部所做的更新到提交时才可见
- 在滚动时,可监测到表数据的变化,消耗的资源相对比较多——性能低
- 只进游标
- 不支持前后滚动,只能从头读到未——只读取数据
- 对数据库所做的更改在提取时是可见的,但不可回退,只进不退;提取后所做的更改是不可见的
- 键集驱动游标
游标生命周期/步骤:声明游标、打开游标、读取游标数据、关闭游标、释放游标
- 声明游标
- 打开游标
- 从游标中读取数据
- 关闭游标
- 释放游标
参考:https://blog.csdn.net/guokeeiron/article/details/130003298
补充关于游标的几个变量:
1.@@Fetch_Status:获得提取状态信息,该状态用于判断Fetch语句返回数据的有效性;!主要用于游标读取做循环操作;
0,Fetch语句成功。
-1:Fetch语句失败或行不在结果集中。
-2:提取的行不存在。
2.全局 变量 @@CORSOR_ROWS 用来记录游标内的数据行数。返回值有四种:全局 变量 @@CORSOR_ROWS 用来记录游标内的数据行数。返回值有四种:
返回值 描述
-m 表示仍在从基础表向游标读入数据,m表示当前在游标中的数据行数
-1 该游标是一个动态游标,其返回值无法确定
0 无符合调剂的记录或游标已经关闭
n 从基础表向游标读入数据已结束,n 为游标中已有的数据记录行数
读取数据的格式:
Fetch [ Next|prior|Frist|Last|Absoute n|Relative n ] from cursor_name [into @variable_name[,....]]
解读格式
Frist:结果集的第一行
Prior:当前位置的上一行
Next:当前位置的下一行
Last:最后一行
Absoute n:从游标的第一行开始数,第n行。
Relative n:从当前位置数,第n行。
Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。
应用示例:
示例一:游标中获取表中数据并打印
------------------游标操作步骤------------------ ------------------声明游标、打开游标、读取游标数据、关闭游标、释放游标------------------ DECLARE @student_name VARCHAR(MAX), @student_age int --1.声明游标 DECLARE cursor_student CURSOR FOR SELECT student_name, student_age FROM [dbo].[student]; --2.打开游标 OPEN cursor_student; --3.读取游标数据 FETCH NEXT FROM cursor_student INTO @student_name,@student_age; --判断是否执行成功,0 表示成功;-1表示失败 WHILE @@FETCH_STATUS = 0 BEGIN --打印获取到的数据 PRINT '姓名:'+@student_name +',年龄:'+ CAST(@student_age AS varchar); --再次读取,否则只读取一次 FETCH NEXT FROM cursor_student INTO @student_name,@student_age; END; --4.关闭游标 CLOSE cursor_student; --5.释放游标 DEALLOCATE cursor_student; go
示例二:存储过程中使用游标,调研执行存储过程
------------------游标操作步骤------------------ ------------------声明游标、打开游标、读取游标数据、关闭游标、释放游标------------------ ------------------存储过程中使用游标------------------ --示例一 单个参数 if object_id('searchStu','p') is not null drop proc searchStu go create proc searchStu(@studentInputAge int) as --定义变量,用于接收查询数据的行返回信息 DECLARE @student_name VARCHAR(100), @student_age int, @student_sex VARCHAR(10) --1.声明游标 DECLARE cursor_student CURSOR FOR SELECT student_name,student_age,student_sex FROM [dbo].[student] where student_age=@studentInputAge; --2.打开游标 OPEN cursor_student; --3.读取游标数据 FETCH NEXT FROM cursor_student INTO @student_name,@student_age,@student_sex; --判断是否执行成功 WHILE @@FETCH_STATUS = 0 BEGIN --打印获取到的数据 PRINT '姓名:'+@student_name +',年龄:'+ CAST(@student_age AS varchar)+',性别:' + @student_sex; --再次读取,否则只读取一次 FETCH NEXT FROM cursor_student INTO @student_name,@student_age,@student_sex; END; --4.关闭游标 CLOSE cursor_student; --5.释放游标 DEALLOCATE cursor_student; go --执行searchStu exec searchStu 18
示例三:
ALTER procedure [dbo].[INIT_DICT_QUEUECODE] ( @p_QUEUEID int, @p_QUEUENAME varchar(100), @p_DEVICECOUNT int, @p_CHECKDURATION int, @p_BEGINTIMEPART varchar(50), @p_ENDTIMEPART varchar(50), @p_CODECOUNT int, @p_QUEUESIGN varchar(50), @p_ADDRESS varchar(300), @p_REMARK varchar(500), @p_ofdepart varchar(30), @p_PMBEGINTIME varchar(50), @p_PMENDTIME varchar(50), @p_CodeCoefficient varchar(50), @p_TIMEPART varchar(50), @p_PARAMTYPE varchar(50), @p_CALLTYPE varchar(50), @p_result int output ) as insert into QS_PARAM (QUEUEID, QUEUENAME, DEVICECOUNT, CHECKDURATION, BEGINTIMEPART, ENDTIMEPART, CODECOUNT,QUEUESIGN,ADDRESS,ofdepart,PMBEGINTIME, PMENDTIME, CodeCoefficient, TIMEPART,PARAMTYPE,CALLTYPE,REMARK) values(@p_QUEUEID, @p_QUEUENAME, @p_DEVICECOUNT, @p_CHECKDURATION, @p_BEGINTIMEPART, @p_ENDTIMEPART, @p_CODECOUNT,@p_QUEUESIGN,@p_ADDRESS,@p_ofdepart,@p_PMBEGINTIME, @p_PMENDTIME, @p_CodeCoefficient, @p_TIMEPART,@p_PARAMTYPE,@p_CALLTYPE,@p_REMARK) declare @loopNum int,--循环次数 @codeNum int,--号源编号 @codeFirstAm int, @codeFirstPm int DECLARE paramDetails cursor for select queuename,paramtype,begintimepart,endtimepart,ofdepart,calltype,codecount from qs_param a where a.queueid=@p_QUEUEID and a.paramtype=@p_PARAMTYPE order by convert(float,endtimepart) begin try set @loopNum=1 set @codeNum=1 set @codeFirstAm=0--未进行初始化 set @codeFirstPm=0--未进行初始化 --删除号源字典 delete from dict_queuecode where queuename=@p_QUEUENAME and codeparamtype=@p_PARAMTYPE and ofdepart=@p_ofdepart begin tran --当前事务点,rollback、commit都从这里开始 DECLARE @v_QUEUEID int, @v_QUEUENAME varchar(100), @v_DEVICECOUNT int, @v_CHECKDURATION int, @v_BEGINTIMEPART varchar(50), @v_ENDTIMEPART varchar(50), @v_CODECOUNT int, @v_QUEUESIGN varchar(50), @v_ADDRESS varchar(50), @v_ofdepart varchar(30), @v_PMBEGINTIME varchar(50), @v_PMENDTIME varchar(50), @v_CodeCoefficient varchar(50), @v_TIMEPART varchar(50), @v_PARAMTYPE varchar(50), @v_CALLTYPE varchar(50) open paramDetails fetch next from paramDetails into @v_queuename,@v_paramtype,@v_begintimepart,@v_endtimepart,@v_ofdepart,@v_calltype,@v_codecount WHILE @@FETCH_STATUS =0 begin --循环获取qs_param表中维护的某个时间段 set @loopNum=1 while @loopNum <= @v_codecount begin --上午 if @codeFirstAm=0 and @v_calltype='上午' begin set @codeNum=1 set @codeFirstAm=1 end --下午 if @codeFirstPm=0 and @v_calltype='下午' begin set @codeNum=1 set @codeFirstPm=1 end --添加号源信息 insert into DICT_QUEUECODE (queuename,codeparamtype,codevalue,timepart,hintinfo,OFDEPART,calltype,queueid) values (@v_queuename,@v_paramtype,@codeNum,@v_begintimepart+'~'+@v_endtimepart,'',@v_ofdepart,@v_calltype,@p_QUEUEID) --重新赋值 set @loopNum=@loopNum+1 set @codeNum=@codeNum+1 end fetch next from paramDetails into @v_queuename,@v_paramtype,@v_begintimepart,@v_endtimepart,@v_ofdepart,@v_calltype,@v_codecount end close paramDetails DEALLOCATE paramDetails set @p_result=1 commit end try begin catch set @p_result=-1 rollback end catch
示例
if object_id('GetData','p') is not null drop proc GetData go create proc GetData as declare @className VARCHAR(50) declare @sql varchar(4000) set @sql= 'select ' +'''检查医生'',' --1.声明游标 DECLARE cursor_devices CURSOR FOR select classname from test group by ClassName --2.打开游标 OPEN cursor_devices; --3.读取游标数据 FETCH NEXT FROM cursor_devices INTO @className; --判断是否执行成功,0 表示成功;-1表示失败 WHILE @@FETCH_STATUS = 0 BEGIN set @sql = @sql +''''+ @className + ''',' --再次读取,否则只读取一次 FETCH NEXT FROM cursor_devices INTO @className; END; set @sql= LEFT(@sql,len(@sql)-1) print @sql; exec(@sql); --4.关闭游标 CLOSE cursor_devices; --5.释放游标 DEALLOCATE cursor_devices; go exec GetData