MySQL游标双层循环方法 MySQL存储过程游标嵌套循环 MySQL使用多个游标

左手的ㄟ右手 2023-06-19 09:34 2阅读 0赞

##    MySQL游标双层循环方法 MySQL存储过程游标嵌套循环 MySQL使用多个游标 ##

# #

**一、需求描述**

1、在项目中，需要将A表中主键id，逐个取出，作为条件，在B表中去逐一查询，将B表查询到的结果集（A表B表关系：一对多），逐一遍历，连同A表的id，逐个插入到C表中。

2、 在Java中很容易实现，A表获取到的结果集，循环遍历取出id，去B表查询；遍历B表结果集，插入到C表中。 相当于2个循环，即可实现需求。 这样会有一个问题，频繁连接数据库，造成大量资源开销。 那么在存储过程中，该怎么实现呢？

**二、思路**

1、要实现逐行获取数据，需要用到MySQL中的游标，一个游标相当于一个for循环，这里需要用到2个游标。如何在MySQL中实现游标双层循环呢？

**三、代码实现**

1、 client 表中有8条数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70][]

2、account 表中有2条数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 1][]

3、要实现client表和account表中数据组合插入到batch表中。 （相当于需求）

4、创建存储过程 pro\_cursor\_nest() ， 实现需求。

DROP PROCEDURE IF EXISTS pro_cursor_nest;
    CREATE PROCEDURE pro_cursor_nest()
    BEGIN -- out BEGIN
    		DECLARE c_name VARCHAR(200) ;
    		DECLARE out_done INT DEFAULT FALSE ; -- 外层游标控制变量
    		DECLARE out_cursor CURSOR FOR (SELECT NAME FROM client ); -- 外层游标
    		DECLARE CONTINUE HANDLER FOR NOT FOUND SET out_done = TRUE ; -- 外层游标执行结束，置为TRUE
    		
    		OPEN out_cursor ; -- 打开外层游标
    		WHILE NOT out_done DO -- out WHILE
    				FETCH out_cursor INTO c_name ; -- 从【外层游标】中获取数据，赋值到定义变量中
    				IF NOT out_done THEN -- out IF
    						-- 开始定义内层游标
    						BEGIN -- inner BEGIN
    								DECLARE money INT ;
    								DECLARE inner_done int DEFAULT FALSE ;
    								DECLARE inner_cursor CURSOR FOR ( SELECT balance FROM account  );
    								DECLARE CONTINUE HANDLER FOR NOT FOUND SET inner_done = TRUE ;
    								
    								OPEN inner_cursor ; -- 打开内层游标
    								WHILE NOT inner_done DO -- inner WHILE
    									FETCH inner_cursor INTO money ; -- 从【内层游标】中获取数据，赋值到定义变量中
    									IF NOT inner_done THEN
    										INSERT INTO `batch` (`id`, `name`, `age`) VALUES (UUID(),c_name ,money);
    									END IF;
    								END WHILE ; -- END inner WHILE
    								CLOSE inner_cursor; -- 循环结束后，关闭内层游标
    						END; -- END inner BEGIN
    				END IF; -- END out IF
    		END WHILE; -- END out WHILE
    		CLOSE out_cursor ; -- 循环结束后 ，关闭外层游标
    END; -- END out BEGIN

（给一代码结构更好看的图片）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 2][]

5、执行存储过程： **CALL pro\_cursor\_nest ();**

6、查看 batch 表结果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 3][]

**四、总结**

1、创建一个游标步骤如下：

*  定义变量，接收游标赋值 c\_name
 *  定义游标开关变量 done
 *  定义游标 out\_cursor
 *  游标结束后，关闭开关 --- DECLARE CONTINUE HANDLER FOR NOT FOUND SET
 *  打开游标 OPEN out\_cursor
 *  开启循环 WHILE .. DO （ [还有LOOP ，REPEAT 也可以][LOOP _REPEAT]）
 *  从游标中获取数据，赋值到变量 （FETCH）
 *  判断游标是否执行结束 (IF NOT out\_done )
 *  执行相应业务逻辑操作 do Something
 *  结束循环 (END WHILE)
 *  关闭游标 (CLOSE out\_cursor）

2、创建双层游标，即在 【执行相应业务逻辑操作】，再 BEGIN ... END ， 重新定义一个新游标，注意嵌套关系即可。

3、觉得双层游标循环麻烦，不易理解的，分别写两个存储过程，也可以，那样业务更简单，易于理解，便于后期维护。

了解更多 ....

[MySQL WHILE和LOOP和REPEAT循环的用法区别 MySQL三种循环的区别 MySQL循环使用方法][LOOP _REPEAT]

[解决MySQL游标循环多执行一次的问题][MySQL]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191205142607346.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191205142618523.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191205142737668.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191205142759360.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly,size_16,color_FFFFFF,t_70
[LOOP _REPEAT]: https://blog.csdn.net/HaHa_Sir/article/details/102913056
[MySQL]: https://blog.csdn.net/HaHa_Sir/article/details/103390356