在單一預存程序中合併多個獨立查詢的結果集,減少應用層網路往返,是報表與儀表板常見需求。本文提供 MySQL 預存程序中使用 雙游標同步 FETCH + 臨時表暫存 的完整實戰範例。
DELIMITER //
CREATE PROCEDURE sp_MergeSalesAndDebt()
BEGIN
DECLARE done1 INT DEFAULT FALSE;
DECLARE done2 INT DEFAULT FALSE;
DECLARE v_customer_id VARCHAR(20);
DECLARE v_region_desc VARCHAR(20);
DECLARE v_index INT DEFAULT 1;
-- 1. 建立臨時表儲存合併結果
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_merged_result (
seq_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id VARCHAR(20),
region_desc VARCHAR(20)
) ENGINE=MEMORY;
-- 2. 宣告兩個游標 (對應兩個獨立查詢)
DECLARE cur_sales CURSOR FOR
SELECT customer_id FROM orders LIMIT 2;
DECLARE cur_region CURSOR FOR
SELECT region_description FROM region LIMIT 2;
-- 3. 宣告 NOT FOUND 處理器 (優雅結束迴圈)
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;
-- 4. 開啟游標
OPEN cur_sales;
OPEN cur_region;
-- 5. 同步迴圈讀取並寫入臨時表
read_loop: LOOP
FETCH cur_sales INTO v_customer_id;
FETCH cur_region INTO v_region_desc;
-- 任一游標耗盡則結束
IF done1 OR done2 THEN
LEAVE read_loop;
END IF;
INSERT INTO tmp_merged_result (customer_id, region_desc)
VALUES (v_customer_id, v_region_desc);
SET v_index = v_index + 1;
END LOOP;
-- 6. 關閉釋放游標
CLOSE cur_sales;
CLOSE cur_region;
-- 7. 返回合併結果
SELECT * FROM tmp_merged_result;
-- 8. 清理 (臨時表 Session 結束自動清理,這裡显式清理)
DROP TEMPORARY TABLE IF EXISTS tmp_merged_result;
END //
DELIMITER ;
| 特性 | SQL Server (T-SQL) | MySQL |
|---|---|---|
| 分隔符 | GO (批次分隔) | DELIMITER // ... // DELIMITER ; |
| 變數宣告 | DECLARE @var TYPE | DECLARE var TYPE [DEFAULT value] |
| 游標 NOT FOUND | @@FETCH_STATUS = 0 | DECLARE CONTINUE HANDLER FOR NOT FOUND |
| 臨時表 | #table (tempdb) | CREATE TEMPORARY TABLE (當前 DB) |
| 自增欄位 | IDENTITY(1,1) | AUTO_INCREMENT |
| 字串連接 | + | CONCAT() 或 || (SQL_MODE=PIPES_AS_CONCAT) |
MySQL 使用 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE 替代 @@FETCH_STATUS:
-- CTE + 視窗函數替代游標合併
WITH ranked_sales AS (
SELECT customer_id, ROW_NUMBER() OVER () AS rn FROM orders LIMIT 2
),
ranked_region AS (
SELECT region_description, ROW_NUMBER() OVER () AS rn FROM region LIMIT 2
)
SELECT s.customer_id, r.region_description
FROM ranked_sales s JOIN ranked_region r ON s.rn = r.rn;
| 場景 | 推薦方案 |
|---|---|
| 兩獨立查詢結果按行號配對合併 | CTE + ROW_NUMBER() (8.0+) |
| 複雜逐行邏輯處理 (需呼叫外部 API、複雜計算) | 游標 + 臨時表 |
| 大量資料中間落地、多報表複用 | 臨時表 (InnoDB) + 索引 |
| 簡單 UNION/ JOIN 可解決 | 集合式 SQL (最佳) |
MySQL 預存程序合併多結果集:
🔧 傳統方案:雙游標 + 臨時表 + NOT FOUND Handler,相容所有版本
⚡ 現代方案:CTE + 視窗函數 (MySQL 8.0+),集合式、可優化、無資源洩漏風險
🎯 核心原則:能用集合式 SQL 解決,**絕不使用游標**;必須用游標時,**显式 CLOSE/DEALLOCATE、使用 LOCAL 範圍、選擇 FAST_FORWARD 類型**