Oracle 實現自增主鍵：Sequence + Trigger 最佳實踐

Oracle 無 AUTO_INCREMENT/IDENTITY（12c 前），標準做法是 Sequence 產生值 + Before Insert Trigger 回填。12c+ 可直接使用 GENERATED AS IDENTITY。本文涵蓋兩種版本的完整語法與效能考量。

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

1. 建立 Sequence

CREATE SEQUENCE salary_seq
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20           -- 緩存 20 個值於 SGA，減少序列鎖競爭
  NOCYCLE            -- 不循環
  NOORDER;           -- RAC 環境下不保證全域有序，效能更高

參數解析：

• CACHE：預先在記憶體分配號碼段，重啟會導致間隙；高併發建議 CACHE 100-1000。
• ORDER：RAC 下強制跨實例有序，需全域鎖，嚴重影響擴展性，非必要勿用。

2. 建立觸發器

CREATE OR REPLACE TRIGGER trg_salary_pk
BEFORE INSERT ON salary_table
FOR EACH ROW
WHEN (NEW.id IS NULL)  -- 僅在未指定 ID 時自動賦值，保留手工指定彈性
BEGIN
  SELECT salary_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/

3. 測試

INSERT INTO salary_table (name, amount) VALUES ('Alice', 5000);
INSERT INTO salary_table (name, amount) VALUES ('Bob', 6000);
SELECT * FROM salary_table;

ID NAME  AMOUNT
-- ----- ------
 1 Alice  5000
 2 Bob    6000

方案二：Identity Column（Oracle 12c+ 原生支援）

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

-- 或允許手工覆蓋
CREATE TABLE salary_table (
  id          NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  name        VARCHAR2(50),
  amount      NUMBER
);

優點：無需 Trigger、DDL 可見、ALTER TABLE MODIFY id GENERATED ALWAYS AS IDENTITY 支援遷移、效能優於 Trigger 方案。

Sequence 進階用法

-- 獲取當前值（需先調用過 NEXTVAL）
SELECT salary_seq.CURRVAL FROM dual;

-- 直接在 SQL 中使用
INSERT INTO t (id, col) VALUES (salary_seq.NEXTVAL, 'x');

-- 修改序列參數
ALTER SEQUENCE salary_seq INCREMENT BY 10 MAXVALUE 10000 CYCLE CACHE 100;

-- 刪除序列
DROP SEQUENCE salary_seq;

效能與間隙說明

• 間隙不可避免：Rollback、Instance Crash、Cache Lost、DROP SEQUENCE 皆導致號碼跳躍，業務勿依賴連續性。
• 高併發優化：CACHE 1000 + NOORDER 可將序列鎖競爭降至最低。
• RAC 最佳實踐：使用 SESSION_CACHED_CURSORS 減少硬解析，搭配 NOORDER Sequence。

12c 以上新專案首選 Identity Column；存量系統維護 Sequence+Trigger 穩定可靠。切記：Sequence 產生的值只保證唯一、遞增，不保證連續。