mysql存储过程双层嵌套循环

转自：https://blog.csdn.net/qq_37267706/article/details/79679262 

CREATE PROCEDURE `order_attachment_procedure`()
BEGIN
	-- 声明变量
	declare done INT DEFAULT 0;
	declare edone INT DEFAULT 0;

	-- 接受游标查询结果
	declare c_id varchar(50) character set utf8;
	declare c_order_id varchar(50) character set utf8;
	declare c_oss_key varchar(10000) character set utf8;
	declare c_file_name varchar(10000) character set utf8;
	
	declare c_id_temp varchar(50) character set utf8;
	declare c_oss_key_temp varchar(4096) character set utf8;
	declare c_file_name_temp varchar(4096) character set utf8;

	-- 定义游标接受查询结果
	declare cur1 CURSOR FOR 
		SELECT t.c_id,t.c_order_id,t.c_oss_key,t.c_file_name FROM t_order_attachment t;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;#结束标识

	-- 打开游标
	OPEN cur1;
		-- 开始循环
		loop_a:LOOP 
			-- 读取游标数据到参数列表
			FETCH cur1 into c_id,c_order_id,c_oss_key,c_file_name;
		if done=1 then
            leave loop_a;
        end if;
			SET edone = 0;-- !!!!!重置标识
			BEGIN
				-- 定义游标v_file_data以及结束标识
				DECLARE oss_key_name CURSOR FOR 
				SELECT help_topic_id as helpid,
				SUBSTRING_INDEX(SUBSTRING_INDEX(c_oss_key,',',help_topic_id+1),',',-1) as osskey,
				SUBSTRING_INDEX(SUBSTRING_INDEX(c_file_name,',',help_topic_id+1),',',-1)	AS filename 
				FROM mysql.help_topic WHERE help_topic_id < LENGTH(c_oss_key)-LENGTH(REPLACE(c_oss_key,',',''))+1;
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = 1;#结束标识
				OPEN oss_key_name;-- 打开游标
					vfiledataLoop:LOOP -- 循环游标开始
						FETCH oss_key_name INTO c_id_temp,c_oss_key_temp,c_file_name_temp;
						IF edone = 1 THEN
							LEAVE vfiledataLoop;
						ELSE
							-- 对表的操作
							insert into t_order_attachment_temp
							values(CONCAT(UUID_SHORT(),c_id_temp),c_order_id,
							trim(REPLACE(REPLACE(REPLACE(c_oss_key_temp,'"',''),'[',''),']','')),
							trim(REPLACE(REPLACE(REPLACE(c_file_name_temp,'"',''),'[',''),']','')));
						END IF;
					END LOOP;
				CLOSE oss_key_name;-- 关闭游标
			END;
		END LOOP loop_a;
	CLOSE cur1;
END