萬盛學電腦網

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

SQL Server 預存程序中使用臨時表與游標整合多結果集實戰

SQL Server 預存程序整合多結果集:臨時表 + 雙游標同步讀取模式

在單一預存程序中合併多個查詢結果集,減少應用層往返,是經典效能優化手法。本文解析使用臨時表 + 雙游標同步 FETCH 模式整合兩個獨立查詢結果的完整範例與關鍵技術點。

完整預存程序範例

CREATE PROCEDURE usp_MergeTwoResults
AS
BEGIN
    SET NOCOUNT ON;  -- 不返回「影響行數」訊息,減少網路封包

    DECLARE @col1c VARCHAR(20), @col2c VARCHAR(20);
    DECLARE @index INT = 1;

    -- 1. 建立臨時表儲存合併結果
    CREATE TABLE #tmptbl (
        colID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        col1 VARCHAR(20),
        col2 VARCHAR(20)
    );

    -- 2. 宣告兩個游標 (對應兩個獨立查詢)
    DECLARE cur1 CURSOR FOR 
        SELECT TOP 2 customerid FROM orders;
    DECLARE cur2 CURSOR FOR 
        SELECT TOP 2 regiondescription FROM region;

    -- 3. 開啟游標
    OPEN cur1;
    OPEN cur2;

    -- 4. 預先 FETCH 第一筆 (雙游標同步)
    FETCH cur2 INTO @col2c;
    FETCH cur1 INTO @col1c;

    -- 5. 迴圈同步讀取並寫入臨時表
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO #tmptbl (col1, col2) VALUES (@col1c, @col2c);
        
        FETCH NEXT FROM cur1 INTO @col1c;
        FETCH NEXT FROM cur2 INTO @col2c;
    END

    -- 6. 清理資源
    CLOSE cur1; CLOSE cur2;
    DEALLOCATE cur1; DEALLOCATE cur2;

    -- 7. 返回合併結果
    SELECT * FROM #tmptbl;

    -- 8. 顯式清理臨時表
    DROP TABLE #tmptbl;
END
GO

關鍵技術點深度解析

1. SET NOCOUNT ON - 減少網路開銷

關閉「(n 行受影響)」訊息,對高頻呼叫的預存程序效能提升明顯。

2. @@FETCH_STATUS 狀態碼對照

值含義
0FETCH 成功,資料有效
-1FETCH 失敗,或行不在結果集中
-2提取的行不存在 (已被刪除)

迴圈條件 WHILE @@FETCH_STATUS = 0 確保雙游標同步推進,任一耗盡即停止。

3. 臨時表設計考量

  • IDENTITY(1,1) PRIMARY KEY CLUSTERED:提供有序唯一鍵,利於後續排序/分頁
  • 放在 tempdb,Session 結束自動清理,但建議显式 DROP
  • 若資料量大,考慮 CREATE INDEX 加速後續查詢

4. 游標類型選擇

-- 預設:FORWARD_ONLY, READ_ONLY, LOCAL
DECLARE cur1 CURSOR FOR SELECT ... 

-- 显式指定 (效能最佳)
DECLARE cur1 CURSOR LOCAL FAST_FORWARD FOR SELECT ...
-- FAST_FORWARD = FORWARD_ONLY + READ_ONLY + 優化

現代替代方案:集合式操作 (推薦)

游標屬「逐行處理」 (RBAR),效能較差。現代 SQL Server 建議使用集合式寫法:

方案 A:UNION ALL + ROW_NUMBER (若兩查詢結構相同)

SELECT col1, col2 FROM (
    SELECT customerid AS col1, NULL AS col2, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn
    FROM orders
    UNION ALL
    SELECT NULL, regiondescription, ROW_NUMBER() OVER (ORDER BY (SELECT 1))
    FROM region
) t WHERE rn <= 2; -- 取前 2 組配對

方案 B:CROSS JOIN 笛卡兒積 (若需全排列)

SELECT o.customerid, r.regiondescription
FROM (SELECT TOP 2 customerid FROM orders) o
CROSS JOIN (SELECT TOP 2 regiondescription FROM region) r;

方案 C:OFFSET/FETCH 分頁配對 (SQL 2012+)

WITH cte1 AS (SELECT customerid, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn FROM orders),
     cte2 AS (SELECT regiondescription, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn FROM region)
SELECT c1.customerid, c2.regiondescription
FROM cte1 c1 JOIN cte2 c2 ON c1.rn = c2.rn
WHERE c1.rn <= 2;

效能對比

方案適用場景效能可讀性
雙游標 + 臨時表複雜邏輯、需逐行處理、舊版相容較慢 (RBAR)中
集合式 JOIN/UNION關聯查詢、批次處理快 (集合運算)高
CTE + 視窗函數分頁、排序、分組快高

最佳實踐建議

  1. 優先使用集合式 SQL (JOIN, UNION, CTE, 視窗函數),避免游標
  2. 必須用游標時:LOCAL FAST_FORWARD + SET NOCOUNT ON + 显式 DEALLOCATE
  3. 臨時表用於中間結果重複利用,單次查詢無需臨時表
  4. 大量資料合併考慮 INSERT ... SELECT 直寫目標表,避免臨時表中轉

理解游標與臨時表的運作機制,並在現代 SQL 功能 (CTE, 視窗函數, OFFSET/FETCH) 普及的今天,做出正確的技術選型。

copyright © 萬盛學電腦網 all rights reserved