Oracle 自動遞增鍵實作完全指南：Sequence + Trigger 傳統架構與 12c Identity 現代化遷移

兩種實作世代對比

傳統架構：Sequence + Trigger 完整實作

1. 建立資料表

CREATE TABLE s_depart (
  depart_id   NUMBER NOT NULL,
  depart_name NVARCHAR2(40) NOT NULL,
  depart_order NUMBER DEFAULT 0,
  CONSTRAINT pk_s_depart PRIMARY KEY (depart_id)
);

2. 建立 Sequence

CREATE SEQUENCE s_s_depart
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  NOCACHE
  NOORDER;  -- RAC 環境建議 ORDER 保證全域有序

參數解析：

• CACHE n：預載 n 個值至記憶體，提升效能；崩潰會導致跳號。關鍵業務建議 NOCACHE
• CYCLE：到達最大值後循環，PK 必須 NOCYCLE
• ORDER：RAC 環境保證跨節點有序，單機可 NOORDER 提升效能

3. 建立 Trigger

CREATE OR REPLACE TRIGGER tri_test_id
BEFORE INSERT ON s_depart
FOR EACH ROW
DECLARE
  v_next_id NUMBER;
BEGIN
  IF :new.depart_id IS NULL OR :new.depart_id = 0 THEN
    SELECT s_s_depart.NEXTVAL INTO v_next_id FROM DUAL;
    :new.depart_id := v_next_id;
  END IF;
END;
/

關鍵點：

• BEFORE INSERT：在寫入前賦值
• :new.depart_id：參考新列值
• 條件判斷允許應用層選擇性指定值（傳入非 NULL/0 時不覆蓋）

4. 測試驗證

INSERT INTO s_depart (depart_name, depart_order) VALUES ('IT', 1);
INSERT INTO s_depart (depart_id, depart_name, depart_order) VALUES (100, 'HR', 2); -- 指定值
INSERT INTO s_depart (depart_name, depart_order) VALUES ('Finance', 3);

SELECT * FROM s_depart ORDER BY depart_id;
-- 結果：1 IT, 3 Finance, 100 HR

現代化架構：12c Identity Column

CREATE TABLE s_depart_modern (
  depart_id   NUMBER GENERATED ALWAYS AS IDENTITY,
  depart_name NVARCHAR2(40) NOT NULL,
  depart_order NUMBER DEFAULT 0,
  CONSTRAINT pk_s_depart_modern PRIMARY KEY (depart_id)
);

等價於：

-- 內部自動建立序列 SYS_ISEQ$_xxxxxx
-- 內部自動建立隱藏觸發器

進階選項

-- 允許指定值（相當於 BY DEFAULT）
depart_id NUMBER GENERATED BY DEFAULT AS IDENTITY

-- 起始值、間距、快取
depart_id NUMBER GENERATED ALWAYS AS IDENTITY
  START WITH 1000 INCREMENT BY 10 CACHE 20

Sequence 進階管理操作

-- 查看序列定義
SELECT sequence_name, min_value, max_value, increment_by, cache_size, last_number
FROM user_sequences WHERE sequence_name = 'S_S_DEPART';

-- 修改序列（不可改 START WITH，需 DROP/CREATE）
ALTER SEQUENCE s_s_depart INCREMENT BY 5 MAXVALUE 10000 CYCLE NOCACHE;

-- 取得當前值/下一值
SELECT s_s_depart.CURRVAL FROM DUAL;  -- 需先在 Session 呼叫過 NEXTVAL
SELECT s_s_depart.NEXTVAL FROM DUAL;

-- 刪除序列
DROP SEQUENCE s_s_depart;

實務常見陷阱與解決

遷移檢核表（Sequence+Trigger → Identity）

1. 確認目標環境 12.1+
2. 識別所有使用序列的表與欄位
3. 測試環境建立 Identity 表，驗證應用程式相容性（特別是指定值插入邏輯）
4. 匯出資料 → 建立 Identity 結構 → 匯入資料 → 手動對齊序列值
5. 移除舊 Trigger、Sequence 物件
6. 更新文件與部署腳本

完整範例腳本（可直接執行）

-- 清理舊物件
DROP TABLE s_depart PURGE;
DROP SEQUENCE s_s_depart;
DROP TRIGGER tri_test_id;

-- 傳統實作
CREATE TABLE s_depart (depart_id NUMBER NOT NULL, depart_name NVARCHAR2(40) NOT NULL, CONSTRAINT pk_s_depart PRIMARY KEY (depart_id));
CREATE SEQUENCE s_s_depart START WITH 1 NOCACHE;
CREATE OR REPLACE TRIGGER tri_test_id BEFORE INSERT ON s_depart FOR EACH ROW BEGIN IF :new.depart_id IS NULL THEN SELECT s_s_depart.NEXTVAL INTO :new.depart_id FROM DUAL; END IF; END;
/

-- 現代實作
CREATE TABLE s_depart_new (depart_id NUMBER GENERATED ALWAYS AS IDENTITY, depart_name NVARCHAR2(40) NOT NULL, CONSTRAINT pk_s_depart_new PRIMARY KEY (depart_id));

-- 測試
INSERT INTO s_depart (depart_name) VALUES ('Traditional');
INSERT INTO s_depart_new (depart_name) VALUES ('Modern');
SELECT * FROM s_depart;
SELECT * FROM s_depart_new;