mysql 获取组织路径

在日常需求中，经常需要获取组织路径，比如给你一个区县的名称，得到的组织路径中国-某省-某市-某区;

CREATE DEFINER=`bsm_pd`@`%` FUNCTION `f_name`(`org_id` BIGINT) RETURNS text CHARSET utf8 COLLATE utf8_bin
    COMMENT '获取组织路径'
BEGIN
	DECLARE fullName text;
	DECLARE parentId BIGINT;
	DECLARE parentName text;
	DECLARE fullid text DEFAULT org_id;
	
	set fullName = (SELECT t.org_name FROM t_org_table t WHERE t.org_id = org_id);
	set parentId = (SELECT t.p_org_id FROM t_org_table t WHERE t.org_id = org_id);

	WHILE parentId<>0 and find_in_set(parentId, fullid)<>1 DO
	    set parentName = (SELECT t.org_name FROM t_org_table t WHERE t.org_id = parentId);
            set fullName = CONCAT(parentName,",",fullName);
	    set fullid = CONCAT(parentId,",",fullid);
	    set parentId = (SELECT t.p_org_id FROM t_org_table t WHERE t.org_id = parentId);

        END WHILE;
	
	RETURN fullid;
  END