欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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