MySQL全面瓦解19：游标相关

定义

我们经常会遇到这样的一种情况，需要对我们查询的结果进行遍历操作，并对遍历到的每一条数据进行处理，这时候就会使用到游标。

所以：游标（Cursor）是处理数据的一种存储在MySQL服务器上的数据库查询方法，为了查看或者处理结果集中的数据，提供了在结果集中一次一行遍历数据的能力。

游标主要用在循环处理、存储过程、函数、触发器中。

游标的作用

比如我们上面那个students学生，需要对每个用户进行遍历，然后根据他们的其他评价进行加分或者减分。这时候我们就需要查询到所有的学生信息（包含成绩）。

    1 select studentid,studentname,score from students;

执行之后返回了的学生数据集合，我们如果需要对学生数据逐一遍历，然后根据具体的情况进行加分，那就需要是使用游标了。

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

游标的使用

声明游标： 创建一个游标，并指定这个游标需要遍历的select查询，声明游标时并不会去执行这个sql。

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

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

业务操作： 对遍历到的每行数据进行操作的过程，可以放置任何需要执行的执行的语句(增删改查):这里视具体情况而定

关闭游标： 游标使用完之后一定要释放。

注:使用的临时字段需要在定义游标之前进行声明。

声明游标

    1 DECLARE cursor_name CURSOR FOR select_statement;

声明一个游标。也可以在子程序中定义多个游标，但是一个块中的每一个游标必须有唯一的名字。声明游标后也是单条操作的，但是SELECT语句不能有INTO子句。

一个begin end中只能声明一个游标。

打开游标

    1 OPEN cursor_name;

打开先前声明的游标。

遍历游标数据

    1 FETCH cursor_name INTO var_list;

这个语句用指定的打开游标读取下一行（如果有下一行的话），并且前进游标指针。取出当前行的结果，将结果放在对应的变量中，并将游标指针指向下一行的数据。

当调用fetch的时候，会获取当前行的数据，如果当前行无数据，会引发mysql内部的NOT FOUND错误。

关闭游标

    1 CLOSE cursor_name;

切记游标使用完毕之后要关闭。

游标举例

写一个函数，里面包含对students 学生用户成绩的计算和附加分计算

数据基础

     1 mysql> select * from students;
     2 +-----------+-------------+-------+---------+
     3 | studentid | studentname | score | classid |
     4 +-----------+-------------+-------+---------+
     5 | 1 | brand | 97.5 | 1 |
     6 | 2 | helen | 96.5 | 1 |
     7 | 3 | lyn | 96 | 1 |
     8 | 4 | sol | 97 | 1 |
     9 | 5 | b1 | 81 | 2 |
    10 | 6 | b2 | 82 | 2 |
    11 | 7 | c1 | 71 | 3 |
    12 | 8 | c2 | 72.5 | 3 |
    13 | 9 | lala | 73 | 0 |
    14 | 10 | A | 99 | 3 |
    15 | 16 | test1 | 100 | 0 |
    16 | 17 | trigger2 | 107 | 0 |
    17 | 22 | trigger1 | 100 | 0 |
    18 +-----------+-------------+-------+---------+
    19 13 rows in set

编写包含游标的函数

这边注释很清晰，关键知识点都已经标红

     1 mysql>
     2 /*判断函数如果存在则删除*/
     3 DROP FUNCTION IF EXISTS fun_test;
     4 /*声明结束符为$*/
     5 DELIMITER $
     6 /*创建函数，对符合条件的每个同学的分数进行加分，加的分数不能超过给定的值max_score*/
     7 CREATE FUNCTION fun_test(max_score decimal(10,2))
     8 RETURNS int
     9 BEGIN
    10 /*定义实时StudentId的变量*/
    11 DECLARE var_studentId int DEFAULT 0;
    12 /*定义计算后分数的变量*/
    13 DECLARE var_score decimal(10,2) DEFAULT 0;
    14 /*定义游标结束标志变量*/
    15 DECLARE var_done int DEFAULT FALSE;
    16 /*创建游标*/
    17 DECLARE cur_test CURSOR FOR SELECT studentid,score from students where classid<>0;
    18 /*游标结束时会设置var_done为true，后续可以使用var_done来判断游标是否结束*/
    19 DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
    20 /*打开游标*/
    21 OPEN cur_test;
    22 /*使用Loop循环遍历游标*/
    23 select_loop:LOOP
    24 /*先获取当前行的数据，然后将当前行的数据放入var_studentId,var_score中，如果无数据行了，var_done会被置为true*/
    25 FETCH cur_test INTO var_studentId,var_score;
    26 /*通过var_done来判断游标是否结束了，退出循环*/
    27 IF var_done THEN
    28 LEAVE select_loop;
    29 END IF;
    30 /*对var_score值添加随机值，不能超过给定的分数*/
    31 set var_score = var_score + LEAST(ROUND(rand()*10,0),max_score);
    32 update students set score = var_score where studentId= var_studentId;
    33 END LOOP;
    34 /*关闭游标*/
    35 CLOSE cur_test;
    36 /*返回结果:可以根据实际情况返回需要的内容*/
    37 RETURN 1;
    38 END $
    39 /*结束符置为;*/
    40 DELIMITER ;
    41 Query OK, 0 rows affected

调用函数

    1 mysql>
    2 /* 参数为8，表示加分上限为8 */
    3 select fun_test(8);
    4 +-------------+
    5 | fun_test(8) |
    6 +-------------+
    7 | 1 |
    8 +-------------+
    9 1 row in set

