Oracle PL/SQL 高效 FOR 迴圈批次插入實戰

Oracle PL/SQL 高效 FOR 迴圈批次插入實戰

在 Oracle 資料庫開發中，批次產生測試資料或執行大量插入操作是常見需求。本文展示如何使用 PL/SQL 的 FOR LOOP 結合 FORALL (雖本範例未使用) 高效完成批次插入，並說明關鍵效能考量點。

建立測試表結構

CREATE TABLE computers (
    comNo      NUMBER(4) CONSTRAINT PK_comp PRIMARY KEY,
    compModel  VARCHAR2(64) CONSTRAINT unique_comp UNIQUE,
    buyTime    DATE,
    price      NUMBER(12,2) CONSTRAINT ch_price CHECK (price > 0 AND price <= 300000),
    owner      VARCHAR2(32)
);

基本 FOR 迴圈插入範例

DECLARE
    i NUMBER := 0;
BEGIN
    FOR i IN 1 .. 3000 LOOP
        INSERT INTO computers (comNo, compModel, buyTime, price, owner)
        VALUES (i, 'Model_' || i, TRUNC(SYSDATE) - MOD(i, 365), i * 100, 'zhangsan');
        -- dbms_output.put_line(i); -- 除錯用，大量資料請關閉
    END LOOP;
    COMMIT;
END;
/

程式碼解析與優化建議

1. 字串串接：原始碼 '8' + i 在 Oracle 中會隱式轉型為數字相加，導致結果非預期。正確寫法為 'Model_' || i 或 TO_CHAR(i)。
2. 日期處理：to_date('' + sysdate, 'YYYY-MM') 語法錯誤。建議使用 TRUNC(SYSDATE) 取得當天零點，或 ADD_MONTHS(TRUNC(SYSDATE), -MOD(i,12)) 產生不同月份。
3. 批次提交：範例在迴圈結束後才 COMMIT，若筆數極大 (如百萬筆) 易導致 UNDO 表空間不足或鎖定過久。建議每 N 筆 (如 1000) 提交一次：
   

   IF MOD(i, 1000) = 0 THEN COMMIT; END IF;

4. 效能最佳化：FORALL 批次綁定：對於大量 DML，FORALL 配合集合 (Collection) 可大幅降低 Context Switch 開銷：
   

   DECLARE
       TYPE num_arr IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
       TYPE vchar2_arr IS TABLE OF VARCHAR2(64) INDEX BY PLS_INTEGER;
       v_comNo   num_arr;
       v_model   vchar2_arr;
       v_buyTime num_arr; -- 簡化用 NUMBER 存 Julian day
       v_price   num_arr;
       v_owner   vchar2_arr;
   BEGIN
       FOR i IN 1 .. 3000 LOOP
           v_comNo(i)   := i;
           v_model(i)   := 'Model_' || i;
           v_buyTime(i) := TRUNC(SYSDATE) - MOD(i, 365);
           v_price(i)   := i * 100;
           v_owner(i)   := 'zhangsan';
       END LOOP;
   
       FORALL idx IN 1 .. 3000
           INSERT INTO computers (comNo, compModel, buyTime, price, owner)
           VALUES (v_comNo(idx), v_model(idx), v_buyTime(idx), v_price(idx), v_owner(idx));
       COMMIT;
   END;
   /
   

5. 直接路徑插入：搭配 /*+ APPEND */ Hint 可繞過緩衝區快取直接寫入資料檔，適用於大量載入：
   

   INSERT /*+ APPEND */ INTO computers ...

   注意：需在無觸發器、無外鍵、表無索引或索引設為 UNUSABLE 時效果最佳，且插入後需 ALTER INDEX ... REBUILD。

常見錯誤與除錯技巧

• 唯一鍵衝突：compModel 有 UNIQUE 限制，迴圈產生的值必須唯一。
• 檢查限制違反：price 的 CHECK 條件 price > 0 AND price <= 300000，測試資料需落在此範圍。
• 隱式轉型陷阱：避免在 SQL 中混用 + 做字串串接，一律使用 ||。

總結

PL/SQL FOR LOOP 適合中小規模批次操作；大規模資料載入請優先採用 FORALL + 集合或 INSERT /*+ APPEND */ 直載模式，並配合適當的提交頻率與索引管理策略，以兼顧效能與資源控制。