MySQL 8存储过程中合并分支与部门表数据为树形结果集

在MySQL 8环境下，需将Branches（分支）表与Departments（部门）表的数据合并为一个结果集，结果集需包含以下字段：

• id：分支或部门表的ID
• name：分支或部门表的名称
• parent_id：分支的该字段为null，部门的该字段不为null
  此结果集用于构建树形数据，分支为一级节点，部门为二级节点。

现有实现问题 #

我尝试使用2个游标和1个临时表在存储过程中实现该需求，代码如下：

create procedure getBranchesWithDepartments(IN in_branchId smallint unsigned)
BEGIN


    DECLARE branchId smallint unsigned;
    DECLARE branchName varchar(100);
    DECLARE done INT DEFAULT FALSE;

    DECLARE branchesCursor cursor for SELECT branches.id, branches.name -- 1st cursor
                                      FROM branches
                                      WHERE branches.active = 1
                                        AND (in_branchId IS NULL OR branches.id = in_branchId)
                                      ORDER BY branches.id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    DECLARE departmentsCursor cursor for SELECT id, name -- 2nd cursor for any found branch
                            FROM departments
                            WHERE branch_id = :branchId   -- How to get reference to branches.id from above cursor
                                      ORDER BY name;
-- In which way have I to declare CONTINUE HANDLER for 2nd cursor?
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    CREATE TEMPORARY TABLE IF NOT EXISTS BranchesWithDepartmentsTable
    (
        id        bigint unsigned,
        name      varchar(100),
        parent_id bigint unsigned
    );

    DELETE FROM BranchesWithDepartmentsTable;

    open branchesCursor;
    branchesLoop:
    loop
        fetch branchesCursor into branchId, branchName;
        INSERT INTO BranchesWithDepartmentsTable SELECT branchId, branchName, null;

        open departmentsCursor; -- But how to pass branches.id parameter from cursor above ?
        fetch departmentsCursor into branchId, branchName; -- internal cursor

        IF done THEN
            LEAVE branchesLoop;
        END IF;

    end loop branchesLoop;
    close branchesCursor;

    SELECT branchId, branchName, parent_id from BranchesWithDepartmentsTable;

END;

目前遇到的问题：

• 如何将第一个游标中获取的分支ID传递给第二个游标？
• 如何为第二个游标声明CONTINUE HANDLER？

解决方案 #

推荐实现：使用UNION ALL（简洁高效） #

无需游标和临时表，直接通过SQL联合查询即可完成需求，性能更高且代码易维护：

CREATE PROCEDURE getBranchesWithDepartments(IN in_branchId SMALLINT UNSIGNED)
BEGIN
    -- 查询符合条件的分支（一级节点，parent_id为null）
    SELECT 
        id,
        name,
        NULL AS parent_id
    FROM branches
    WHERE active = 1
      AND (in_branchId IS NULL OR id = in_branchId)
    UNION ALL
    -- 查询对应分支下的部门（二级节点，parent_id为分支ID）
    SELECT 
        d.id,
        d.name,
        b.id AS parent_id
    FROM departments d
    JOIN branches b ON d.branch_id = b.id
    WHERE b.active = 1
      AND (in_branchId IS NULL OR b.id = in_branchId)
    ORDER BY parent_id, id;
END;

游标版实现（解决原问题） #

如果必须使用游标，需注意为不同游标定义独立的状态变量，避免冲突，同时在分支循环内动态绑定部门查询条件：

CREATE PROCEDURE getBranchesWithDepartments(IN in_branchId SMALLINT UNSIGNED)
BEGIN
    DECLARE branch_id_var SMALLINT UNSIGNED;
    DECLARE branch_name_var VARCHAR(100);
    DECLARE dept_id_var BIGINT UNSIGNED;
    DECLARE dept_name_var VARCHAR(100);
    DECLARE branch_done BOOLEAN DEFAULT FALSE;
    DECLARE dept_done BOOLEAN DEFAULT FALSE;

    -- 声明分支游标
    DECLARE branches_cursor CURSOR FOR
        SELECT id, name
        FROM branches
        WHERE active = 1
          AND (in_branchId IS NULL OR id = in_branchId)
        ORDER BY id;

    -- 分支游标的NOT FOUND处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET branch_done = TRUE;

    -- 创建并清空临时表
    CREATE TEMPORARY TABLE IF NOT EXISTS BranchesWithDepartmentsTable (
        id BIGINT UNSIGNED,
        name VARCHAR(100),
        parent_id BIGINT UNSIGNED
    );
    TRUNCATE TABLE BranchesWithDepartmentsTable;

    -- 遍历分支
    OPEN branches_cursor;
    branches_loop: LOOP
        FETCH branches_cursor INTO branch_id_var, branch_name_var;
        IF branch_done THEN
            LEAVE branches_loop;
        END IF;

        -- 插入分支数据
        INSERT INTO BranchesWithDepartmentsTable (id, name, parent_id)
        VALUES (branch_id_var, branch_name_var, NULL);

        -- 内部块处理当前分支的部门数据
        BEGIN
            -- 声明当前分支对应的部门游标
            DECLARE depts_cursor CURSOR FOR
                SELECT id, name
                FROM departments
                WHERE branch_id = branch_id_var
                ORDER BY name;

            -- 部门游标的NOT FOUND处理器
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET dept_done = TRUE;

            OPEN depts_cursor;
            depts_loop: LOOP
                FETCH depts_cursor INTO dept_id_var, dept_name_var;
                IF dept_done THEN
                    LEAVE depts_loop;
                END IF;

                -- 插入部门数据
                INSERT INTO BranchesWithDepartmentsTable (id, name, parent_id)
                VALUES (dept_id_var, dept_name_var, branch_id_var);
            END LOOP depts_loop;
            CLOSE depts_cursor;
            SET dept_done = FALSE; -- 重置部门游标状态，避免影响下一次循环
        END;
    END LOOP branches_loop;
    CLOSE branches_cursor;

    -- 返回结果并清理临时表
    SELECT id, name, parent_id FROM BranchesWithDepartmentsTable;
    DROP TEMPORARY TABLE IF EXISTS BranchesWithDepartmentsTable;
END;

关键说明 #

• UNION ALL版：直接通过SQL联合查询完成，无需复杂的游标逻辑，执行效率更高，代码更易维护
• 游标版：
  • 为分支和部门游标分别定义独立的NOT FOUND状态变量（branch_done和dept_done），避免状态冲突导致循环异常
  • 将部门游标声明放在分支循环的内部块中，确保每次循环都能使用当前分支的ID作为查询条件
  • 每次遍历完部门游标后重置dept_done状态，保证下一次分支遍历正常执行

内容的提问来源于stack exchange，提问作者Petro Gromovo