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