写在前面

前面对存储过程、函数和流程控制语句进行了学习，接下来再学习另一个知识点—游标。当我们需要对一个select语句的查询结果进行遍历时，就可以使用游标来进行遍历，之后就可以对每行数据进行处理。

本文的主要内容如下：（1）游标定义；（2）游标作用；（3）游标作用；（4）游标的使用语法；（5）单游标示例；（6）游标过程详解；（7）嵌套游标示例；（8）文章总结。

数据准备

为了后续学习的必要，这里先准备了三张数据表envy1、envy2和envy3，相应的建表语句如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
drop database if exists envythink;
create database envythink;

use envythink;

drop table if exists envy1;
create table envy1(a int,b int);
insert into envy1 values(1,2),(3,4),(5,6);

drop table if exists envy2;
create table envy2(a int);
insert into envy2 values(100),(200),(300);

drop table if exists envy3;
create table envy3(b int);
insert into envy3 values(400),(500),(600);

游标

游标定义

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行遍历数据的能力。请注意，游标只能在存储过程和函数中使用。

游标作用

前面也说了，游标是对结果集中的数据一次一行的遍历，它相当于一个指针，这个指针指向select的第一行数据，因此可以通过移动指针来遍历后面的数据。

游标的使用

游标的使用分为4个步骤，分别是声明游标、打开游标、遍历数据和关闭游标。接下来简单解释一下上述各个步骤的使用情况：
（1）声明游标：此过程只是创建一个游标，需要指定游标待遍历的select查询，注意在声明游标的时候，并不会去执行这个SQL。
（2）打开游标：打开游标的时候，才会执行游标对应的select语句。
（3）遍历数据：使用游标循环遍历select结果中的每一行数据，然后才进行处理。
（4）关闭游标：切记，游标使用完成后一定要记得关闭，否则其占用的资源是无法释放的。

游标的使用语法

声明游标

声明游标的语法格式如下所示：

1
declare 游标名称 cursor for 查询语句;

请注意，一个begin…end语句中只能声明一个游标。

打开游标

打开游标的语法格式如下所示：

1
open 游标名称;
遍历游标

遍历游标的语法格式如下所示：

1
fetch 游标名称 into 变量列表;

变量游标时，它会取出当前行的结果，并将结果放在对应的变量中，然后游标指针会指向下一行的数据。请注意，在调用fetch语句的时候，它会获取当前行的数据，如果当前行无数据，那么会引发MySQL内部的NOT FOUND错误。

关闭游标

关闭游标的语法格式如下所示：

1
close 游标名称;

请注意，游标在使用完后，一定要记得关闭，否则其占用的资源是无法释放的。

单游标示例

接下来通过一个需求：写一个函数，计算envy1表中a和b字段所有值的和，来演示如何使用单游标。

第一步，创建函数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
/*删除函数*/
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_envy1 cursor for select a,b from envy1 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_envy1;
    /*使用Loop循环遍历游标*/
    a:loop
      /*先获取当前行的数据，然后将当前行的数据放入v_a,v_b中，如果当前行无数据，v_done会被置为true*/
      fetch cur_envy1 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_envy1;
    /*返回结果*/
    return v_total;
  end $
/*结束符置为;*/
delimiter ;

第二步，调用函数并查询envy1来验证结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select * from envy1;
+------+------+
| 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异常的时候，开发者可以使用一个变量来标记一下，如下所示：

1
declare continue  handler for not found set v_done=true;

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

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

1
fetch 游标名称 into 变量列表;

嵌套游标示例

说完了单游标示例，接下来开始学习嵌套游标。同样通过一个需求：写一个存储过程，遍历envy2和envy3表，将envy2表中的a字段和envy3表中的b字段任意组合，之后插入到envy1表中，来演示如何使用嵌套游标。

第一步，创建存储过程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
/*删除存储过程*/
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_envy2 cursor for  select a from envy2;
    /*设置游标结束时v_done1的值为true，可以v_done1来判断游标cur_envy2是否结束了*/
    declare continue handler for not found set v_done1=true;
    /*打开游标*/
    open cur_envy2;
    /*使用Loop循环遍历游标*/
    a:loop
      fetch cur_envy2 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_envy3 cursor  for select b from envy3;
        /*设置游标结束时v_done1的值为true，可以v_done1来判断游标cur_envy3是否结束了*/
        declare continue handler for not  found set v_done2=true;

        /*打开游标*/
        open cur_envy3;
        /*使用Loop循环遍历游标*/
        b:loop
          fetch cur_envy3 into v_b;
          /*通过v_done1来判断游标是否结束了，退出循环*/
          if v_done2 then
            leave b;
          end if;

          /*将v_a、v_b插入envy1表中*/
          insert into envy1 values(v_a,v_b);
        end loop b;
        /*关闭cur_envy3游标*/
        close cur_envy3;
      end;

    end loop;
    /*关闭游标cur_envy2*/
    close cur_envy2;
  end $
/*结束符置为;*/
delimiter ;

第二步，调用存储过程并查询envy1来验证结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> delete from envy1;
Query OK, 3 rows affected (0.13 sec)

mysql> select * from envy1;
Empty set (0.00 sec)

mysql> call proc1();
Query OK, 0 rows affected (0.44 sec)

mysql> select * from envy1;
+------+------+
| 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条数据。

文章总结

本文介绍了如何使用游标来对查询结果进行遍历，游标的使用分为四个步骤，分别是：声明游标、打开游标、遍历数据和关闭游标。请注意，游标只能在存储过程中和函数中使用。一个begin…end语句中只能声明一个游标。关于游标的学习，了解上述内容就行了。