mysql递归插入

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES departments(id)
);

DELIMITER //

CREATE PROCEDURE InsertDepartment(IN p_name VARCHAR(255), IN p_parent_id INT)
BEGIN
    DECLARE v_parent_name VARCHAR(255);
    
    -- 插入新部门
    INSERT INTO departments (name, parent_id) VALUES (p_name, p_parent_id);
    
    -- 获取上级部门的名称（如果存在）
    SELECT name INTO v_parent_name FROM departments WHERE id = p_parent_id;
    
    -- 更新上级部门的某些信息（示例：更新部门数量）
    UPDATE departments SET department_count = department_count + 1 WHERE id = p_parent_id;
    
    -- 如果上级部门存在，则递归调用此存储过程
    IF v_parent_name IS NOT NULL THEN
        CALL InsertDepartment(p_name, (SELECT parent_id FROM departments WHERE name = v_parent_name));
    END IF;
END //

DELIMITER ;