单个处理数据祖籍列表层级关系

CREATE DEFINER=`root`@`%` FUNCTION `sys_organization_getAncestorsNames`(deptId varchar(36)) RETURNS varchar(1000) CHARSET utf8DETERMINISTIC
BEGINDECLARE parentDeptId varchar(36) default '';  -- 父部门iddeclare parentDeptName varchar(100) default ''; -- 父部门名称declare currentDeptName varchar(100) default ''; -- 当前部门名称declare retStr varchar(1000) default '';    --  返回字符串（部门1/部门1的子部门1/部门1的子部门2）declare deptName varchar(2000) default '';## 获取当前部门名称if deptId is not null and length(deptId) > 0 thenset currentDeptName = (select ifnull(organization_name, '') from sys_organization where uuid = deptId);elsereturn retStr;end if;WHILE deptId is not null and deptId <> '1'doSET parentDeptId = (SELECT parent_id FROM sys_organization WHERE uuid = deptId);IF parentDeptId is not null and parentDeptId <> '1'   THENset parentDeptName = (select organization_name from sys_organization td where td.uuid = parentDeptId);if parentDeptName is not null thenset deptName = concat(parentDeptName,'/', deptName);end if;SET deptId = parentDeptId;ELSESET deptId = parentDeptId;END IF;END WHILE;-- 将当前部门名称 拼接到最后set retStr = concat(deptName,currentDeptName);return retStr;
END

执行完之后在执行
update sys_organization set ancestors_names= sys_organization_getAncestorsNames(uuid);
即可修改表中所有数据 括号中（uuid）为表的主键