Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL cursor multi-loop problem how to solve", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL cursor multi-loop problem how to solve" it!

When using cursors in MySQL, you find that cursors are looped multiple times

Wanted to insert a statement into the table, but actually inserted two

The statement is as follows:

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)

Solution:

Add IF judgment on both sides of the actually executed statement

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)

Thank you for your reading, the above is the content of "how to solve the problem of multiple loops of MySQL cursors". After the study of this article, I believe you have a deeper understanding of how to solve the problem of multiple loops of MySQL cursors, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.