MySQL游标多循环一次的问题怎么处置
发布时间：2022-01-20 14:29:24 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了MySQL游标多循环一次的问题怎么解决，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习MySQL游标多循环一次的问题怎么解决吧！ 在MySQL中使用游标的时候，发现游标多循环一次 想在表中插入
|
这篇文章主要讲解了“MySQL游标多循环一次的问题怎么解决”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“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) 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游标多循环一次的问题怎么解决”的内容了，经过本文的学习后，相信大家对MySQL游标多循环一次的问题怎么解决这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。 （编辑：舟山站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