【MySQL语法】游标

目录

• 1. MySQL 游标
  • 1.1. 概述
  • 1.2. 操作
    • 1.2.1. 基本语法、
    • 1.2.2. 示例

---

1. MySQL 游标

1.1. 概述

1. 概述：

   1. 当需要在 检索出来的行中前进或后退 一行或多行；
   2. 是被语句 检索出来的结果集；
   3. 游标(cursor) 是存储在服务器上的 数据库查询；
   4. 只用于 存储过程(和函数)；

2. 优点

   1. 针对 行 进行操作；
   2. 对查询到的结果集的每一行，进行独立的 相同或是 不同的操作，是一种分离的思想；

3. 游标的缺点

   1. 性能不高
   2. 只能一行一行操作
   3. 使用游标会产生死锁，造成内存开销大

4. 游标的适用场景

   1. 存储过程 procesure
   2. 函数
   3. 触发器
   4. 事件

参考：

1. MySQL 游标的使用
2. mysql游标的用法及作用

---

1.2. 操作

1.2.1. 基本语法、

1. 基本语法

   1、游标的定义
      DECLARE 光标名称 CURSOR FOR 查询语法
      declare cursor_name cursor for select_statement
   2、打开游标OPEN 光标名称
      open cursor_name
   3、取游标中的数据
      FETCH 光标名称 INFO var_name [，var_name ].....
      fetch cursor_name info var_name
   4、关闭游标
      CLOSE curso_name;
      close 光标名称
   5、释放游标
      DEALLOCATE 光标名称
      deallocate cursor_name;
   

2. 方法

   1. loop
   2. while
   3. repeat

3. 注意：

   1. fetch 是获取当前指向的数据行，并将指针指向下一行；指向最后一行时，继续执行则会溢出；
   2. loop本身不会监控是否遍历到 最后一条数据；易造成死循环；

      read_loop:loop  
      fetch cur into n,c;  
      set total = total+c;  
      end loop;  
      

   3. MySql中同一个error的事件只能定义一次，如果多定义的话在编译时会提示Duplicate handler declared in the same block。

1.2.2. 示例

1. 预制数据

create table student(
    stuId int primary key auto_increment,
    stuName varchar(20),
    stuSex varchar(2),
    stuAge int
)default charset=utf8;
​
insert into student(stuName,stuSex,stuAge) values
('小明','男',20),
('小花','女',19),
('大赤','男',20),
('可乐','男',19),
('莹莹','女',19);​

2. 普通案例

delimiter //
create procedure p1()
begin
    declare id int;
    declare name varchar(100) character set utf8;
    declare done int default 0;
    -- 声明游标
    declare mc cursor for select stuId,stuName from student where stuAge >19;
    declare continue handler for not found set done = 1;
    -- 打开游标
    open mc;
    -- 获取结果
    fetch mc into id,name;
    -- 这里是为了显示获取结果
    select id,name;
    -- 关闭游标
    close mc;
end //
delimiter ;

3. loop 循环

-- 定义语法结束符号
delimiter //
-- 创建一个 名称为 p2 的存储过程
create procedure p2()
begin
    -- 创建 用于接收游标值的变量
    declare id,age,total int;
    -- 注意 接收游标值为中文时 需要 给变量 指定 字符集为utf8
    declare name,sex varchar(20) character set utf8;
    
    -- 游标结束的标志
    declare done int default 0;
    -- 声明游标
    declare cur cursor for select stuId,stuName,stuSex,stuAge from student where stuAge > 19;
    -- 指定游标循环结束时的返回值 
    declare continue handler for not found set done =1;
    -- 打开游标
    open cur;
    
    -- 初始化 变量
    set total = 0;
    
    -- loop 循环
    xxx:loop
        -- 根据游标当前指向的一条数据  
        fetch cur into id,name,sex,age;
        -- 当 游标的返回值为 1 时 退出 loop循环 
        if done = 1 then
            leave xxx;
        end if;
        -- 累计
        set total = total + 1;
    end loop;
    -- 关闭游标
    close cur;
    -- 输出 累计的结果
    select total;
end //
delimiter;

4. while循环
   delimiter /-- 创建一个 名称为 p3 的存储过程create procedure p3()

delimiter //
-- 创建一个 名称为 p3 的存储过程
create procedure p3()
begin
    -- 创建 用于接收游标值的变量
    declare id,age,total int;
    -- 注意 接收游标值为中文时 需要 给变量 指定 字符集为utf8
    declare name,sex varchar(20) character set utf8;
    -- 游标结束的标志
    declare done int default 0;
    -- 声明游标
    declare cur cursor for select stuId,stuName,stuSex,stuAge from student where stuAge > 19;
    -- 指定游标循环结束时的返回值 
    declare continue handler for not found set done = 1;
    -- 打开游标
    open cur;
    
    -- 初始化 变量
    set total = 0;
    
    -- while 循环
    while done != 1 do
        fetch cur into id,name,sex,age;
          if done != 1 then
             set total = total + 1;
          end if;    
    end while;
    -- 关闭游标
    close cur;
    -- 输出 累计的结果
    select total;
end //
delimiter ;

5. repeat 循环

delimiter //
-- 创建一个 名称为 p3 的存储过程
create procedure p3()
begin
    -- 创建 用于接收游标值的变量
    declare id,age,total int;
    -- 注意 接收游标值为中文时 需要 给变量 指定 字符集为utf8
    declare name,sex varchar(20) character set utf8;
    -- 游标结束的标志
    declare done int default 0;
    -- 声明游标
    declare cur cursor for select stuId,stuName,stuSex,stuAge from student where stuAge > 19;
    -- 指定游标循环结束时的返回值 
    declare continue handler for not found set done = 1;
    -- 打开游标
    open cur;
    
    -- 初始化 变量
    set total = 0;
    
    -- repeat 循环
    repeat
        fetch cur into id,name,sex,age;
           if done != 1 then
              set total = total + 1;
           end if;
        until done = 1
    end repeat;
    -- 关闭游标
    close cur;
    -- 输出 累计的结果
    select total;
end //
delimiter ;

参考：

1. MySQL 游标的使用

---