011 数据库学习笔记--游标

游标：游标说白了, 就是先取一个结果集,然后后对这个结果集逐条进行操作

一般复杂的存储过程，都会有游标的出现，他的用处主要有：

定位到结果集中的某一行。
对当前位置的数据进行读写。
可以对结果集中的数据单独操作，而不是整行执行相同的操作。
是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

定义：

游标是对数据查询结果集的一种访问机制，允许用户对结果集进行逐条访问，即单条数据。
访问对象是，结果集
可以理解为定义在特定结果集上的指针，控制这个指针，遍历数据集或制定特定的行--对其进行读取或写入

作用：

定位到结果集中的某一行，对当期位置的数据进行读写
数据读取出来一般放到临时数据库里，放到内存(缺点：内存空间有限，不可太大，内存空间不足)
适用于数据量小的情况

分类：静态游标、动态游标、只进游标、键集驱动游标

静态游标
- 游标操作的是结果集，对数据库如何操作，结果集都不会改变
- 如果想获取操作后的结果集，则需关闭游标后重新打开游标
- 在滚动时，监测不到表数据的变化，消耗的资源相对较少——性能高
动态游标
- 与静态游标相对，前后滚动，结果集会获取所有的改变，提取时，行数据、顺序、成员都会发生变化
- 对数据可见
- 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