MySQL存储过程使用临时表返回结果集

技术前提与避坑指南

在使用存储过程进行开发前，需要了解 MySQL 数据库在处理此类逻辑时的特性。虽然利用游标遍历数据行是常见做法，但必须注意以下几点限制与建议：

• 游标的性能瓶颈：在 MySQL 中，逐行处理的游标效率较低，通常远低于基于 Set（集合）的 SQL 语句。除非涉及极其特殊的逐行逻辑且无法用集合操作替代，否则一般不建议在高频查询中使用游标。Oracle 等数据库对游标的优化较好，但在 MySQL 中应极力避免。
• 动态 SQL 的局限性：为了避免存储过程代码过于臃肿或难以维护，建议尽量减少使用字符串拼接方式构建动态 SQL。对于参数是否为空的判断，推荐使用 OR 运算符配合空值判定来简化单表过滤条件，例如：(TTB.office_id = IN_Office_id OR IN_Office_id IS NULL OR IN_Office_id = '')。
• 临时表的适用范围：临时表主要用于当前会话期间的临时数据存储，不适合用于多用户共享且频繁读取的全局报表缓存。若需高性能报表，应考虑专门的OLAP引擎或物化视图。
• 更优的替代方案：大多数情况下，直接编写包含多表连接的复杂查询语句（JOIN），或通过应用层多次查询组装数据，往往比在存储过程中拼接逻辑更高效且易读。下文提供的示例旨在说明“如何实现”，在实际生产环境中，请将其视为反面教材或特定场景下的妥协方案进行参考。

基础示例：利用游标与临时表返回简单结果集

为了实现返回结果集，一个典型的流程是：创建临时表 -> 打开游标读取原始数据 -> 循环插入临时表 -> 关闭游标 -> 查询临时表返回结果。

第1步：定义变量与游标结构

首先在存储过程中声明接收数据的变量以及游标句柄。同时需要设置一个终止标记（Handler），以便游标到达最后一行后能正确退出循环。

DECLARE s INT DEFAULT 0;
DECLARE cursor_name CURSOR FOR SELECT id, name FROM user;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;

第2步：创建临时表并准备写入环境

为确保每次执行时数据结构一致，先 DROP 再 CREATE 临时表，或者使用 CREATE TABLE IF NOT EXISTS 保护已有结构。定义字段以匹配目标数据结构。

DROP TABLE IF EXISTS user_temporary;
CREATE TEMPORARY TABLE IF NOT EXISTS user_temporary (
	id VARCHAR(64) PRIMARY KEY,
	user_name VARCHAR(20)
); 

第3步：打开游标并执行循环处理

这是核心逻辑部分。使用 FETCH ... INTO 获取一行数据存入变量，利用 `WHILE s <> 1` 进行迭代，并将数据 INSERT 进临时表。务必注意在处理完所有数据后关闭游标。

-- 省略前面的变量声明... OPEN cursor_name; 
FETCH cursor_name INTO a, b; -- a为id, b为用户名

WHILE s <> 1 DO 
    INSERT INTO user_temporary(id, user_name) VALUES(a, b);
    FETCH cursor_name INTO a, b;
END WHILE;

CLOSE cursor_name ;

第4步：输出最终结果集

所有数据处理完毕后，直接针对临时表执行 SELECT 即可将结果返回给调用方。此外，请务必确保存储过程的类型是 PROCEDURE 而非 FUNCTION。

SELECT * FROM user_temporary;

上述逻辑运行后的标准输出效果如下：

进阶实战：结合动态SQL处理复杂业务统计

真实的业务场景中，可能需要针对每条记录进行多维度的统计（如：某时间段内的建单数、签单数、交流次数及销售额）。由于统计条件（如时间范围、状态标志）可能是动态传入的参数，我们需要在循环内部结合预处理的动态 SQL 进行统计。

第1步：初始化高级临时表

定义包含 ID、姓名、公司、各项指标总数的宽表临时结构。

DROP TABLE IF EXISTS user_temporary;
CREATE TEMPORARY TABLE IF NOT EXISTS user_temporary (
	id VARCHAR(64) PRIMARY KEY,
	user_name VARCHAR(20),
	company_name VARCHAR(20),
	worksheet_num INTEGER,
	sign_worksheet_num INTEGER,
	exchange_num INTEGER,
	sales_volume DOUBLE(20,2)
); 

第2步：声明复杂变量的游标遍历循环

