Oracle 11g 實現自增主鍵：Sequence + Trigger 完整實作

Oracle 11g 實現自增主鍵：Sequence + Trigger 完整實作

Oracle 11g 及之前版本無 IDENTITY 或 AUTO_INCREMENT 關鍵字，需結合 Sequence（序列） 與 Trigger（觸發器） 實現主鍵自動遞增。本文提供標準範本與進階寫法。

步驟 1：建立測試表

create table TEST (
  ID int PRIMARY KEY,
  TEST1 varchar2(20),
  TEST2 varchar2(20),
  TEST3 varchar2(20)
);

步驟 2：建立 Sequence

create sequence SEQ_TEST
  minvalue 1
  maxvalue 999999999999999999999999999
  start with 1
  increment by 1
  nocycle
  nocache;

參數說明：

• NOCACHE：不預先緩存序列值，避免例項重啟導致間隙（但效能稍低）
• NOCYCLE：達最大值不循環，防止主鍵衝突

步驟 3：建立 BEFORE INSERT Trigger

CREATE OR REPLACE TRIGGER TG_TEST
BEFORE INSERT ON TEST
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
  SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

關鍵點：

• BEFORE INSERT 確保在寫入前賦值
• FOR EACH ROW 逐列觸發
• WHEN (NEW.ID IS NULL) 僅在應用未提供 ID 時自動產生，保留手動指定彈性

驗證測試

insert into TEST (TEST1, TEST2, TEST3) values ('A', 'B', 'C');
insert into TEST (ID, TEST1, TEST2, TEST3) values (999, 'X', 'Y', 'Z'); -- 手動指定
insert into TEST (TEST1, TEST2, TEST3) values ('D', 'E', 'F');

select * from TEST;
/* 結果：
ID   TEST1 TEST2 TEST3
1    A     B     C
999  X     Y     Z
1000 D     E     F
*/

進階觸發器範本：帶預設值邏輯

CREATE OR REPLACE TRIGGER TRI_SCORE
BEFORE INSERT OR UPDATE ON XUANKE
FOR EACH ROW
BEGIN
  IF :NEW.SCORE IS NULL THEN
    :NEW.SCORE := 0;
  END IF;
END;
/

此範例示範在 INSERT 或 UPDATE 時，若 SCORE 為 NULL 則自動設為 0。

Oracle 12c+ 更簡潔寫法（參考）

create table TEST (
  ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  TEST1 VARCHAR2(20)
);

12c 引入 GENERATED AS IDENTITY，內部自動建立 Sequence，無需手寫 Trigger。

常見坑與最佳實踐

1. 不要在 Trigger 中 COMMIT：Trigger 屬於觸發交易一部分，隱式提交會導致 ORA-04092。
2. 大量載入資料時：考慮暫停 Trigger ALTER TRIGGER TG_TEST DISABLE;，載入完再 ENABLE，或直接在 SQL*Loader / Data Pump 指定 Sequence。
3. NOCACHE 會增加磁碟 I/O，高併發場景建議 CACHE 1000 並接受間隙。
4. 若需「無間隙」連號，請改用 MAX(ID)+1 邏輯（但嚴重影響併發效能），通常業務不要求連號。

掌握 Sequence + Trigger 模式，即可在 Oracle 11g 完美實現自增主鍵。