Oracle 實現自增主鍵：Sequence + Trigger 經典方案與 12c Identity Column 現代寫法對比

傳統方案：Sequence + BEFORE INSERT Trigger

Step 1：建立 Sequence

CREATE SEQUENCE sequ_data_datainfo
    INCREMENT BY 1
    START WITH 1
    NOCYCLE
    NOCACHE
    NOORDER;

• NOCACHE：避免實例重啟導致序號跳躍（犧牲少量效能換取連續性）。
• NOCYCLE：達最大值不循環，防止主鍵衝突。

Step 2：建立 Trigger 自動賦值

CREATE OR REPLACE TRIGGER trig_test
BEFORE INSERT ON table1
FOR EACH ROW
DECLARE
    v_nextval NUMBER;
BEGIN
    SELECT sequ_data_datainfo.NEXTVAL INTO v_nextval FROM dual;
    :NEW.procid := v_nextval;
EXCEPTION
    WHEN OTHERS THEN
        -- 生產環境建議記錄日誌後再 RAISE
        RAISE;
END;
/

關鍵點：

• BEFORE INSERT 確保主鍵在行寫入前已生成。
• :NEW.procid 直接修改偽記錄，對應用透明。
• 例外區塊 RAISE 保持錯誤傳播，避免靜默失敗。

12c+ 現代方案：Identity Column（原生自增）

-- 建表時直接定義
CREATE TABLE table1 (
    procid NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    col2   VARCHAR2(100)
);

-- 或修改現有表
ALTER TABLE table1 MODIFY procid GENERATED ALWAYS AS IDENTITY;

優勢：

1. 零物件維護（無 Sequence/Trigger）。
2. 效能更高（內部機制優於觸發器上下文切換）。
3. 支援 GENERATED BY DEFAULT ON NULL 允許手動指定值（兼容舊遷移資料）。
4. Data Pump 匯出/匯入自動處理序列值銜接。

遷移指南：從 Trigger 升級到 Identity

1. 停用應用寫入。
2. DROP TRIGGER trig_test;
3. DROP SEQUENCE sequ_data_datainfo;
4. ALTER TABLE table1 MODIFY procid GENERATED ALWAYS AS IDENTITY;
5. 驗證 INSERT INTO table1(col2) VALUES('x') 自動生成主鍵。
6. 重啟應用。

效能對比實測（百萬筆插入）

結論：極高併發寫入場景，應用層批次預取 Sequence 最優；一般業務系統，12c Identity Column 兼顧開發效率與效能，為首選。

常見坑點提醒

• Sequence 獨立於事務：ROLLBACK 不會回滾 Sequence 值，導致主鍵不連續（正常現象，勿嘗試修補）。
• CACHE 導致跳號：RAC 環境每實例各緩存一段，重啟丟失緩存段，跳號更明顯。
• Trigger 內查詢 Sequence：舊寫法 SELECT seq.NEXTVAL INTO :NEW.id FROM dual; 11g+ 可簡寫為 :NEW.id := seq.NEXTVAL;（PL/SQL 引擎內部優化）。

根據資料庫版本與業務量級選型：12c+ 直上 Identity Column；11g 及以下堅持 Sequence + Trigger，並關閉 Sequence CACHE 以換取主鍵連續性感知。