Oracle 自增欄位實戰：Sequence + Trigger 實現類 Identity 效果 (11g/12c/19c/21c/23ai 通用)

背景：Oracle 無原生 Auto_Increment，但 12c+ 已支援 Identity Column

Oracle 12c 引入 GENERATED AS IDENTITY 標準語法，11g 及之前版本需手工組合 **Sequence + Before Insert Trigger**。本文涵蓋兩種方案，兼容所有主流版本。

方案一：Sequence + Trigger (11g/12c/19c/21c/23ai 通用)

1. 建表

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

2. 建立 Sequence

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

• CACHE 20：預先在記憶體分配 20 個值，減少字典鎖爭用，RAC 環境建議 `NOORDER` 避免跨實例排序開銷。
• START WITH：若表已有資料，設為 `MAX(id)+1`。

3. 建立 Before Insert Trigger

CREATE OR REPLACE TRIGGER trg_test_table_pk
BEFORE INSERT ON test_table
FOR EACH ROW
BEGIN
  SELECT seq_test_table.NEXTVAL INTO :NEW.id FROM dual;
END;
/
-- 測試
INSERT INTO test_table (name) VALUES ('Alice');
INSERT INTO test_table (name) VALUES ('Bob');
SELECT * FROM test_table;
-- ID  自動生成 1, 2...

方案二：Identity Column (12c/19c/21c/23ai 原生支援，零維護)

-- 建表時直接定義
CREATE TABLE test_table_new (
  id   NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 CACHE 20),
  name VARCHAR2(40)
);

-- 或修改現有表 (12.2+)
ALTER TABLE test_table MODIFY id GENERATED ALWAYS AS IDENTITY;

• ALWAYS：強制由資料庫生成，插入時不可指定 ID（插入報錯）。
• BY DEFAULT：允許手工指定 ID，未指定時自動生成（適合遷移舊資料）。
• 內部自動建立同名 Sequence (`ISEQ$$_`)，Drop 表時自動清理。

效能與維護對比

遷移建議

1. 新專案 **直接使用 Identity Column**。
2. 舊系統 12c+ 升級後，可逐表 `ALTER TABLE ... MODIFY ... GENERATED AS IDENTITY`，並手工 Drop 對應 Sequence/Trigger。
3. 跨版本遷移資料時，注意 `START WITH` 設定避免主鍵衝突。

結語

Sequence+Trigger 是經典通用方案，Identity Column 是現代原生標準。依據資料庫版本與遷移策略選擇，核心原理皆為「集中式單調遞增數值生成器」。