Oracle 實現欄位自動遞增的兩種方案：Sequence + Trigger / 顯式呼叫

前言

MySQL 等資料庫支援 AUTO_INCREMENT，Oracle 需自行實作。核心皆仰賴 Sequence (序列)，搭配 Trigger (觸發器) 或 SQL 顯式呼叫 兩種模式。

序列建立語法回顧

CREATE SEQUENCE 序列名
  [INCREMENT BY n]          -- 步長，預設 1，負值為遞減
  [START WITH n]            -- 起始值，預設 1
  [{MAXVALUE n | NOMAXVALUE}] -- 最大值，預設 NOMAXVALUE (10^27)
  [{MINVALUE n | NOMINVALUE}] -- 最小值，預設 NOMINVALUE
  [{CYCLE | NOCYCLE}]       -- 循環與否，預設 NOCYCLE
  [{CACHE n | NOCACHE}];    -- 快取數量，預設 CACHE 20

方案一：Sequence + Trigger (自動、透明)

-- 1. 建立序列
CREATE SEQUENCE seq_t_recv
  MINVALUE 1 MAXVALUE 9999999
  START WITH 1 INCREMENT BY 1
  CACHE 50;

-- 2. 建立資料表
CREATE TABLE recv_msg (
  id          NUMBER,
  messageid   VARCHAR2(32),
  contents    VARCHAR2(2000),
  app_flg     VARCHAR2(100),
  phonenumber VARCHAR2(2000),
  updatetime  DATE DEFAULT SYSDATE
);

-- 3. 建立 BEFORE INSERT 觸發器
CREATE OR REPLACE TRIGGER recv_trig
BEFORE INSERT ON recv_msg
FOR EACH ROW
BEGIN
  SELECT seq_t_recv.NEXTVAL INTO :NEW.id FROM dual;
END;
/

-- 4. 測試
INSERT INTO recv_msg (messageid, contents) VALUES ('msg001', 'test');
SELECT * FROM recv_msg;  -- id 自動為 1

優點：應用端無感知，SQL 簡潔。
缺點：觸發器微額效能開銷；大量批次匯入時可考慮停用觸發器改用方案二。

方案二：Sequence + 顯式呼叫 (手動、可控)

-- 1. 建立序列
CREATE SEQUENCE seq_on_test
  INCREMENT BY 1 START WITH 1
  NOMAXVALUE NOCYCLE NOCACHE;

-- 2. 建表
CREATE TABLE test (
  id       INTEGER,
  stu_name NVARCHAR2(4),
  stu_age  NUMBER
);

-- 3. 插入時顯式取 NEXTVAL
INSERT INTO test VALUES (seq_on_test.NEXTVAL, 'Mary', 15);
INSERT INTO test VALUES (seq_on_test.NEXTVAL, 'Tom', 16);

-- 4. 查詢序列目前值
SELECT seq_on_test.CURRVAL FROM dual;  -- 當前 session 最後取得的值
SELECT seq_on_test.NEXTVAL FROM dual;  -- 取得下一值並前進

優點：完全可控、批次匯入效能佳、易於跨表共用序列。
缺點：每條 INSERT 必須撰寫序列呼叫，維護成本稍高。

進階：Oracle 12c Identity Column

CREATE TABLE test_modern (
  id       NUMBER GENERATED ALWAYS AS IDENTITY,
  stu_name NVARCHAR2(4),
  stu_age  NUMBER
);
-- INSERT 完全不需指定 id，自動遞增

總結建議

• 一般 OLTP 應用：方案一 (Trigger) 開發效率最高。
• 大量資料載入 (ETL/批次)：方案二 (顯式呼叫) 或 SQL*Loader / 外部表 直接指定 ID。
• 12c 以上新專案：優先使用 Identity Column，語法標準、效能最佳。