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

181次閱讀

共計 2897 個字符，預(yù)計需要花費 8 分鐘才能閱讀完成。

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

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

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

正文完