Oracle 自增欄位實現：Sequence + Trigger 完整教學

核心概念

Oracle 無原生 `AUTO_INCREMENT`/`IDENTITY` (12c 前)，標準做法：Sequence 產生序號 + BEFORE INSERT Trigger 自動填入。

完整建立步驟

1. 建立測試表

CREATE TABLE userlogin (
  id   NUMBER(6) NOT NULL,
  name VARCHAR2(30) NOT NULL PRIMARY KEY
) TABLESPACE users;

2. 建立 Sequence

CREATE SEQUENCE userlogin_seq
  INCREMENT BY 1
  START WITH 1
  MINVALUE 1
  MAXVALUE 9999999999999
  NOCACHE
  ORDER;

參數解析：

• `NOCACHE`：不預先緩存，避免異常關機導致序號跳號（效能稍降）
• `ORDER`：RAC 環境下保證跨節點序號有序

3. 建立 BEFORE INSERT Trigger

CREATE OR REPLACE TRIGGER userlogin_trigger
BEFORE INSERT ON userlogin
FOR EACH ROW
BEGIN
  SELECT userlogin_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/
SHOW ERRORS

4. 測試驗證

INSERT INTO userlogin (name) VALUES ('Alice');
INSERT INTO userlogin (name) VALUES ('Bob');
SELECT * FROM userlogin;

-- 結果
ID NAME
--- -----
1  Alice
2  Bob

Sequence 進階知識

建立語法完整選項

CREATE SEQUENCE emp_sequence
  INCREMENT BY 1      -- 每次增量
  START WITH 1        -- 起始值
  NOMAXVALUE          -- 無上限 (或 MAXVALUE 10000)
  NOCYCLE             -- 不循環 (或 CYCLE 循環)
  CACHE 10;           -- 記憶體預取 10 個 (預設 20)

CURRVAL vs NEXTVAL

注意：同一語句多次引用 `NEXTVAL` 會產生不同值。

可使用 Sequence 的 SQL 場景

• 不含子查詢/SNAPSHOT/VIEW 的 `SELECT` 列表
• `INSERT` 的 `VALUES` 子句
• `INSERT ... SELECT` 的子查詢
• `UPDATE` 的 `SET` 子句

範例：直接在 INSERT 使用

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (emp_seq.NEXTVAL, 'LEWIS', 'CLERK', 7902, SYSDATE, 1200, NULL, 20);

SELECT emp_seq.CURRVAL FROM dual;

ALTER SEQUENCE

ALTER SEQUENCE emp_sequence
  INCREMENT BY 10
  MAXVALUE 10000
  CYCLE
  NOCACHE;

無法修改 `START WITH`，需 `DROP` 後重建。

CACHE 與跳號風險

`CACHE n` 會在記憶體預存 n 個值，效能較好。但 異常關機 (SHUTDOWN ABORT) 會導致快取中未用序號遺失，造成跳號。關鍵業務主鍵建議 `NOCACHE`。

刪除 Sequence

DROP SEQUENCE order_seq;

12c 新特性：IDENTITY 欄位

Oracle 12c 引入標準身分欄位，語法更簡潔：

CREATE TABLE t (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR2(30)
);

或允許手動指定：

id NUMBER GENERATED BY DEFAULT AS IDENTITY