【MySQL】MySQL层级数据的递归遍历
/* 根据ID获取级联编码路径 */ CREATE FUNCTION `f_org_recursion`(v_id INTEGER
) RETURNS VARCHAR(1024)
BEGIN
/* 编码 */
DECLARE v_code VARCHAR(1024);
/* 编码路径 */
DECLARE v_cascade_code VARCHAR(1024);
/* 父节点ID */
DECLARE v_parent_id INT;
/* 递归操作的层次 */
DECLARE v_recursion_level INT;
SET v_cascade_code = '';
SET v_recursion_level = 0;
/* 查询本节点代码、和父节点ID */
SELECT t.code, t.parent_id INTO v_code, v_parent_id FROM t_organ t WHERE t.id = v_id;
/* 是否为空 */
IF v_code IS NULL THEN
RETURN CONCAT('no record for> ELSE
SET v_cascade_code = CONCAT('', v_code);
END IF;
/* 递归拼接代码 */
WHILE v_parent_id IS NOT NULL DO
/* 递归层次限制，避免死循环 */
SET v_recursion_level = v_recursion_level + 1;
IF v_recursion_level > 20 THEN
RETURN 'Recursion out of 20 times';
END IF;
/* 以父ID为ID查询 */
SELECT t.code, t.parent_id INTO v_code, v_parent_id FROM t_organ t WHERE t.id = v_parent_id;
/* 是否为空 */
IF v_code IS NULL THEN
RETURN CONCAT('no record for parent> ELSE
SET v_cascade_code = CONCAT(v_code, '-', v_cascade_code);
END IF;
END WHILE;
RETURN v_cascade_code;
END
页:
[1]