MYSQL存储过程：递归查询父节点，并更新数据

业务描述：机构向下管理，需要获取每个机构的所有上级节点，并得到对应的级别

效果，如图：

执行前：

执行后：

存储过程：

BEGIN  

    DECLARE fid varchar(100) default '';   

    DECLARE str varchar(100000) default '';   

        DECLARE level_m INTEGER DEFAULT 0;

    DECLARE rootId varchar(100) default '';

        DECLARE rootId_mid varchar(100) default '';

    -- 创建自定义控制游标循环变量  

    DECLARE done TINYINT DEFAULT FALSE;  

    -- 创建自定义游标 并输入结果集  

    DECLARE my_cursor CURSOR FOR SELECT comcode from xj_prpdcompany ;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  

        

        DROP table if exists str_spilt_result;

        CREATE TEMPORARY TABLE str_spilt_result(`id` int(11) NOT NULL AUTO_INCREMENT,code VARCHAR(20),cname VARCHAR(10000),level_n int(4),PRIMARY KEY (`id`));   

   

    -- 打开游标  

    OPEN my_cursor;  

    -- 循环开始  

    my_loop: LOOP  

        FETCH NEXT FROM my_cursor INTO rootId;

                IF done THEN  

                        LEAVE my_loop;  

                END IF;   

                set str=rootId;

                set rootId_mid=rootId;

                set level_m=0;

                -- 这里做你想做的循环的事件

                WHILE rootId_mid !='' and rootId is not NULL  do  

                        SET fid =(SELECT supercomcode FROM xj_prpdcompany WHERE comcode = rootId_mid);   

                        IF fid is not null THEN   

                                SET str = concat(str, ',', fid);   

                                set level_m =level_m+1;

                        END IF;   

                        SET rootId_mid = fid;

                           

                END WHILE;

                

            

                insert into str_spilt_result(code,cname,level_n) values(rootId,str,level_m);

             

    END LOOP my_loop;  

      COMMIT;

    CLOSE my_cursor;    -- 关闭游标  

    

        select * from str_spilt_result;

        update xj_prpdcompany p,str_spilt_result t set p.supercomcode=t.cname,p.`level`=t.level_n where p.comcode=t.code;

        COMMIT;

END