函数递归

CREATE DEFINER=`root`@`%` FUNCTION `queryParentAreaInfo`(areaId INT) RETURNS varchar(4000) CHARSET utf8mb4
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);
SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT parent_id INTO sTempChd FROM sys_dept WHERE dept_id = sTempChd;
WHILE sTempChd <> 0 DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT parent_id INTO sTempChd FROM sys_dept WHERE dept_id = sTempChd;
END WHILE;

RETURN sTemp;
END

CREATE DEFINER=`root`@`%` FUNCTION `queryAreaInfo`(areaId INT,levelId INT,returnId INT) RETURNS varchar(4000) CHARSET utf8mb4
BEGIN
DECLARE resultStr VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET resultStr ='';

if returnId = 0 then
	SELECT `name` INTO resultStr from sys_dept where FIND_IN_SET(dept_id,queryParentAreaInfo(areaId)) AND dept_level = levelId;
end if;

if returnId = 1 then
	SELECT dept_id INTO resultStr from sys_dept where FIND_IN_SET(dept_id,queryParentAreaInfo(areaId)) AND dept_level = levelId;
end if;

set sTempChd = resultStr;


RETURN resultStr;
END