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

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

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;