CREATE DEFINER=root
@localhost
FUNCTION queryAncestors
(in_id
int) RETURNS varchar(1000) CHARSET utf8mb4
BEGIN
DECLARE ancestorsInfo VARCHAR(1000);
DECLARE temps VARCHAR(1000);
WITH RECURSIVE cte AS (
SELECT 1 as depart_level, sd.org_ecd, sd.spr_org_ecd, sd.org_apltn
FROM std_bpm_organzition sd WHERE org_ecd = in_id
UNION ALL
SELECT (1 + cte.depart_level) as depart_level, sd.org_ecd, sd.spr_org_ecd, sd.org_apltn
FROM std_bpm_organzition sd, cte WHERE sd.org_ecd = cte.spr_org_ecd
)
select GROUP_CONCAT(spr_org_ecd ORDER BY depart_level desc) into ancestorsInfo from cte;
set temps = concat(‘0,’,ancestorsInfo);
RETURN temps;
END
mysql递归函数
最新推荐文章于 2023-05-28 19:01:46 发布