您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 自增主鍵實戰：Sequence + Trigger 實現 IDENTITY 效果 (附圖解流程)

編輯：Oracle 資料庫基礎

業務場景

表 `USERINFO` 需要 `ID` 欄位自動遞增，Oracle 11g/12c 前無原生 Identity，需組合 **Sequence (序列) + Before Insert Trigger (觸發器)** 實現。

三步建立標準流程

1. 建表

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

2. 建立 Sequence

CREATE SEQUENCE seq_userinfo
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20
  NOORDER
  NOCYCLE;

`CACHE 20`：記憶體預分配 20 個值，減少字典鎖，RAC 建議 `NOORDER`。
`NOCYCLE`：達到 MAXVALUE 不循環，避免主鍵衝突。

3. 建立 Before Insert Trigger

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

運作流程圖解 (文字版)

INSERT INTO userinfo (name) VALUES ('Charlie')
        │
        ▼
┌─────────────────────────────┐
│  BEFORE INSERT TRIGGER      │
│  trg_userinfo_pk            │
│  1. SELECT seq.NEXTVAL      │
│  2. :NEW.id := 值           │
└─────────────────────────────┘
        │
        ▼
INSERT 實際寫入 (id=3, name='Charlie')
        │
        ▼
COMMIT → Sequence 值持久化，下次 NEXTVAL=4

進階：12c+ 原生 Identity Column 遷移指南

-- 12c+ 直接建表
CREATE TABLE userinfo_new (
  id   NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 CACHE 20),
  name VARCHAR2(40)
);

-- 遷移舊表 (12.2+)
ALTER TABLE userinfo MODIFY id GENERATED ALWAYS AS IDENTITY;
-- 並手工 DROP SEQUENCE seq_userinfo; DROP TRIGGER trg_userinfo_pk;

避坑指南

問題	原因	解決
主鍵重複 ORA-00001	手工 INSERT 指定 ID 與 Sequence 值衝突	應用層禁止指定 ID；或改用 `GENERATED BY DEFAULT ON NULL AS IDENTITY` (12c+)
Sequence 值跳躍 (非連續)	緩存值未用完實例重啟/崩潰丟失	業務可接受非連續；極度要求連續需 `NOCACHE` (效能損)
RAC 環境 Sequence 爭用	多實例搶字典鎖獲取 NEXTVAL	加大 `CACHE` (如 1000)、使用 `NOORDER`
Trigger 失效/編譯錯誤	DDL 變更導致 Trigger INVALID	DDL 後檢查 `USER_TRIGGERS.STATUS`，需時 `ALTER TRIGGER ... COMPILE`