Oracle 旋轉 INSERT (PIVOT INSERT) 多表插入實戰

Oracle 旋轉 INSERT (PIVOT INSERT) 多表插入實戰

Oracle 多表 INSERT 支援 INSERT ALL 搭配 SELECT 子句，實現「一讀多寫」與「行轉列」旋轉插入。本文透過銷售表轉月度統計表範例，示範旋轉 INSERT 完整流程。

源資料：銷售表

CREATE TABLE sales_tbl (
   id  NUMBER PRIMARY KEY,
   jan NUMBER,
   feb NUMBER,
   mar NUMBER,
   apr NUMBER
);

INSERT INTO sales_tbl VALUES (1, 100, 200, 300, 400);
INSERT INTO sales_tbl VALUES (2, 110, 120, 130, 140);

-- 來源資料
ID  JAN  FEB  MAR  APR
1   100  200  300  400
2   110  120  130  140

目標表：月度銷售統計表

CREATE TABLE monthly_sales_stats (
   seq_no       NUMBER,
   month_name   VARCHAR2(10),
   avg_sales    NUMBER,
   sum_sales    NUMBER
);

旋轉 INSERT 語句

INSERT ALL
   INTO monthly_sales_stats VALUES (1, '一月', avg1, sum1)
   INTO monthly_sales_stats VALUES (2, '二月', avg2, sum2)
   INTO monthly_sales_stats VALUES (3, '三月', avg3, sum3)
   INTO monthly_sales_stats VALUES (4, '四月', avg4, sum4)
SELECT AVG(jan) avg1, SUM(jan) sum1,
       AVG(feb) avg2, SUM(feb) sum2,
       AVG(mar) avg3, SUM(mar) sum3,
       AVG(apr) avg4, SUM(apr) sum4
FROM   sales_tbl;

執行結果

4 rows created.

SEQ_NO  MONTH_NAME  AVG_SALES  SUM_SALES
1       一月         105        210
2       二月         160        320
3       三月         215        430
4       四月         270        540

核心機制解析

• 單次掃描：SELECT 子句只掃描一次 sales_tbl，計算 8 個彙總值
• 多目標插入：INSERT ALL 將每個 INTO 子句視為獨立插入操作，共插入 4 筆
• 列轉行：來源表的 4 個月份欄位（JAN/FEB/MAR/APR）轉為目標表的 4 列記錄
• 條件分支：可加入 WHEN 子句實現條件式多表插入（INSERT ALL/WHEN ... THEN ... ELSE ...）

擴充：條件式多表插入

INSERT ALL
   WHEN avg_sales > 150 THEN
      INTO high_perf_months VALUES (month_name, avg_sales, sum_sales)
   WHEN avg_sales <= 150 THEN
      INTO low_perf_months VALUES (month_name, avg_sales, sum_sales)
   ELSE
      INTO other_months VALUES (month_name, avg_sales, sum_sales)
SELECT AVG(jan) avg_sales, SUM(jan) sum_sales, '一月' month_name FROM sales_tbl UNION ALL
SELECT AVG(feb), SUM(feb), '二月' FROM sales_tbl UNION ALL
SELECT AVG(mar), SUM(mar), '三月' FROM sales_tbl UNION ALL
SELECT AVG(apr), SUM(apr), '四月' FROM sales_tbl;

適用場景

1. ETL 資料轉換：寬表轉長表（樞紐逆向操作）
2. 報表預計算：單次掃描產生多維度彙總表
3. 資料歸檔分流：依業務規則分發至不同歸檔表
4. 效能優化：替代多次獨立 INSERT，減少掃描與解析開銷

限制與注意事項

• 最大 127 個 INTO 子句
• INSERT FIRST：滿足第一個 WHEN 條件即停止評估後續條件
• INSERT ALL：評估所有 WHEN 條件，可能插入多張表
• 目標表不可有觸發器（或需注意觸發器遞迴）
• 並行 DML：ALTER SESSION ENABLE PARALLEL DML 後可提升大量資料效能

效能對比