mysql存储过程-递归赋值国家地区

申明：下列存储过程适合表已经有数据，需要为某字段赋值（国，省，市，区，县等）数据时使用。

1.创建regionAdd存储过程，查询国表信息传入参数到reg存储过程中
/*删除存储过程regionAdd*/
DROP PROCEDURE regionAdd;
/*创建存储过程regionAdd*/
create PROCEDURE regionAdd()
/*开始存储过程*/
BEGIN
    DECLARE id int;    /*接收国id*/
    DECLARE full_id VARCHAR(100); /*接收国id*/
    declare full_name VARCHAR(100);/*接收国名*/
    DECLARE b int default 0;/*定义异常参数*/
    /*创建游标接收查询的country表的列值*/
    DECLARE pid CURSOR for select c.id,c.id,c.zh_name from country c;
    /*定义异常，游标的数据为空时异常，异常参数修改为b 修改为1*/
    DECLARE CONTINUE HANDLER for not FOUND set b = 1;
    /*打开游标*/
    OPEN pid;
    /*读取第一行数据存储到pid中*/
    FETCH pid into id,full_id,full_name;
    /*开始循环，数据不为空的时候循环（b=1时游标数据为空）*/
    WHILE b <> 1 DO
    /*调用reg存储过程，传入，国id，国id，国名，国id*/
            call reg(id,full_id,full_name,id);
    /*读取下一条信息*/
            FETCH pid into id,full_id,full_name;
    /*结束循环*/
    end while;
    /*关闭游标*/
    CLOSE pid;
    /*结束存储过程*/
END;



/*删除reg存储过程*/
DROP PROCEDURE reg;
/*创建reg存储过程，参数为，地区父id，地区父id全（1，11,112），地区父name全（中国，北京市，朝阳区），国id*/
create PROCEDURE reg(IN pid int,in fullId VARCHAR(100),in fullName VARCHAR(100),in countryId int)
/*开始存储过程*/
BEGIN

        declare  dids int;/*接收当前地区id*/
        declare  rnames VARCHAR(100);/*接收当前地区名*/
        declare  fullids VARCHAR(100);/*存储当前id全（1，11,112）*/
        declare  fullnames VARCHAR(100);/*存储当前地区名全（中国，北京市，朝阳区）*/
        declare  b int default 0; /*定义异常参数*/
        /*定义游标regionup，查询地区，id，区名，id全，取名全*/
        DECLARE regionup CURSOR for select r.id,r.name,r.full_id,r.full_name from region r where r.parent_id = pid;
        /*定义异常，游标的数据为空时异常，异常参数修改为b 修改为1*/
        DECLARE CONTINUE HANDLER for not FOUND set b = 1;
        /*开始游标*/
        OPEN regionup;
        /*读取游标中第一行数据*/
        FETCH regionup into dids,rnames,fullids,fullnames;
        /*开始循环，数据不为空的时候循环（b=1时游标数据为空）*/
        WHILE b <> 1 DO
            /*获取当前地区全名，父类地区全名+当前地区名*/
                set fullnames = concat(fullName,',',rnames);
            /*获取当前id全，父类id全+当前id*/
                set fullids = concat(fullId,',',dids);
            /*修改当前地区全，di全，国id*/
                update region res set  res.full_id = fullids , res.full_name = fullnames ,res.country_id = countryId  where res.id = dids;
            /*递归调用自己，传入，当前区id，当前区id全，当前国全名，国id*/
                call reg(dids,fullids,fullnames,countryId);
            /*读取下一条信息*/ 
        FETCH regionup into dids,rnames,fullids,fullnames;
        /*结束循环*/
        end while;
        /*关闭游标*/
        CLOSE regionup;
/*结束存储过程*/
end;


2.调用存储过程，调用前需要设置循环深度
/*
这个修改课涉及到全局和session级修改,全局修改的话 需要 有super权限： set global max_sp_recursion_depth=2, session级修改的话只对当前连接有效。不需要加global
你可以执行 show privileges 查看权限列表，找到super
*/
SET global  max_sp_recursion_depth=25;/*深度自定义，不定义将不能实现递归*/
call regionAdd;/*调用存储过程*/