Oracle Sequence 序列完整指南：建立、取值、快取、觸發器自增

1. 建立序列語法

CREATE SEQUENCE my_seq
  START WITH 1           -- 初始值
  INCREMENT BY 1         -- 遞增量 (可負數)
  MAXVALUE 9999999999    -- 最大值 (NOMAXVALUE 無上限)
  MINVALUE 1             -- 最小值
  CYCLE                  -- 循環 (NOCYCLE 不循環，預設)
  CACHE 20               -- 記憶體預取數量 (NOCACHE 不快取)
  NOORDER;               -- 不保證順序 (ORDER 強制順序，RAC 必須)

2. 核心特性與陷阱

• 首次 NEXTVAL 回傳 START WITH 值；後續每次 + INCREMENT BY。
• CURRVAL：僅在 Session 執行過 NEXTVAL 後可用，否則 ORA-08002。
• CACHE 跳號風險：非正常關機 (SHUTDOWN ABORT) 導致快取值遺失，下次啟動跳過。關鍵業務建議 NOCACHE。
• RAC 環境：必須加 ORDER 或 NOCACHE 確保全域遞增順序，否則各節點各自快取導致亂序。

3. 取值方式

-- 單純取值
SELECT my_seq.NEXTVAL FROM dual;
SELECT my_seq.CURRVAL FROM dual;

-- 插入時直接使用
INSERT INTO emp (empno, ename) VALUES (my_seq.NEXTVAL, 'SMITH');

4. 觸發器實現自增主鍵 (傳統寫法，11g 前)

CREATE OR REPLACE TRIGGER emp_bir
BEFORE INSERT ON emp
FOR EACH ROW
WHEN (NEW.empno IS NULL)
BEGIN
  SELECT emp_seq.NEXTVAL INTO :NEW.empno FROM dual;
END;
/
-- 測試
INSERT INTO emp (ename) VALUES ('ALLEN');  -- empno 自動填入

5. Oracle 12c+ 內建身分欄 (Identity Column) — 推薦

CREATE TABLE emp (
  empno NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  ename VARCHAR2(50)
);
-- 或 GENERATED BY DEFAULT ON NULL (允許手動指定)
-- 完全取代 Sequence + Trigger，效能更好、語意清晰。

6. 管理維護指令

-- 修改參數 (除 START WITH 外皆可)
ALTER SEQUENCE emp_seq INCREMENT BY 10 MAXVALUE 10000 CYCLE NOCACHE;

-- 重設起始值：必須 DROP 再 CREATE
DROP SEQUENCE emp_seq;
CREATE SEQUENCE emp_seq START WITH 1000 ...;

-- 查詢序列狀態
SELECT sequence_name, last_number, cache_size, cycle_flag, order_flag
FROM   user_sequences
WHERE  sequence_name = 'EMP_SEQ';

7. 參數初始化影響

• SEQUENCE_CACHE_ENTRIES：字典快取序列定義的項目數，影響 NEXTVAL 解析速度。