Oracle Sequence 與 Trigger 實現主鍵自增完整方案

序列與觸發器打造 Oracle 自增主鍵最佳實踐

Oracle 無原生 Auto Increment，標準做法是結合 Sequence (序列) 與 Trigger (觸發器) 實現主鍵自動產生。本文完整說明語法選項、建立範例、替代方案與效能考量。

Sequence 語法完整選項

CREATE SEQUENCE sequence_name
  [INCREMENT BY n]          -- 步長，預設 1，負值為遞減
  [START WITH n]            -- 初始值，預設 1
  [{MAXVALUE n | NOMAXVALUE}] -- 最大值，遞增預設 10^27
  [{MINVALUE n | NOMINVALUE}] -- 最小值，遞減預設 -10^26
  [{CYCLE | NOCYCLE}]       -- 到達極值是否循環，預設 NOCYCLE
  [{CACHE n | NOCACHE}];    -- 記憶體快取數量，預設 20

實戰建立範例

1. 建立遞增序列

CREATE SEQUENCE SEQ_TEST
  INCREMENT BY 1
  START WITH 1
  MINVALUE 1 NOMAXVALUE
  NOCYCLE
  CACHE 20;  -- 建議快取提升效能

2. 建立 BEFORE INSERT 觸發器

CREATE OR REPLACE TRIGGER TRG_TEST
BEFORE INSERT ON TEST
FOR EACH ROW
BEGIN
  SELECT SEQ_TEST.NEXTVAL INTO :NEW.TEST_ID FROM DUAL;
END;
/
-- 記得以 / 結束多行 PL/SQL 區塊

替代方案：SQL 直接呼叫序列

INSERT INTO TEST (TEST_ID, COL1, COL2)
VALUES (SEQ_TEST.NEXTVAL, 'value1', 'value2');

優點：簡單直觀，無觸發器額外開銷。缺點：需在每條 INSERT 陳述式中明確呼叫，易遺漏。

進階效能優化建議

• CACHE 設定：高併發插入環境建議 CACHE 100~1000，減少字典鎖競爭；但需接受重啟時快取值遺失造成間隙。
• ORDER/NOORDER：RAC 環境需 ORDER 保證全域有序，單機 NOORDER 效能更好。
• 12c+ Identity Column：Oracle 12c 引入 GENERATED AS IDENTITY 原生支援，簡化語法並優化效能，優先考慮。

常見管理操作

• 查看序列現值：SELECT SEQ_TEST.CURRVAL FROM DUAL;
• 查看序列定義：SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME='SEQ_TEST';
• 刪除序列：DROP SEQUENCE SEQ_TEST;
• 重設序列值：需刪除重建，或用 INCREMENT BY -n 配合 NEXTVAL 調整

Sequence + Trigger 是 Oracle 傳統自增主鍵標準解法，掌握細節能避開效能陷阱與維護坑點。