189 8069 5689
这篇文章主要讲解了“MySQL游标多循环一次的问题怎么解决”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL游标多循环一次的问题怎么解决”吧！
10年积累的成都网站制作、网站设计经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先网站设计后付款的网站建设流程，更有浮梁免费网站建设让你可以放心的选择与我们合作。
在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)
感谢各位的阅读，以上就是“MySQL游标多循环一次的问题怎么解决”的内容了，经过本文的学习后，相信大家对MySQL游标多循环一次的问题怎么解决这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！