在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遊標多迴圈一次的解決方法

相關文章