7-游标

1、概述

# 游标cursor,可理解为一个内存块，用户存储select语句的结果集
# 游标多用于存储过程和触发器
# 定义一个游标：
declare 游标名 cursor for {select语句};
# 打开游标：
open 游标名;
# 关闭游标:
close 游标名;
# 获取游标内容:
declare 变量;
fetch 游标名 into 变量;
select 变量；
注: fetch每次只读取一条数据，若读取所有数据，需使用循环语句。
# 游标结束：
declare 变量;
declare continue handler for not found set 变量=值；

eg:
declare aa tinyint default 0;
declare continue handler for not found set aa=1;

2、示例1：mm1表指定编号,插入mm2。[游标一列数据]

mysql> select * from mm1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | lilei    |
|    2 | hanmei   |
|    3 | lihua    |
|    4 | xiaoming |
|    5 | erxi     |
|    6 | goudan   |
|    7 | zhuzi    |
|    8 | ashi     |
|    9 | baozei   |
|   10 | benben   |
+------+----------+
10 rows in set (0.00 sec)

mysql> desc mm2;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| namebak    | varchar(10) | YES  |     | NULL    |       |
| inserttime | datetime    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 创建向mm2插入数据的存储过程
mysql> drop procedure if exists x1;
Query OK, 0 rows affected (0.14 sec)

mysql> delimiter //
mysql> create procedure x1(in v1 varchar(10), in v2 datetime)
    -> begin
    -> insert into mm2 values (v1,v2);
    -> end //
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter ;
# 创建使用游标批量插入的存储过程
mysql> drop procedure if exists x2;
Query OK, 0 rows affected (0.13 sec)

mysql> delimiter //
mysql> create procedure x2(in snum tinyint,in esum tinyint)
    -> begin
    -> declare aa varchar(10);
    -> declare bb tinyint default 0;
    -> declare fbi cursor for select name from mm1 where id between snum and esum;
    -> declare continue handler for not found set bb=1;
    -> open fbi;
    -> repeat
    -> fetch fbi into aa;
    -> if bb<>1 then
    -> call x1(aa,now());
    -> end if;
    -> until bb=1
    -> end repeat;
    -> close fbi;
    -> end //
Query OK, 0 rows affected (0.08 sec)

mysql> delimiter ;
mysql> call x2(2,5);
Query OK, 0 rows affected (0.26 sec)

mysql> select * from mm2;
+----------+---------------------+
| namebak  | inserttime          |
+----------+---------------------+
| hanmei   | 2020-12-23 11:54:00 |
| lihua    | 2020-12-23 11:54:00 |
| xiaoming | 2020-12-23 11:54:00 |
| erxi     | 2020-12-23 11:54:00 |
+----------+---------------------+
4 rows in set (0.00 sec)

3、示例2：mm1表指定编号,插入mm2。[游标多列数据]

mysql> select * from mm1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | lilei    |
|    2 | hanmei   |
|    3 | lihua    |
|    4 | xiaoming |
|    5 | erxi     |
|    6 | goudan   |
|    7 | zhuzi    |
|    8 | ashi     |
|    9 | baozei   |
|   10 | benben   |
+------+----------+
10 rows in set (0.00 sec)

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

mysql> desc mm1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | tinyint     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc mm2;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| idbak   | tinyint     | YES  |     | NULL    |       |
| namebak | varchar(10) | YES  |     | NULL    |       |
| time    | datetime    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> delimiter //
mysql> create procedure x1(in v1 tinyint,in v2 varchar(10),in v3 datetime)
    -> begin
    -> insert into mm2 values (v1,v2,v3);
    -> end //
Query OK, 0 rows affected (0.10 sec)

mysql> drop procedure if exists x2;
Query OK, 0 rows affected (0.46 sec)

mysql> delimiter //
mysql> create procedure x2(in snum tinyint,in esum tinyint)
    -> begin
    -> declare aa tinyint;
    -> declare bb varchar(10);
    -> declare cc tinyint default 0;
    -> declare fbi cursor for select * from mm1 where id between snum and esum;
    -> declare continue handler for not found set cc=1;
    -> open fbi;
    -> repeat
    -> fetch fbi into aa,bb;
    -> if cc<>1 then
    -> call x1(aa,bb,now());
    -> end if;
    -> until cc=1
    -> end repeat;
    -> close fbi;
    -> end //
Query OK, 0 rows affected (0.16 sec)

mysql> call x2(7,9);
Query OK, 0 rows affected (0.57 sec)

mysql> select * from mm2;
+-------+---------+---------------------+
| idbak | namebak | time                |
+-------+---------+---------------------+
|     7 | zhuzi   | 2020-12-23 15:18:34 |
|     8 | ashi    | 2020-12-23 15:18:35 |
|     9 | baozei  | 2020-12-23 15:18:35 |
+-------+---------+---------------------+
3 rows in set (0.00 sec)