MySQL游标多循环一次的问题怎么解决

开发技术作者：chen 2024-03-28 06:45:01

这篇文章主要讲解了“MySQL游标多循环一次的问题怎么解决”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“M...

这篇文章主要讲解了“MySQL游标多循环一次的问题怎么解决”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL游标多循环一次的问题怎么解决”吧！

在MySQL中使用游标的时候，发现游标多循环一次
想在表中插入一条语句，但是实际上却插入了两条
语句如下：
mysql> create table test(id int(5));
Query OK, 0 rows affected (0.13 sec)

mysql> delimiter $$
mysql> create procedure proc_drop_table()
    -> BEGIN
    -> /* Declare Variables */
    -> DECLARE done_1 INT DEFAULT FALSE;
    -> DECLARE v_history_table_name varchar(64);
    -> declare the_query VARCHAR(500);
    ->
    -> /* Declare Conditions */
    -> DECLARE not_found CONDITION FOR 1741;
    ->
    -> /* Declare Cursors */
    -> DECLARE cur1 CURSOR FOR select CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME like '%test%';
    ->
    -> /* Declare Exception Handlers, usually with set actions */
    -> /* usually with set actions, the following handler has two forms,
   /*> one with begin .. end statements, and the other without */
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_1 = TRUE;
    ->
    ->     OPEN cur1;
    ->     read_loop_1: LOOP
    ->     FETCH cur1 INTO v_history_table_name;
    -> /*IF done_1 IS FALSE THEN*/
    -> set @v_history_table_name=v_history_table_name;
    ->     select concat('insert into ', @v_history_table_name, ' values (10)') into the_query;
    -> SET @stmt=the_query;
    -> PREPARE STMT FROM @stmt;
    -> EXECUTE STMT;
    -> DEALLOCATE PREPARE STMT;
    -> /*END IF;*/
    ->     IF done_1 THEN
    ->     LEAVE read_loop_1;
    -> END IF;
    -> END LOOP read_loop_1;
    -> CLOSE cur1;
    ->     end$$
Query OK, 0 rows affected (0.01 sec)

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

mysql> call proc_drop_table();
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
+------+
| id   |
+------+
|   10 |
|   10 |
+------+
2 rows in set (0.00 sec)

解决方法：
在实际执行的语句两边增加IF判断

mysql> truncate table test;
Query OK, 0 rows affected (0.07 sec)

mysql> drop procedure proc_drop_table;
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter $$
mysql> create procedure proc_drop_table()
    -> BEGIN
    -> /* Declare Variables */
    -> DECLARE done_1 INT DEFAULT FALSE;
    -> DECLARE v_history_table_name varchar(64);
    -> declare the_query VARCHAR(500);
    ->
    -> /* Declare Conditions */
    -> DECLARE not_found CONDITION FOR 1741;
    ->
    -> /* Declare Cursors */
    -> DECLARE cur1 CURSOR FOR select CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME like '%test%';
    ->
    -> /* Declare Exception Handlers, usually with set actions */
    -> /* usually with set actions, the following handler has two forms,
   /*> one with begin .. end statements, and the other without */
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_1 = TRUE;
    ->
    ->     OPEN cur1;
    ->     read_loop_1: LOOP
    ->     FETCH cur1 INTO v_history_table_name;
    -> IF done_1 IS FALSE THEN
    -> set @v_history_table_name=v_history_table_name;
    ->     select concat('insert into ', @v_history_table_name, ' values (10)') into the_query;
    -> SET @stmt=the_query;
    -> PREPARE STMT FROM @stmt;
    -> EXECUTE STMT;
    -> DEALLOCATE PREPARE STMT;
    -> END IF;
    ->     IF done_1 THEN
    ->     LEAVE read_loop_1;
    -> END IF;
    -> END LOOP read_loop_1;
    -> CLOSE cur1;
    ->     end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc_drop_table();
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)