MYSQL 自定义函数递归查出部门层级名称显示

本文主要是介绍MYSQL 自定义函数递归查出部门层级名称显示，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

通过部门ID查询层级显示名称。

如：select  getDepartmentListName(154)

测试部门/一级子部门/二级子部门/三级子部门/四级部门

 

DELIMITER $$USE `sporch`$$DROP FUNCTION IF EXISTS `getDepartmentListName`$$CREATE DEFINER=`sporch`@`%` FUNCTION `getDepartmentListName`(depId INT) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN#SHOW OPEN TABLES WHERE In_use > 0;#SHOW PROCESSLIST#KILL {id}DECLARE depName VARCHAR(1000) DEFAULT '';DECLARE tmpName VARCHAR(1000) DEFAULT '';SET @depId = depId;#循环递归WHILE @depId IS NOT NULL AND @depId > 0  DOSELECT  departmentPid,departmentName INTO  @depId,tmpName FROM  t_department WHERE departmentId=@depId;IF tmpName IS NULL OR tmpName = '' THENSET @depId = NULL;ELSE #判断是否为空IF tmpName != ''  THENSET depName = CONCAT(tmpName,'/',depName); END IF;#退出IF @depId = 0 THENSET @depId = NULL;END IF;END IF;END WHILE;IF depName IS NOT NULL AND LENGTH(depName) > 2 THENSET depName = REVERSE(SUBSTR(REVERSE(depName),2));END IF;RETURN depName;
END$$DELIMITER ;

 

 

这篇关于MYSQL 自定义函数递归查出部门层级名称显示的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---