mysql函数获取全路径

函数

DROP FUNCTION IF EXISTS GetFullPath;
 
CREATE FUNCTION GetFullPath(InputDeptId char(36)) 
RETURNS VARCHAR(1000)
BEGIN
declare fullPath VARCHAR(1000) default '';
declare parentId char(36) default '';
declare num int default 0;

select concat(`Name`,'/',fullPath),UpperId into fullPath,parentId 
from MDM_Dept 
where DeptId=InputDeptId;

WHILE parentId<>'' DO
	select count(1) into num from MDM_Dept where DeptId=parentId;
	if num>0 then 
		select concat(`Name`,'/',fullPath),UpperId into fullPath,parentId 
		from MDM_Dept 
		where DeptId=parentId;
	else
		set parentId='';
	end if;
END WHILE;

return fullPath; 
END;

调用

select GetFullPath(DeptId) fullPath,DeptId,UpperId,`Name` from MDM_Dept