mysql 游标循环,嵌套游标循环

存储过程游标使用方法,嵌套游标使用方法

BEGIN
    -- 这里定义的变量不能和表字段相同（重点）
    DECLARE FID1 int;
    declare MC1 varchar(50);
DECLARE FID2 int;
    declare MC2 varchar(50);
declare `result` varchar(2000);
DECLARE done int;
    declare DataSet_FID cursor for select FID,MC from bmlx; 
    declare DataSet_Department cursor for select FID,MC from jflx;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET result = '';
　　
　　-- 第一个游标循环
    open DataSet_FID;
    out_loop:LOOP
        FETCH DataSet_FID INTO FID1,MC1;
        IF done = 1 THEN
            LEAVE out_loop;
        END IF;

        set result = CONCAT(result,MC1,',');

　　　　　-- 第二个游标循环
        open DataSet_Department;
        inner_loop:LOOP
        FETCH DataSet_Department INTO FID2,MC2;
        IF done = 1 THEN
            LEAVE inner_loop;
        END IF;
        SET result = CONCAT(result,MC2,',');
        end LOOP inner_loop;
        CLOSE DataSet_Department;
SET done=0;-- 注意这里（重点）
        
    END LOOP out_loop;
  CLOSE DataSet_FID;

　select `result`;
END

 

这是两张表的数据

select FID,MC from bmlx;

select FID,MC from jflx;

 

 

 

转载于:https://www.cnblogs.com/phao123/p/6006780.html