存储过程双层循环_mysql嵌套存储过程实现循环嵌套

下面是工作中使用到的一个mysql嵌套循环存储过程，因为写的比较少，开始写花费了一点时间，特此记录一下

DELIMITER //

CREATE PROCEDURE handleHisNewCommentData()

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE topId INT;

DECLARE topComments_cur CURSOR FOR SELECT ID

FROM

T_COMMENT

WHERE REPLY_ID = 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN topComments_cur;

data_loop: LOOP

-- 取值 取多个字段

FETCH topComments_cur

INTO topId;

IF done

THEN

LEAVE data_loop;

END IF;

CALL updateCommentRootId(topId, topId);

SELECT topId;

END LOOP;

CLOSE topComments_cur;

END//

CREATE PROCEDURE updateCommentRootId(replyId INT, rootId INT)

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE commentId INT;

DECLARE comments_cur CURSOR FOR SELECT ID

FROM

T_COMMENT

WHERE REPLY_ID = replyId;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN comments_cur;

data_loop: LOOP

-- 取值 取多个字段

FETCH comments_cur

INTO commentId;

IF done

THEN

LEAVE data_loop;

END IF;

UPDATE T_NEW_COMMENT

SET ROOT_ID = rootId

WHERE id = commentId;

CALL updateCommentRootId(commentId, rootId);

END LOOP;

CLOSE comments_cur;

END//

DELIMITER ;

CALL handleHisNewCommentData;

DROP PROCEDURE handleHisNewCommentData;

DROP PROCEDURE updateCommentRootId;