MySQL Stored Procedure - Nested Loop Example @ DanBrother的部落格

MySQL Stored Procedure - Nested Loop Example

DELIMITER $$
CREATE PROCEDURE sp_fix_dup_nickname()
BEGIN
DECLARE outer_done, inner_done BOOLEAN DEFAULT FALSE;
   DECLARE current_id varchar(30);
   DECLARE current_nickname varchar(60);
   DECLARE current_update_time timestamp(6);
   DECLARE current_ranking int;
   DECLARE new_nickname varchar(60);
DECLARE outer_cur CURSOR FOR select nickname from tmp_dup_nickname;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET outer_done = TRUE;

OPEN outer_cur;
outer_cur_loop: LOOP
FETCH FROM outer_cur INTO new_nickname;

IF outer_done THEN
CLOSE outer_cur;
LEAVE outer_cur_loop;
END IF;

BLOCK2: BEGIN
DECLARE inner_cur CURSOR FOR SELECT normal.id as id, normal.nickname as nickname, normal.update_time as update_time, COUNT(lesser.id) AS ranking
       FROM tmp_customer AS normal
       LEFT JOIN tmp_customer AS lesser ON lesser.update_time > normal.update_time AND lesser.nickname = normal.nickname
       WHERE normal.nickname = new_nickname
       GROUP BY id
       order by nickname,ranking;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET inner_done = TRUE;
OPEN inner_cur;
inner_cur_loop: LOOP
FETCH FROM inner_cur INTO current_id, current_nickname, current_update_time, current_ranking;
IF inner_done THEN
set inner_done = false;
CLOSE inner_cur;
LEAVE inner_cur_loop;
END IF;
           IF current_ranking > 0 THEN
           SET current_nickname := CONCAT(CONCAT(current_nickname,'_'),current_ranking);
           END IF;
           -- SELECT concat(concat(concat(concat(current_id, ' / '),current_nickname),' / ',current_update_time)) as result;

           update tmp_customer
           set nickname = current_nickname
           where id = current_id;
END LOOP inner_cur_loop;
END BLOCK2;
END LOOP outer_cur_loop;
END$$
DELIMITER ;

CALL sp_fix_dup_nickname();