Oracle 自動遞增主鍵實作：Sequence + Trigger 標準模式與 12c Identity Column 新寫法

傳統兩步驟實作 (11g 及之前通用)

步驟 1：建立序列

CREATE SEQUENCE sequence_identity
  START WITH 1          -- 起始值
  INCREMENT BY 1        -- 遞增值
  MAXVALUE 9999999999   -- 最大值 (或 NOMAXVALUE)
  NOCYCLE               -- 不循環
  NOCACHE;              -- 不快取 (避免跳號，效能稍低)
  -- CACHE 1000;         -- 高併發建議快取

步驟 2：建立 BEFORE INSERT 行觸發器

CREATE OR REPLACE TRIGGER trigger_identity
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  SELECT sequence_identity.NEXTVAL 
  INTO :NEW.column_name 
  FROM DUAL;
END;
/

命名慣例建議 (避免衝突)

Oracle 12c+ 原生 Identity Column (推薦)

-- 永遠自動產生，不允許手動指定
CREATE TABLE t (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR2(100)
);

-- 允許手動指定值，NULL 時自動產生
CREATE TABLE t (
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  name VARCHAR2(100)
);

-- 自訂序列參數
CREATE TABLE t (
  id NUMBER GENERATED ALWAYS AS IDENTITY 
    (START WITH 100 INCREMENT BY 10 MAXVALUE 10000 CYCLE CACHE 100),
  name VARCHAR2(100)
);

Identity Column 優勢

• 效能更好：無觸發器呼叫開銷，內部最佳化
• 語意清晰：DDL 直接表達意圖
• 支援所有序列選項：START WITH, INCREMENT BY, MAXVALUE, CYCLE, CACHE, ORDER/NOORDER
• 匯入/匯出更簡單：Data Pump 自動處理序列狀態
• 無觸發器失效風險：DDL 變更不會導致觸發器 INVALID

遷移建議

1. 新專案直接使用 Identity Column
2. 舊系統可保持 Sequence+Trigger，除非有效能瓶頸
3. 若遷移：ALTER TABLE t MODIFY id GENERATED ALWAYS AS IDENTITY; (12c+)
4. 遷移後可刪除對應觸發器與序列 (確認無其他處引用)

常見問題

效能調優建議

• 高併發 OLTP：CACHE 1000 或更大，減少序列鎖競爭
• 批次載入：暫停觸發器，使用 sequence.NEXTVAL 直接賦值
• 監控序列狀態：SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = '...';