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
函数输出结果