Oracle SEQUENCE 完整使用手冊：建立、取值、修改、刪除、觸發器自動填充主鍵

1. 建立 Sequence

CREATE SEQUENCE SEQ_TEST
INCREMENT BY 1        -- 每次遞增值
START WITH 1          -- 起始值
NOMAXVALUE            -- 無上限 (或 MAXVALUE 999999)
NOCYCLE               -- 不循環 (達上限報錯)
CACHE 10;             -- 記憶體預取 10 個值，提升效能；NOCACHE 可避免跳號

2. 取得值

• SEQ_TEST.NEXTVAL：遞增並回傳新值 (首次回傳 START WITH 值)。
• SEQ_TEST.CURRVAL：回傳當前 Session 最後一次 NEXTVAL 的值 (必須先呼叫過 NEXTVAL)。

SELECT SEQ_TEST.NEXTVAL FROM DUAL;  -- 1
SELECT SEQ_TEST.CURRVAL FROM DUAL;  -- 1
SELECT SEQ_TEST.NEXTVAL FROM DUAL;  -- 2

3. 允許使用 Sequence 的 SQL 場景

• 不含子查詢/Snapshot/View 的 SELECT 選單
• INSERT 子查詢 / VALUES 子句
• UPDATE SET 子句

4. 修改 Sequence

ALTER SEQUENCE SEQ_TEST MAXVALUE 9999999;
-- 無法修改 START WITH，需 DROP 再 CREATE

5. 刪除 Sequence

DROP SEQUENCE SEQ_TEST;

6. 實戰：觸發器自動填充主鍵

-- Sequence
CREATE SEQUENCE SEQ_ID
MINVALUE 1 MAXVALUE 99999999
START WITH 1 INCREMENT BY 1 NOCACHE ORDER;

-- Trigger
CREATE OR REPLACE TRIGGER TRI_TEST_ID
BEFORE INSERT ON S_DEPART
FOR EACH ROW
DECLARE
  V_NEXTID NUMBER;
BEGIN
  IF :NEW.DEPARTID IS NULL OR :NEW.DEPARTID = 0 THEN
    SELECT SEQ_ID.NEXTVAL INTO V_NEXTID FROM DUAL;
    :NEW.DEPARTID := V_NEXTID;
  END IF;
END;
/

注意事項

• CACHE 可能導致跳號 (Crash/Shutdown Abort 丟失快取值)，關鍵業務主鍵建議 NOCACHE。
• 同一 Statement 多次 NEXTVAL 會產生不同值。
• Sequence 獨立於 Table，可跨表共用。