组织树中父子组织路径自动生成_Mysql脚本

DELIMITER //
DROP FUNCTION IF EXISTS orgfun;
CREATE FUNCTION orgfun(prId VARCHAR(32))
RETURNS VARCHAR(1024)
BEGIN
DECLARE opath VARCHAR(1024) DEFAULT '';
DECLARE id VARCHAR(32) DEFAULT '';
DECLARE pid VARCHAR(500) DEFAULT '';
SET id = prId;
WHILE pid is not null DO
	SET id = (SELECT pr_id FROM pr_organize WHERE pr_id = id);
	SET pid = (SELECT pr_parentId from pr_organize WHERE pr_id = id);
	IF pid != '-1' THEN
		SET opath = CONCAT('/',id,opath);
		SET id = pid;
	ELSE
		SET opath = CONCAT('/',id,opath);
		RETURN opath;
	END IF;
END WHILE;
END //

#SELECT orgfun('100010');

DROP FUNCTION IF EXISTS updatePath;
CREATE FUNCTION updatePath()
RETURNS VARCHAR(50)
BEGIN
	DECLARE prId VARCHAR(32) DEFAULT '';
	DECLARE opath VARCHAR(1024) DEFAULT '';
	-- 判断游标是否遍历完全部记录的标记
	DECLARE num INT DEFAULT 0;
	-- 定义游标 将sql结果集赋值到游标中
	DECLARE prid_list CURSOR FOR SELECT pr_id FROM pr_organize;
	-- 当游标遍历完全部记录后将标志变量置成某个值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET num=1;
	-- 打开游标
	OPEN prid_list; 
		FETCH prid_list INTO prId; 
		-- while循环
		WHILE num <> 1 DO
			SET opath = orgfun(prId);
			IF opath is not null THEN
				UPDATE pr_organize SET pr_orgPath = opath where pr_id = prId;
			END IF;
			-- 读取下一行数据
			FETCH prid_list INTO prId;
		END WHILE;
	CLOSE prid_list;

	RETURN 'UPDATE ORGPATH SUCCESS';

END //

SELECT updatePath();

注：

1、现mysql的pr_organize表中含有pr_id(主键，即组织id)、pr_parentId(父级组织id)、pr_orgPath(组织路径)

2、根组织的父级组织以 -1 标识

3、以上脚本可根据子父id关系自动更新表中所有记录的pr_orgPath字段