查看结果

对比原来的成绩的值，发现成绩添加了随机值，但没超过给定的分数 8

     1 mysql> select * from students;
     2 +-----------+-------------+-------+---------+
     3 | studentid | studentname | score | classid |
     4 +-----------+-------------+-------+---------+
     5 | 1 | brand | 105.5 | 1 |
     6 | 2 | helen | 98.5 | 1 |
     7 | 3 | lyn | 97 | 1 |
     8 | 4 | sol | 97 | 1 |
     9 | 5 | b1 | 89 | 2 |
    10 | 6 | b2 | 90 | 2 |
    11 | 7 | c1 | 76 | 3 |
    12 | 8 | c2 | 73.5 | 3 |
    13 | 9 | lala | 73 | 0 |
    14 | 10 | A | 100 | 3 |
    15 | 16 | test1 | 100 | 0 |
    16 | 17 | trigger2 | 107 | 0 |
    17 | 22 | trigger1 | 100 | 0 |
    18 +-----------+-------------+-------+---------+
    19 13 rows in set

查看触发器日志

符合条件被修改分数的有9条数据，都已经被触发器记录到日志里面了

     1 mysql>
     2 /*上一篇编写了触发器，当修改students表的时候触发日志记录 */
     3 select * from triggerlog;
     4 +----+--------------+---------------+-----------------------------------------+
     5 | id | trigger_time | trigger_event | memo |
     6 +----+--------------+---------------+-----------------------------------------+
     7 | 1 | after | insert | new student info,id:21 |
     8 | 2 | after | update | update student info,id:21 |
     9 | 3 | after | update | delete student info,id:21 |
    10 | 4 | after | update | from:test2,101.00 to:trigger2,106.00 |
    11 | 5 | after | update | from:trigger2,106.00 to:trigger2,107.00 |
    12 | 6 | after | update | delete student info,id:11 |
    13 | 7 | after | update | from:brand,97.50 to:brand,105.50 |
    14 | 8 | after | update | from:helen,96.50 to:helen,98.50 |
    15 | 9 | after | update | from:lyn,96.00 to:lyn,97.00 |
    16 | 10 | after | update | from:sol,97.00 to:sol,97.00 |
    17 | 11 | after | update | from:b1,81.00 to:b1,89.00 |
    18 | 12 | after | update | from:b2,82.00 to:b2,90.00 |
    19 | 13 | after | update | from:c1,71.00 to:c1,76.00 |
    20 | 14 | after | update | from:c2,72.50 to:c2,73.50 |
    21 | 15 | after | update | from:A,99.00 to:A,100.00 |
    22 +----+--------------+---------------+-----------------------------------------+
    23 15 rows in set

游标的执行过程

按照上面的例子，分析下这个游标的执行过程。

1、我们创建了一个游标，数据源取自于student学生表。

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

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

当触发NOT FOUND异常的时候，我们可以使用一个变量来标记一下，如上面的：DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;

将变量var_done的值置为TURE，循环中就可以通过var_done的值控制循环的退出：LEAVE select_loop;。

如果当前行有数据，则将当前行数据存到对应的变量中，并将游标指针指向下一行数据，如下语句：FETCH cur_test INTO var_studentId,var_score;

总结

1、游标用来对查询结果进行遍历处理。
2、游标的使用过程：声明游标、打开游标、遍历游标、关闭游标。
3、游标主要用在循环处理、存储过程、函数中使用，用来查询结果集。
4、游标的缺点是只能一行一行操作，在数据量大的情况下，是不适用的，速度过慢。数据库大部分是面对集合的，业务会比较复杂，而游标使用会有死锁，影响其他的业务操作，不可取。当数据量大时，使用游标会造成内存不足现象。

Java 面试宝典是大明哥全力打造的 Java 精品面试题，它是一份靠谱、强大、详细、经典的 Java 后端面试宝典。它不仅仅只是一道道面试题，而是一套完整的 Java 知识体系，一套你 Java 知识点的扫盲贴。

它的内容包括：

大厂真题：Java 面试宝典里面的题目都是最近几年的高频的大厂面试真题。
原创内容：Java 面试宝典内容全部都是大明哥原创，内容全面且通俗易懂，回答部分可以直接作为面试回答内容。
持续更新：一次购买，永久有效。大明哥会持续更新 3+ 年，累计更新 1000+，宝典会不断迭代更新，保证最新、最全面。
覆盖全面：本宝典累计更新 1000+，从 Java 入门到 Java 架构的高频面试题，实现 360° 全覆盖。
不止面试：内容包含面试题解析、内容详解、知识扩展，它不仅仅只是一份面试题，更是一套完整的 Java 知识体系。
宝典详情：https://www.yuque.com/chenssy/sike-java/xvlo920axlp7sf4k
宝典总览：https://www.yuque.com/chenssy/sike-java/yogsehzntzgp4ly1
宝典进展：https://www.yuque.com/chenssy/sike-java/en9ned7loo47z5aw

目前 Java 面试宝典累计更新 400+ 道，总字数 42w+。大明哥还在持续更新中，下图是大明哥在 2024-12 月份的更新情况：

想了解详情的小伙伴，扫描下面二维码加大明哥微信【daming091】咨询

同时，大明哥也整理一套目前市面最常见的热点面试题。微信搜[大明哥聊 Java]或扫描下方二维码关注大明哥的原创公众号[大明哥聊 Java] ，回复【面试题】即可免费领取。

阅读全文