mysql存储过程--数据库批量更新组织机构树的层级name

数据库根据组织机构树的层级code批量更新组织机构树的层级name

需求描述

组织机构树，每个组织机构都有层级code(level_code)，根据level_code可知其上级组织信息，现在需要根据level_code 获得其level_name，通过存储过程批量更新org表中每个组织机构的level_name，如下图所示结构

MySQL存储过程

CREATE PROCEDURE `updateLevelName`(tenantId VARCHAR(4))
BEGIN
-- 声明变量
DECLARE cid VARCHAR(255);
DECLARE done INT;
DECLARE len INT;
DECLARE i INT;
DECLARE orgName VARCHAR(255);
DECLARE orgName1 VARCHAR(255);
 
-- 创建游标，并设置游标所指的数据（这里设置ID不为1是因为ID为1的是总的大类）
DECLARE cur CURSOR for 
	SELECT level_code as cid from tab_org where del_flag = '0' AND tenant_id = tenantId;
-- 游标执行完，即遍历结束。设置done的值为1
DECLARE CONTINUE HANDLER for not FOUND set done=1;
-- 开启游标
open cur;
-- 执行循环
posLoop:LOOP
-- 如果done的值为1，即遍历结束，结束循环
	IF done=1 THEN
		LEAVE posLoop;
-- 注意，if语句需要添加END IF结束IF
END IF;
-- 从游标中取出cid
FETCH cur INTO cid;

-- 计算每个levelCode的长度len
SELECT LENGTH(level_code) INTO len FROM tab_org WHERE level_code = cid AND del_flag='0' AND tenant_id = tenantId;

-- 初始化orgName为根节点
SET orgName = (SELECT name FROM tab_org WHERE level_code = LEFT(cid,3) AND del_flag='0' AND tenant_id = tenantId);
-- 初始化i的值为6
SET i=6;

WHILE  i<=len DO
SELECT name INTO orgName1 FROM tab_org WHERE level_code = LEFT(cid,i) AND del_flag='0' AND tenant_id = tenantId;
SET orgName = CONCAT(orgName,'-',orgName1);
SET i=i+3;
END WHILE;

-- 更新level_name
UPDATE tab_org SET level_name = orgName WHERE level_code = cid AND del_flag='0' AND tenant_id = tenantId;

-- 关闭循环
END LOOP posLoop;
-- 关闭游标
CLOSE cur;
-- 关闭分隔标记
END