在單一預存程序中合併多個查詢結果集,減少應用層往返,是經典效能優化手法。本文解析使用臨時表 + 雙游標同步 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
關閉「(n 行受影響)」訊息,對高頻呼叫的預存程序效能提升明顯。
| 值 | 含義 |
|---|---|
| 0 | FETCH 成功,資料有效 |
| -1 | FETCH 失敗,或行不在結果集中 |
| -2 | 提取的行不存在 (已被刪除) |
迴圈條件 WHILE @@FETCH_STATUS = 0 確保雙游標同步推進,任一耗盡即停止。
IDENTITY(1,1) PRIMARY KEY CLUSTERED:提供有序唯一鍵,利於後續排序/分頁tempdb,Session 結束自動清理,但建議显式 DROPCREATE INDEX 加速後續查詢-- 預設: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 建議使用集合式寫法:
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 組配對
SELECT o.customerid, r.regiondescription
FROM (SELECT TOP 2 customerid FROM orders) o
CROSS JOIN (SELECT TOP 2 regiondescription FROM region) r;
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 + 視窗函數 | 分頁、排序、分組 | 快 | 高 |
LOCAL FAST_FORWARD + SET NOCOUNT ON + 显式 DEALLOCATEINSERT ... SELECT 直寫目標表,避免臨時表中轉理解游標與臨時表的運作機制,並在現代 SQL 功能 (CTE, 視窗函數, OFFSET/FETCH) 普及的今天,做出正確的技術選型。