Oracle 自增列實現方案：Sequence + Trigger 標準模式（11g/12c/19c/21c 通用）

為什麼 Oracle 無原生 Auto Increment？

Oracle 設計哲學強調序列對象獨立於表，支援多表共享、緩存、循環、順序/無序等豐富屬性。12c 引入 IDENTITY 列（語法糖），底層仍由 Sequence 實現。

方案一：Sequence + BEFORE INSERT Trigger（通用、全版本）

1. 創建測試表

CREATE TABLE test_table (
  id   NUMBER NOT NULL,  -- 自增主鍵
  name VARCHAR2(40)
);

2. 創建序列

CREATE SEQUENCE seq_test_table
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  INCREMENT BY 1
  START WITH 1
  CACHE 20
  NOORDER
  NOCYCLE;

• CACHE 20：SGA 中預分配 20 個值，減少字典鎖爭用，RAC 環境建議 NOORDER（不保證跨實例絕對順序）提升性能。
• NOCYCLE：達到最大值不循環，防止主鍵衝突。

3. 創建觸發器

CREATE OR REPLACE TRIGGER trg_test_table_bir
BEFORE INSERT ON test_table
FOR EACH ROW
BEGIN
  SELECT seq_test_table.NEXTVAL INTO :NEW.id FROM dual;
END;
/

• BEFORE INSERT：在行寫入前賦值。
• FOR EACH ROW：行級觸發，每行執行一次。
• :NEW.id：引用新行的 id 列。

4. 測試驗證

INSERT INTO test_table (name) VALUES ('Alice');
INSERT INTO test_table (name) VALUES ('Bob');
INSERT INTO test_table (name) VALUES ('Charlie');

SELECT * FROM test_table;
-- ID | NAME
-- 1  | Alice
-- 2  | Bob
-- 3  | Charlie

方案二：12c+ Identity 列（原生語法，零觸發器）

CREATE TABLE test_table_new (
  id   NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 CACHE 20),
  name VARCHAR2(40)
);

-- 或允許手動指定值（GENERATED BY DEFAULT）
CREATE TABLE test_table_flex (
  id   NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
  name VARCHAR2(40)
);

• GENERATED ALWAYS：插入時不可指定 ID，違規報 ORA-32795。
• GENERATED BY DEFAULT：插入時可選指定 ID，未指定則自動取序列值。
• 底層自動創建系統序列 ISEQ$$_XXXXX，可通過 USER_TAB_IDENTITY_COLS 查詢。

方案三：12c+ DEFAULT ON NULL 序列值（最輕量）

CREATE SEQUENCE seq_test;

CREATE TABLE test_table_simple (
  id   NUMBER DEFAULT ON NULL seq_test.NEXTVAL NOT NULL,
  name VARCHAR2(40)
);

• 無觸發器、無 Identity 元數據，純列默認值綁定序列。
• ON NULL：當插入值為 NULL 時才取序列值，允許顯式指定 ID。

進階：指定 ID 插入後序列同步（防止主鍵衝突）

-- 手動指定 ID = 100 插入
INSERT INTO test_table (id, name) VALUES (100, 'Manual');

-- 同步序列到 > 100
DECLARE
  v_curr NUMBER;
BEGIN
  SELECT seq_test_table.NEXTVAL INTO v_curr FROM dual;  -- 先取一個
  WHILE v_curr <= 100 LOOP
    SELECT seq_test_table.NEXTVAL INTO v_curr FROM dual;  -- 循環推進
  END LOOP;
END;
/
-- 或 18c+ 一條語句
ALTER SEQUENCE seq_test_table RESTART START WITH 101;

性能與最佳實踐對比

常見問題避坑

1. 緩存導致間隙：實例重啟、ALTER SEQUENCE、RAC 實例故障會丟失緩存值，ID 不連續是正常現象，業務勿依賴連續性。
2. MAXVALUE 設太小：達到上限報 ORA-08004，建議設極大或 CYCLE（極少用）。
3. 觸發器中誤用 :OLD.id：BEFORE INSERT 時 :OLD 為 NULL，必須用 :NEW。
4. 批量插入性能：FORALL + INSERT ... VALUES (:1, :2) 仍會逐行觸發 Trigger，12c+ Identity/DEFAULT 顯著更快。

結語

Oracle 實現自增主鍵的標準演進路徑：Sequence + Trigger（通用） → Identity 列（12c+ 原生） → DEFAULT ON NULL（12c+ 最輕量）。新項目優先選擇 GENERATED BY DEFAULT AS IDENTITY 或 DEFAULT ON NULL seq.NEXTVAL，零觸發器、內核級性能、語義清晰。舊版本或需複雜賦值邏輯時，BEFORE INSERT FOR EACH ROW Trigger 依然可靠。記住：序列不連續是特性而非缺陷，業務設計勿依賴連續性。