Oracle 資料表間資料搬移：INSERT SELECT 與游標批量處理實戰範例

基本語法：INSERT ... SELECT

結構完全相同

INSERT INTO target_table SELECT * FROM source_table;

指定欄位對應 (父子表、欄位順序不同)

INSERT INTO target_table (col1, col2, col3)
SELECT src_col1, src_col2, src_col3 FROM source_table;

進階：帶參數游標批量搬移 (PL/SQL)

適用於需依條件篩選、轉換邏輯、記錄執行狀態的複雜場景。

CREATE OR REPLACE PROCEDURE get_data (
  n_task_id   IN NUMBER,
  v_task_name IN VARCHAR2,
  v_name      IN VARCHAR2
) IS
  i_count NUMBER(5) := 0;
  
  -- 定義帶參數游標
  CURSOR data_cur (in_name VARCHAR2) IS
    SELECT * FROM get_data_src WHERE name = in_name;
    
BEGIN
  FOR mycur IN data_cur(v_name) LOOP
    INSERT INTO abc (
      row_id, task_id, task_name, get_data_dt,
      cust_id, assign_flag, deal_date
    ) VALUES (
      seq_kd.NEXTVAL, n_task_id, v_task_name, SYSDATE,
      mycur.cust_id, 'N', NULL
    );
    i_count := i_count + 1;
    
    -- 選擇性：每 N 筆 Commit 一次，避免 Undo 爆滿
    IF MOD(i_count, 1000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  
  COMMIT; -- 最終提交
  DBMS_OUTPUT.PUT_LINE('Total migrated: ' || i_count);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END get_data;
/

高效能寫法：BULK COLLECT + FORALL

資料量超過萬筆時，強烈建議使用批量綁定：

CREATE OR REPLACE PROCEDURE get_data_bulk (
  n_task_id   IN NUMBER,
  v_task_name IN VARCHAR2,
  v_name      IN VARCHAR2
) IS
  TYPE src_rt IS TABLE OF get_data_src%ROWTYPE;
  v_src src_rt;
  v_limit CONSTANT PLS_INTEGER := 5000;
  v_total PLS_INTEGER := 0;
  
  CURSOR c_src IS
    SELECT * FROM get_data_src WHERE name = v_name;
    
BEGIN
  OPEN c_src;
  LOOP
    FETCH c_src BULK COLLECT INTO v_src LIMIT v_limit;
    EXIT WHEN v_src.COUNT = 0;
    
    FORALL i IN 1..v_src.COUNT
      INSERT INTO abc (
        row_id, task_id, task_name, get_data_dt,
        cust_id, assign_flag, deal_date
      ) VALUES (
        seq_kd.NEXTVAL, n_task_id, v_task_name, SYSDATE,
        v_src(i).cust_id, 'N', NULL
      );
      
    v_total := v_total + v_src.COUNT;
    COMMIT; -- 每批提交
  END LOOP;
  CLOSE c_src;
  
  DBMS_OUTPUT.PUT_LINE('Bulk migrated: ' || v_total);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END get_data_bulk;
/

效能比較與選擇指南

常見陷阱與最佳實務

1. 序列值重複：FORALL 內呼叫 SEQ.NEXTVAL 會產生相同值，需改用 SEQ.NEXTVAL 在 SQL 陳述式中直接引用，或預先生成序列陣列。
2. 分批 Commit：避免長交易導致 Undo 表空間暴增、鎖定爭用，建議每 1,000~5,000 筆 Commit。
3. 錯誤處理：FORALL ... SAVE EXCEPTIONS 捕獲個別 DML 錯誤，事後檢視 SQL%BULK_EXCEPTIONS。
4. 平行處理：極大量資料可考慮 DBMS_PARALLEL_EXECUTE 切分任務並行執行。

結語

從簡單的 INSERT SELECT 到企業級的 BULK COLLECT + FORALL 分批提交，選擇適合資料量與業務複雜度的搬移策略，是確保資料遷移專案成功的關鍵。