MySQL 递归函数(亲测可用)

navicat在创建函数时,需要先指定他的参数和返回值

• 向下递归函数
• 看SQL预览这一栏,areaId是我们作为外部传入的参数,returns varchar(4000) 就是返回值类型,因自己使用的是navicat都需要在创建函数前期就设置好

CREATE DEFINER=`examination2021`@`%` FUNCTION `queryChildrenOrgInfo`(`areaId` int) 
RETURNS varchar(4000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS SIGNED);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(ORG_ID) INTO sTempChd FROM tbl_org_info WHERE FIND_IN_SET(parentOrgId,sTempChd)>0;
END WHILE;
RETURN sTemp;
END

函数输出结果

• 向上递归函数

CREATE DEFINER=`lishui2020`@`%` FUNCTION `queryParentOrgInfo`(`areaId` int) 
RETURNS varchar(4000) CHARSET utf8
BEGIN	
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

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

SELECT parentOrgId INTO sTempChd FROM tbl_org_info WHERE org_id = sTempChd;
WHILE sTempChd <> 0 DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT parentOrgId INTO sTempChd FROM tbl_org_info WHERE org_id = sTempChd;
END WHILE;
RETURN sTemp;
END

函数输出结果