MYSQL 自定义函数递归查出部门及下属部门

通过部门ID查询子部门

例如:select  getDepartmentListName(1);getDepartmentListName:函数名；1：参数

获取结果：顶级部门/一级子部门/二级子部门/三级子部门/四级部门

DELIMITER $$

USE `sporch`$$

DROP FUNCTION IF EXISTS `getDepartmentListName`$$

CREATE DEFINER=`sporch`@`%` FUNCTION `getDepartmentListName`(depId INT) RETURNS VARCHAR(1000) CHARSET utf8

BEGIN

DECLARE depName VARCHAR(1000) DEFAULT '';

DECLARE tmpName VARCHAR(1000) DEFAULT '';

SET @depId = depId;

#循环递归条件

WHILE @depId IS NOT NULL AND @depId > 0 DO

SELECT departmentPid,departmentName INTO @depId,tmpName FROM t_department WHERE departmentId=@depId;

IF tmpName IS NULL OR tmpName = '' THEN

SET @depId = NULL;

ELSE

#判断是否为空

IF tmpName != '' THEN

SET depName = CONCAT(tmpName,'/',depName);

END IF;

#退出

IF @depId = 0 THEN

SET @depId = NULL;

END IF;

END IF;

END WHILE;

IF depName IS NOT NULL AND LENGTH(depName) > 2 THEN

SET depName = REVERSE(SUBSTR(REVERSE(depName),2));

END IF;

RETURN depName;

END$$

DELIMITER ;