萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

MySQL 預存程序實現多表結果集合併:游標與臨時表實戰範例

MySQL 預存程序整合多表查詢結果:游標迴圈寫入臨時表統一輸出

在單一預存程序中合併多個獨立查詢的結果集,減少應用層網路往返,是報表與儀表板常見需求。本文提供 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 ;

MySQL 與 SQL Server 寫法關鍵差異

特性SQL Server (T-SQL)MySQL
分隔符GO (批次分隔)DELIMITER // ... // DELIMITER ;
變數宣告DECLARE @var TYPEDECLARE var TYPE [DEFAULT value]
游標 NOT FOUND@@FETCH_STATUS = 0DECLARE 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:

  • 程式碼結構更清晰 (LOOP + LEAVE)
  • 支援多游標獨立控制
  • 符合 SQL:2003 標準

效能考量與優化建議

  • 游標屬逐行處理 (RBAR),效能遠低於集合式操作
  • 臨時表引擎選擇:
    - MEMORY:小結果集、高頻存取、無大欄位
    - InnoDB:大結果集、需事務、支援 BLOB/TEXT
  • 現代替代方案 (MySQL 8.0+):
-- 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 類型**

copyright © 萬盛學電腦網 all rights reserved