MySQL游标多循环一次的解决方法

忘是亡心i 2024-04-18 00:32 55阅读 0赞

在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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2134241/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2134241/