专辑：MySQL教程_游标详解-Java充电社【公众号：Java充电社】

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

欢迎大家加我微信itsoku一起交流java、算法、数据库相关技术。

这是Mysql系列第19篇。

环境：mysql5.7.25，cmd命令中进行演示。

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

## 需求背景

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

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

## 本篇内容

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

## 准备数据

创建库：`javacode2018`

创建表：test1、test2、test3

```java
/*建库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：

```java
select a,b from test1;
```

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

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

## 游标的使用步骤

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

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

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

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

## 游标语法

### 声明游标

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

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

### 打开游标

```java
open 游标名称;
```

### 遍历游标

```java
fetch 游标名称 into 变量列表;
```

> 取出当前行的结果，将结果放在对应的变量中，并将游标指针指向下一行的数据。
>
> 当调用fetch的时候，会获取当前行的数据，如果当前行无数据，会引发mysql内部的`NOT FOUND`错误。

### 关闭游标

```java
close 游标名称;
```

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

## 单游标示例

> 写一个函数，计算test1表中a、b字段所有的和。

**创建函数：**

```java
/*删除函数*/
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 创建函数出现This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA**
>
> This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary
>
> mysql的设置默认是不允许创建函数
>
> **解决办法1:**
>
> 执行：
>
> SET GLOBAL log_bin_trust_function_creators = 1;
>
> 不过 重启了 就失效了
>
> 注意： 有主从复制的时候 从机必须要设置  不然会导致主从同步失败
>
> **解决办法2：**
>
> 在my.cnf里面设置
>
> log-bin-trust-function-creators=1
>
> 不过这个需要重启服务

**见效果：**

```java
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`异常的时候，我们可以使用一个变量来标记一下，如下代码：

```java
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE;
```

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

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

```java
fetch 游标名称 into 变量列表;
```

## 嵌套游标

> 写个存储过程，遍历test2、test3，将test2中的a字段和test3中的b字段任意组合，插入到test1表中。

**创建存储过程：**

```java
/*删除存储过程*/
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 ;
```

**见效果：**

```java
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. 大家下去了多练习一下，熟练掌握游标的使用

<a style="display:none" target="_blank" href="https://mp.weixin.qq.com/s/_S1DD2JADnXvpexxaBwLLg" style="color:red; font-size:20px; font-weight:bold">继续收门徒，亲手带，月薪 4W 以下的可以来找我</a>

## 最新资料

1. <a href="https://mp.weixin.qq.com/s?__biz=MzkzOTI3Nzc0Mg==&mid=2247484964&idx=2&sn=c81bce2f26015ee0f9632ddc6c67df03&scene=21#wechat_redirect" target="_blank">尚硅谷 Java 学科全套教程（总 207.77GB）</a>
2. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484192&idx=1&sn=505f2faaa4cc911f553850667749bcbb&scene=21#wechat_redirect" target="_blank">2021 最新版 Java 微服务学习线路图 + 视频</a>
3. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484573&idx=1&sn=7f3d83892186c16c57bc0b99f03f1ffd&scene=21#wechat_redirect" target="_blank">阿里技术大佬整理的《Spring 学习笔记.pdf》</a>
4. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484544&idx=2&sn=c1dfe907cfaa5b9ae8e66fc247ccbe84&scene=21#wechat_redirect" target="_blank">阿里大佬的《MySQL 学习笔记高清.pdf》</a>
5. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485167&idx=1&sn=48d75c8e93e748235a3547f34921dfb7&scene=21#wechat_redirect" target="_blank">2021 版 java 高并发常见面试题汇总.pdf</a>
6. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485664&idx=1&sn=435f9f515a8f881642820d7790ad20ce&scene=21#wechat_redirect" target="_blank">Idea 快捷键大全.pdf</a>

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/1/2883e86e-3eff-404a-8943-0066e5e2b454.png)