MySQL 8游标存储过程测试报错及树形数据实现咨询

一、原存储过程报错原因 #

1. 未处理游标取空场景：原代码未声明NOT FOUND处理器，当游标遍历完所有数据后，FETCH语句会触发1329错误（No data - zero rows fetched）。
2. 变量名冲突：存储过程的OUT参数branch_id、branch_name与内部声明的局部变量同名，导致局部变量覆盖OUT参数的传递逻辑，无法正确返回数据。

二、存储过程调试优化 #

1. 解决多标签输出问题 #

你当前用多个SELECT输出调试信息，每个SELECT会生成独立结果集。要在同一个标签输出所有调试内容，可采用两种方式：

• 用CONCAT_WS合并多字段为一行输出：

  SELECT CONCAT_WS(' | ', '执行步骤:', @step, '分支ID:', branch_id, '分支名称:', branch_name) AS debug_info;
  

• 创建临时表存储调试日志，最后统一查询：

  -- 存储过程开头创建临时表
  CREATE TEMPORARY TABLE IF NOT EXISTS debug_log (step INT, branch_id SMALLINT UNSIGNED, branch_name VARCHAR(100));
  
  -- 调试节点插入日志
  INSERT INTO debug_log VALUES (@step, branch_id, branch_name);
  
  -- 循环结束后查询所有日志
  SELECT * FROM debug_log;
  

2. 实用调试技巧 #

• 使用用户变量（如@step）标记执行阶段，快速定位异常步骤。
• 在FETCH前后输出变量值，确认游标读取的数据是否符合预期。

三、树形数据生成的最优实现思路 #

你的需求是生成分支（一级节点，parent_id为NULL）和部门（二级节点，parent_id为所属分支ID）的树形结构，无需使用游标，直接用UNION ALL合并两张表数据即可，效率更高：

核心实现SQL #

SELECT 
  id, 
  name, 
  NULL AS parent_id 
FROM branches 
WHERE active = 1
-- 可选：过滤特定分支
AND (in_branchId IS NULL OR id = in_branchId)
UNION ALL
SELECT 
  d.id, 
  d.name, 
  d.branch_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 d.branch_id = in_branchId)
ORDER BY parent_id, id;

逻辑说明 #

• 第一部分查询所有激活分支，parent_id设为NULL作为一级节点。
• 第二部分查询所有隶属于激活分支的部门，parent_id设为对应分支ID作为二级节点。
• 用UNION ALL合并结果并排序，直接得到完整树形结构数据。

封装为存储过程（可选） #

如果需要封装成存储过程，直接返回结果集即可，无需OUT参数：

DELIMITER $$
DROP PROCEDURE IF EXISTS getBranchesWithDepartments $$
CREATE PROCEDURE getBranchesWithDepartments(IN in_branchId SMALLINT UNSIGNED)
BEGIN
  SELECT 
    id, 
    name, 
    NULL AS parent_id 
  FROM branches 
  WHERE active = 1
    AND (in_branchId IS NULL OR id = in_branchId)
  UNION ALL
  SELECT 
    d.id, 
    d.name, 
    d.branch_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 d.branch_id = in_branchId)
  ORDER BY parent_id, id;
END$$
DELIMITER ;

调用示例：

CALL getBranchesWithDepartments(NULL); -- 查询所有分支及下属部门
CALL getBranchesWithDepartments(1); -- 查询ID为1的分支及下属部门

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