Mysql 两个游标嵌套迭代操作

BEGIN
DECLARE goodsId,cate_id int(10) default 0;
DECLARE done INT (10) DEFAULT 1;
DECLARE cate_name VARCHAR(255) default "";
DECLARE cate_name_b VARCHAR(255) default "";
-- 定义第一个游标
DECLARE goods CURSOR for select GOODS_ID from cdec.ecm_goods ;
-- 定义第二个游标 该游标中有declare 中定义的参数作为查询条件
DECLARE cate CURSOR FOR
SELECT
g.cate_id cate_id, c.cate_name cate_name
FROM
cdec.ecm_category_goods g
LEFT JOIN cdec.ecm_gcategory c ON g.CATE_ID = c.CATE_ID
WHERE
g.goods_id = goodsId AND g.store_id = 0
ORDER BY
g.cate_id ASC;
-- 定义handler 定义此项是为了判断游标是否循环到最后
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
-- 打开游标goods
open goods ;
-- 对goods 进行循环,并将值付给变量goodsId
goods_loop:loop
-- 取出goodsid 开始循环
set done = 0;
fetch goods into goodsId;
-- 如果循环到最后一个,结束循环
if done=1 THEN
leave goods_loop;
end if;
set cate_name_b = "";
-- 打开第二个游标
open cate ;
cate_loop:loop
set done = 0;
FETCH cate into cate_id,cate_name;
if done=1 THEN
leave cate_loop;
end if;
-- 对第二个游标进行操作 并拼接字符串
set cate_name_b = concat(cate_name_b,cate_name," ");
-- 结束里层循环
end loop cate_loop;
CLOSE cate;
-- 更新操作
update ecm_goods set cate_name_b =cate_name_b where goods_id =goodsId;
-- 结束最外层循环
end loop goods_loop;
-- 关闭第一个游标
CLOSE goods;
END