Oracle 存儲過程與觸發器實現跨表資料複製遷移實戰

在 ICP 備案管理系統中，需將 5 張「通過審核表」的資料按主體 ID 批量複製至 5 張「臨時表」，並對臨時表寫入操作自動記錄變更日誌。本文提供完整的 Package 封裝存儲過程、行級觸發器日誌記錄、表級觸發器全量同步三種模式的實戰代碼與設計思路。

場景與資料模型

• 來源表（通過表）：ICP_GN_BAXX_ZT（主體）、_WZ（網站）、_JR（接入）、_IPLB（IP）、_YMLB（域名）。
• 目標表（臨時表）：對應 ICP_GN_TEMP_BAXX_* 結構，新增流水號 LSH、操作類別 CZLB、黑名單標記 IN_HMD、備案階段 BAJD 等業務欄位。
• 日誌表：ICP_GN_BAXX_XGLS_* 記錄臨時表每筆變更明細。

方案一：Package 封裝存儲過程——批量事務性複製

-- 1. 規範定義
CREATE OR REPLACE PACKAGE NCS_ICP_TJ AS
  PROCEDURE ICP_PASS_TO_TEMP(
    v_main_id IN ICP_GN_TEMP_BAXX_ZT.ZTID%TYPE,  -- 主體ID
    v_lyd     IN ICP_GN_TEMP_BAXX_ZT.SJXT_ZTID%TYPE, -- 來源系統ID
    v_in_hmd  IN ICP_GN_TEMP_BAXX_ZT.IN_HMD%TYPE,    -- 黑名單標記
    v_czlb    IN ICP_GN_TEMP_BAXX_ZT.CZLB%TYPE,      -- 操作類別
    v_bajd    IN ICP_GN_TEMP_BAXX_ZT.BAJD%TYPE       -- 備案階段
  );
END NCS_ICP_TJ;

-- 2. 實作包體：單一事務內完成 5 表級聯複製
CREATE OR REPLACE PACKAGE BODY NCS_ICP_TJ AS
  PROCEDURE ICP_PASS_TO_TEMP(...) IS
    v_lsh INTEGER;
  BEGIN
    SELECT SEQ_ICP_GN_TEMP_BAXX_ZT_ZTID.NEXTVAL INTO v_lsh FROM DUAL;
    -- 參數非空檢核
    IF v_main_id IS NULL OR ... THEN
      RAISE_APPLICATION_ERROR(-20000, 'Exsit null value in arguments.');
    END IF;

    -- 1. 複製主體表（帶業務欄位覆蓋）
    INSERT INTO ICP_GN_TEMP_BAXX_ZT (LSH, ZTID, IN_HMD, CZLB, BAJD, ...)
    SELECT v_lsh, v_main_id, v_in_hmd, v_czlb, v_bajd, ...
    FROM ICP_GN_BAXX_ZT WHERE ID = v_main_id;

    -- 2-5. 級聯複製網站、接入、IP、域名表（以 ZTID/WZID 關聯）
    INSERT INTO ICP_GN_TEMP_BAXX_WZ (...) SELECT v_lsh, ..., v_bajd FROM ICP_GN_BAXX_WZ WHERE ZTID = v_main_id;
    INSERT INTO ICP_GN_TEMP_BAXX_JR (...) SELECT v_lsh, ..., v_bajd FROM ICP_GN_BAXX_JR WHERE ZTID = v_main_id;
    INSERT INTO ICP_GN_TEMP_BAXX_IPLB (...) SELECT v_lsh, ... FROM ICP_GN_BAXX_IPLB WHERE ZTID = v_main_id;
    INSERT INTO ICP_GN_TEMP_BAXX_YMLB (...) SELECT v_lsh, ... FROM ICP_GN_BAXX_YMLB WHERE ZTID = v_main_id;
  END;
END NCS_ICP_TJ;

-- 3. 呼叫範例
CALL NCS_ICP_TJ.ICP_PASS_TO_TEMP(5, 1, 0, 2, 17);

設計亮點： 統一序列生成 LSH 串聯 5 表關聯；參數化業務欄位覆蓋；單一事務保證原子性；非空防禦性檢核。

方案二：行級觸發器——臨時表寫入自動記錄變更日誌

每張臨時表建立 AFTER INSERT FOR EACH ROW 觸發器，將 :NEW 伪記錄欄位映射寫入對應日誌表：

CREATE OR REPLACE TRIGGER TRIGGER_ICP_TEMP_ZT_INSERT
AFTER INSERT ON ICP_GN_TEMP_BAXX_ZT FOR EACH ROW
BEGIN
  INSERT INTO ICP_GN_BAXX_XGLS_ZT (ID, LSH, BBDW, CZLB, ZTID, ..., LR_YHM_ID, BAMM)
  VALUES (SEQ_ICP_GN_BAXX_XGLS_ZT_ID.NEXTVAL,
          :NEW.LSH, :NEW.BBDW, :NEW.CZLB, :NEW.ZTID, ..., :NEW.LR_YHM_ID, :NEW.BAMM);
END;

同理建立 _WZ、_JR、_IPLB 觸發器。**關鍵點：** 使用 :NEW 獲取剛插入行值；日誌表獨立序列生成主鍵；保留業務關鍵欄位（LSH、CZLB）便於追溯。

方案三：表級觸發器——全量同步（慎用）

CREATE OR REPLACE TRIGGER TRIGGER_AAA_INSERT
AFTER INSERT ON AAA
BEGIN
  INSERT INTO BBB (USERID, USERNAME)
  SELECT ID, USERNAME FROM AAA; -- 全表掃描+全量插入，高併發下極易鎖衝突、效能雪崩
END;

⚠️ 警示： 表級觸發器執行一次即掃描全表，僅適用於**極低頻、小數據量、單用戶**場景（如配置表同步）。高併發業務表**嚴禁**使用，請改用行級觸發器或 CDC/GoldenGate 等異步同步方案。

最佳實務總結