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

158次阅读

共计 2897 个字符，预计需要花费 8 分钟才能阅读完成。

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