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

矫情吗；* 2022-05-10 12:10 635阅读 0赞

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