Use a cursor in the MySQL Stored Procedure

1 DELIMITER $
2
3 USE 'newhome _ db' $
4
5 drop procedure if exists 'get _ join_num '$
6
7 create procedure 'get _ join_num '()
8 BEGIN
9 DECLARE flag INT (11) DEFAULT 0;
10 DECLARE hd_id INT (11 );
11 DECLARE join_number INT (11 );
12 DECLARE my_cursor CURSOR
13 SELECT huodong_id, COUNT (*) FROM 'loupan _ collect 'WHERE category = 2 group by huodong_id;
14 declare continue handler for not found set flag = 1;
15 SET flag = 0;
16 OPEN my_cursor;
17 repeat fetch my_cursor INTO hd_id, join_number;
18 UPDATE 'loupan _ activity_basic 'SET join_num = join_number WHERE act_id = hd_id;
19 -- select hd_id, join_number;
20 UNTIL flag = 1 end repeat;
21 CLOSE my_cursor;
22 END $
23
24 DELIMITER;