MySQL 存储过程中使用游标

 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 FOR
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 ;