MySQL游標(biāo)多循環(huán)一次的問(wèn)題怎么解決

這篇文章主要講解了“MySQL游標(biāo)多循環(huán)一次的問(wèn)題怎么解決”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“MySQL游標(biāo)多循環(huán)一次的問(wèn)題怎么解決”吧！

在MySQL中使用游標(biāo)的時(shí)候，發(fā)現(xiàn)游標(biāo)多循環(huán)一次
想在表中插入一條語(yǔ)句，但是實(shí)際上卻插入了兩條
語(yǔ)句如下：
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)

解決方法：
在實(shí)際執(zhí)行的語(yǔ)句兩邊增加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游標(biāo)多循環(huán)一次的問(wèn)題怎么解決”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)MySQL游標(biāo)多循環(huán)一次的問(wèn)題怎么解決這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！