MySQL游标嵌套循环存储过程
程序员文章站
2022-06-10 19:58:01
...
BEGIN DECLARE _uuid VARCHAR(60); DECLARE _labels varchar(100); DECLARE done int DEFAULT 0; DECLARE _result VARCHAR(200); DECLARE _name VARCHAR(60); DECLARE token int DEFAULT 0; #定义游标 DECLARE rs_cursor CURSOR FOR SELECT uuid FROM product; #游标结束条件 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; #打开游标 OPEN rs_cursor; cursor_loop:LOOP FETCH rs_cursor INTO _uuid; IF done = 1 THEN LEAVE cursor_loop; END IF; SET _result='',_name=''; BEGIN DECLARE rs_cursor_item CURSOR FOR select f.name as name from func_prod_map p LEFT JOIN product_func f on p.func_uuid = f.uuid WHERE p.product_uuid = _uuid; #游标结束条件 DECLARE CONTINUE HANDLER FOR NOT FOUND SET token=1; OPEN rs_cursor_item; REPEAT FETCH rs_cursor_item INTO _name; #SELECT _name; IF token <> 1 THEN SET _result = CONCAT(_result,_name,','); END IF; #UNTIL token=1 END REPEAT ; UNTIL token = 1 END REPEAT; CLOSE rs_cursor_item; SET token=0; UPDATE product SET labels = _result WHERE uuid = _uuid; SET _result='',_name=''; END; END LOOP; CLOSE rs_cursor; END