Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。

这是Mysql系列第19篇。

环境:mysql5.7.25,cmd命令中进行演示。

代码中被[]包含的表示可选,|符号分开的表示可选其一。

需求背景

当我们需要对一个select的查询结果进行遍历处理的时候,如何实现呢?

此时我们需要使用游标,通过游标的方式来遍历select查询的结果集,然后对每行数据进行处理。

本篇内容

  • 游标定义
  • 游标作用
  • 游标使用步骤
  • 游标执行过程详解
  • 单游标示例
  • 嵌套游标示例

准备数据

创建库:javacode2018

创建表:test1、test2、test3

/*建库javacode2018*/
drop database if exists javacode2018;
create database javacode2018;

/*切换到javacode2018库*/
use javacode2018;

DROP TABLE IF EXISTS test1;
CREATE TABLE test1(a int,b int);
INSERT INTO test1 VALUES (1,2),(3,4),(5,6);

DROP TABLE IF EXISTS test2;
CREATE TABLE test2(a int);
INSERT INTO test2 VALUES (100),(200),(300);

DROP TABLE IF EXISTS test3;
CREATE TABLE test3(b int);
INSERT INTO test3 VALUES (400),(500),(600);

游标定义

游标(Cursor)是处理数据的一种方法,为了查看或者处理结果集中的数据,游标提供了在结果集中一次一行遍历数据的能力。

游标只能在存储过程和函数中使用。

游标的作用

如sql:

select a,b from test1;

上面这个查询返回了test1中的数据,如果我们想对这些数据进行遍历处理,此时我们就可以使用游标来进行操作。

游标相当于一个指针,这个指针指向select的第一行数据,可以通过移动指针来遍历后面的数据。

游标的使用步骤

声明游标:这个过程只是创建了一个游标,需要指定这个游标需要遍历的select查询,声明游标时并不会去执行这个sql。

打开游标:打开游标的时候,会执行游标对应的select语句。

遍历数据:使用游标循环遍历select结果中每一行数据,然后进行处理。

关闭游标:游标使用完之后一定要关闭。

游标语法

声明游标

DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标

open 游标名称;

遍历游标

fetch 游标名称 into 变量列表;

关闭游标

close 游标名称;

单游标示例

创建函数:

/*删除函数*/
DROP FUNCTION IF EXISTS fun1;
/*声明结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION fun1(v_max_a int)
  RETURNS int
  BEGIN
    /*用于保存结果*/
    DECLARE v_total int DEFAULT 0;
    /*创建一个变量,用来保存当前行中a的值*/
    DECLARE v_a int DEFAULT 0;
    /*创建一个变量,用来保存当前行中b的值*/
    DECLARE v_b int DEFAULT 0;
    /*创建游标结束标志变量*/
    DECLARE v_done int DEFAULT FALSE;
    /*创建游标*/
    DECLARE cur_test1 CURSOR FOR SELECT a,b from test1 where a<=v_max_a;
    /*设置游标结束时v_done的值为true,可以v_done来判断游标是否结束了*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE;
    /*设置v_total初始值*/
    SET v_total = 0;
    /*打开游标*/
    OPEN cur_test1;
    /*使用Loop循环遍历游标*/
    a:LOOP
      /*先获取当前行的数据,然后将当前行的数据放入v_a,v_b中,如果当前行无数据,v_done会被置为true*/
      FETCH cur_test1 INTO v_a, v_b;
      /*通过v_done来判断游标是否结束了,退出循环*/
      if v_done THEN
        LEAVE a;
      END IF;
      /*对v_total值累加处理*/
      SET v_total = v_total + v_a + v_b;
    END LOOP;
    /*关闭游标*/
    CLOSE cur_test1;
    /*返回结果*/
    RETURN v_total;
  END $
/*结束符置为;*/
DELIMITER ;

见效果:

mysql> SELECT a,b FROM test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT fun1(1);
+---------+
| fun1(1) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT fun1(2);
+---------+
| fun1(2) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT fun1(3);
+---------+
| fun1(3) |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

游标过程详解

以上面的示例代码为例,咱们来看一下游标的详细执行过程。

游标中有个指针,当打开游标的时候,才会执行游标对应的select语句,这个指针会指向select结果中第一行记录

当调用fetch 游标名称时,会获取当前行的数据,如果当前行无数据,会触发NOT FOUND异常。

当触发NOT FOUND异常的时候,我们可以使用一个变量来标记一下,如下代码:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE;

