mysql 中创建递归函数

1.在navicat中创建递归函数

BEGIN   
DECLARE fname VARCHAR(8000) DEFAULT '';
DECLARE strname varchar(8000) default '';   
DECLARE fid varchar(100) default '';   
DECLARE str varchar(1000) default rootId;   
 DECLARE indexOrer INT DEFAULT 0; 
WHILE rootId is not null  do    
    SET fid =(SELECT parentId FROM uc_organization WHERE id = rootId);   
 SET fname =(SELECT name FROM uc_organization WHERE id = rootId); 
    IF fid is not null THEN   
set strname=CONCAT(strname,',',fname);
        SET str = concat(str, ',', fid);   
        SET rootId = fid;   
    ELSE   
        SET rootId = fid;   
    END IF;   
END WHILE;

set strname=REPLACE(strname,',集团','');
set strname=TRIM(BOTH ',' FROM strname);
return strname;  
END

 参数:rootId varchar(1000)  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

返回类型:varchar(1000)

类型:FUNCTION

posted @ 2017-11-15 13:24  甜菜波波  阅读(959)  评论(0编辑  收藏  举报