mysql游标LOOP方式循环和嵌套循环

66 阅读 0 评论 44 点赞

我是靠谱客的博主真实荔枝，最近开发中收集的这篇文章主要介绍mysql游标LOOP方式循环和嵌套循环，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1. LOOP方式循环

DELIMITER $$
USE mydb $$
DROP PROCEDURE IF EXISTS `test`$$

CREATE DEFINER=`dev`@`192.168.%` PROCEDURE `test`()
BEGIN
	DECLARE userId INT;
	DECLARE done INT DEFAULT 0;

	DECLARE userIds CURSOR FOR
		SELECT id FROM user;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 溢出处理
    
	OPEN userIds; -- 打开游标
  	testLoop:LOOP
		FETCH userIds INTO userId;

		IF done = 1
			THEN LEAVE testLoop; -- 退出循环
		ELSE
			... -- 循环体
		END IF;
	END LOOP;
	CLOSE userIds; -- 关闭游标
END$$
DELIMITER;

2. LOOP方式嵌套循环

DELIMITER $$

DROP PROCEDURE IF EXISTS test $$
CREATE PROCEDURE mydb.test()

BEGIN
    DECLARE done INT DEFAULT 0;

    DECLARE tmpId INT(10);
    DECLARE tmpTheme VARCHAR(10);
    DECLARE tmpGrade TINYINT(3);
	
	-- 游标1
    DECLARE userIds CURSOR FOR
        SELECT id FROM user;
	
	-- 游标2
    DECLARE scores CURSOR FOR
        SELECT id,theme,grade FROM score;
		
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN userIds;
    firstLoop:LOOP -- 循环外层游标
        FETCH userIds INTO userId;
			
        IF done = 1
            THEN LEAVE firstLoop;
        ELSE
            ... //循环体

            OPEN scores;
            secondLoop:LOOP -- 内层循环
                FETCH scores INTO tmpId,tmpTheme,tmpGrade;
				
                IF
                    done = 1 THEN LEAVE secondLoop;
                ELSE
                    ... //循环体					
                END IF;
				
            END LOOP secondLoop;
            CLOSE rels;

            SET done = 0; -- 注意这个别漏了
        END IF;
    END LOOP firstLoop;
    CLOSE accountIds;
END$$

DELIMITER ;