函数
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