当游标无数据触发NOT FOUND异常的时候,将变量v_down的值置为TURE,循环中就可以通过v_down的值控制循环的退出。

如果当前行有数据,则将当前行数据存到对应的变量中,并将游标指针指向下一行数据,如下语句:

fetch 游标名称 into 变量列表;

嵌套游标

创建存储过程:

/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc1;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc1()
  BEGIN
    /*创建一个变量,用来保存当前行中a的值*/
    DECLARE v_a int DEFAULT 0;
    /*创建游标结束标志变量*/
    DECLARE v_done1 int DEFAULT FALSE;
    /*创建游标*/
    DECLARE cur_test1 CURSOR FOR SELECT a FROM test2;
    /*设置游标结束时v_done1的值为true,可以v_done1来判断游标cur_test1是否结束了*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done1=TRUE;
    /*打开游标*/
    OPEN cur_test1;
    /*使用Loop循环遍历游标*/
    a:LOOP
      FETCH cur_test1 INTO v_a;
      /*通过v_done1来判断游标是否结束了,退出循环*/
      if v_done1 THEN
        LEAVE a;
      END IF;

      BEGIN
        /*创建一个变量,用来保存当前行中b的值*/
        DECLARE v_b int DEFAULT 0;
        /*创建游标结束标志变量*/
        DECLARE v_done2 int DEFAULT FALSE;
        /*创建游标*/
        DECLARE cur_test2 CURSOR FOR SELECT b FROM test3;
        /*设置游标结束时v_done1的值为true,可以v_done1来判断游标cur_test2是否结束了*/
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done2=TRUE;

        /*打开游标*/
        OPEN cur_test2;
        /*使用Loop循环遍历游标*/
        b:LOOP
          FETCH cur_test2 INTO v_b;
          /*通过v_done1来判断游标是否结束了,退出循环*/
          if v_done2 THEN
            LEAVE b;
          END IF;

          /*将v_a、v_b插入test1表中*/
          INSERT INTO test1 VALUES (v_a,v_b);
        END LOOP b;
        /*关闭cur_test2游标*/
        CLOSE cur_test2;
      END;

    END LOOP;
    /*关闭游标cur_test1*/
    CLOSE cur_test1;
  END $
/*结束符置为;*/
DELIMITER ;

见效果:

mysql> DELETE FROM test1;
Query OK, 9 rows affected (0.00 sec)

mysql> SELECT * FROM test1;
Empty set (0.00 sec)

mysql> CALL proc1();
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * from test1;
+------+------+
| a    | b    |
+------+------+
|  100 |  400 |
|  100 |  500 |
|  100 |  600 |
|  200 |  400 |
|  200 |  500 |
|  200 |  600 |
|  300 |  400 |
|  300 |  500 |
|  300 |  600 |
+------+------+
9 rows in set (0.00 sec)

成功插入了9条数据。

总结

  1. 游标用来对查询结果进行遍历处理
  2. 游标的使用过程:声明游标、打开游标、遍历游标、关闭游标
  3. 游标只能在存储过程和函数中使用
  4. 一个begin end中只能声明一个游标
  5. 掌握单个游标及嵌套游标的使用
  6. 大家下去了多练习一下,熟练掌握游标的使用

Mysql系列目录

  1. 第1篇:mysql基础知识
  2. 第2篇:详解mysql数据类型(重点)
  3. 第3篇:管理员必备技能(必须掌握)
  4. 第4篇:DDL常见操作
  5. 第5篇:DML操作汇总(insert,update,delete)
  6. 第6篇:select查询基础篇
  7. 第7篇:玩转select条件查询,避免采坑
  8. 第8篇:详解排序和分页(order by & limit)
  9. 第9篇:分组查询详解(group by & having)
  10. 第10篇:常用的几十个函数详解
  11. 第11篇:深入了解连接查询及原理
  12. 第12篇:子查询
  13. 第13篇:细说NULL导致的神坑,让人防不胜防
  14. 第14篇:详解事务
  15. 第15篇:详解视图
  16. 第16篇:变量详解
  17. 第17篇:存储过程&自定义函数详解
  18. 第18篇:流程控制语句
  19. 第19篇:游标详解
  20. 第20篇:异常捕获及处理详解
  21. 第21篇:什么是索引?

mysql系列大概有20多篇,喜欢的请关注一下,欢迎大家加我微信itsoku或者留言交流mysql相关技术!

Mysql高手系列 - 第19篇:mysql游标详解,此技能可用于救火-LMLPHP

10-10 04:07