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)