声明用于保存游标读取出的多个字段的变量，并在循环中初始化全局会话变量（如 @userId, @beginDate 等），为后续拼接 SQL 做准备。

DECLARE s INT DEFAULT 0;
DECLARE cursor_name CURSOR FOR 
    SELECT a.id, a.name, o.name AS company_name 
    FROM sys_user a LEFT JOIN sys_office o ON a.company_id = o.id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;

OPEN cursor_name;
FETCH cursor_name INTO a_id, b_user_name, c_company_name;

WHILE s <> 1 DO 
    -- 设定上下文参数
    SET @userId = a_id;
    SET @beginDate = begin_date;
    SET @endDate = end_date;
    
    -- (此处执行具体的各项指标统计逻辑，见下文详解)
    
    -- 将统计数据插入临时表
    INSERT INTO user_temporary (...) VALUES (...);
    
    FETCH cursor_name INTO a_id, b_user_name, c_company_name;
END WHILE;

CLOSE cursor_name;

第3步：利用 Prepare/Execute 实现动态条件聚合统计

由于 CRM 系统的业务逻辑多变，常需要在存储过程中根据传入的日期区间（begin_date / end_date）动态追加查询条件。这里以统计“机会点总数”为例：

1. 构建基础 SQL：拼接不带日期的基本查询字符串到变量 @exesqlAll 中。
2. 动态追加条件：使用 IF 块判断传入的 begin_date 是否非空，若非空则通过 CONCAT 追加对应的时间比较条件。TO_DAYS() 函数常用于处理日期对比。
3. 预处理与执行：使用 PREPARE ... FROM @sql 生成语句，用 EXECUTE 执行，最后 DEALLOCATE PREPARE 释放资源。

核心代码片段如下：

-- 拼接基础 SQL
SET @exesqlAll = CONCAT(
    'SELECT COUNT(1) INTO @handle_num FROM crm_worksheet WHERE create_by=@userId AND del_flag=0'
);

-- 动态添加开始时间条件
IF begin_date IS NOT NULL AND begin_date != '' THEN
    SET @exesqlAll = CONCAT(@exesqlAll, 
        ' AND TO_DAYS(create_date) >= TO_DAYS(@beginDate)');
END IF;

-- 类似地处理结束时间等条件...

-- 执行并获取返回值
PREPARE allWorksheet FROM @exesqlAll;
EXECUTE allWorksheet;
DEALLOCATE PREPARE allWorksheet;

-- 结果存入本地变量
SET d_worksheet_num = @handle_num;

同理，签单数（sign_worksheet_num）、交流次数（exchange_num）和销售额（sales_volume）的计算均采用相同的动态拼接与执行模式。其中销售额可能还涉及到求和及排序逻辑，需注意处理潜在的内存开销。

第4步：过滤并输出最终报表数据

当所有用户的统计数据都填充完毕后，可根据是否需要针对特定用户ID进行二次过滤，再次构造动态 SQL 从临时表抽取数据。

SET @userIdInput = user_id;
SET @exesqlResult = CONCAT(
    'SELECT user_name, company_name, sign_worksheet_num, exchange_num, worksheet_num, sales_volume FROM user_temporary'
);

IF user_id IS NOT NULL AND user_id != '' THEN
    SET @exesqlResult = CONCAT(@exesqlResult, ' WHERE id = @userIdInput');
END IF;

PREPARE resultUser FROM @exesqlResult;
EXECUTE resultUser;
DEALLOCATE PREPARE resultUser;

经过上述复杂的游标循环与动态SQL处理后，返回的结果集符合预期的业务报表结构，输出示例如下图所示：

总结与排错

通过使用 TEMPORARY TABLE 结合游标，我们成功实现了将多维度、带参数的复杂聚合查询封装在单个存储过程中，并以结果集的形式返回给前端或其他程序。这种方法解决了 MySQL 本身不支持在 PL/SQL 中直接像 Oracle 那样返回 “Table Type” 的限制。

排查建议：

• 权限不足：若在创建临时表或执行动态 SQL 时报错，请检查当前数据库用户是否具备相应的 CREATE TEMPORARY TABLE 权限以及 PROCESS 相关权限。
• 死锁或超时：游标遍历大量数据极易导致连接占用时间过长。如果数据量超过万级，建议在外部应用中分批请求，或在存储过程中加入进度打印/日志机制观察卡点。
• 变量作用域混乱：务必分清局部变量（声明于 BEGIN 内开头）与会话变量（以 @ 开头的 PREPARE 执行结果），两者混用会导致数据覆盖或为空的情况。