萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 自動增長欄位實現全解析:Sequence + Trigger 經典模式

為什麼 Oracle 沒有原生 Auto-Increment?

與 MySQL、SQL Server 不同,Oracle 歷來採用「序列 + 觸發器」的組合模式實現主鍵自動增長。這種設計雖然稍顯繁瑣,但極其靈活:序列可獨立於表存在、支援跨表共享、提供緩存與循環等高階選項。本文將從零開始構建一套生產級的自增主鍵方案。

第一步:建立序列

序列是產生唯一數值的核心對象。以下範例創建一個名為 `INNERID` 的序列,涵蓋常用參數說明:

CREATE SEQUENCE INNERID
    MINVALUE 1
    MAXVALUE 99999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20          -- 緩存 20 個值於記憶體,減少磁盤 I/O,提升高併發性能
    ORDER;            -- 確保 RAC 環境下按請求順序分配值,單實例可省略

關鍵參數解析:

  • CACHE:預分配數值至 SGA,避免每次 `NEXTVAL` 都更新數據字典。若實例崩潰,緩存中的值會丟失導致「跳號」,但不影響唯一性。
  • ORDER:僅在 RAC 必須;單實例環境預設即有序。

第二步:建立業務表

表結構中 不定義默認值,主鍵欄位僅聲明 `NOT NULL`:

CREATE TABLE admin (
    id       NUMBER(20) NOT NULL,  -- 使用 NUMBER 而非 VARCHAR2 存儲數值型主鍵
    name     VARCHAR2(10),
    CONSTRAINT pk_admin PRIMARY KEY (id)
);

第三步:創建 BEFORE INSERT 觸發器

觸發器在每行插入前自動從序列取值填充主鍵:

CREATE OR REPLACE TRIGGER admin_tg
BEFORE INSERT ON admin
FOR EACH ROW
BEGIN
    SELECT INNERID.NEXTVAL INTO :NEW.id FROM dual;
END;
/

設計要點:

  • 使用 `:NEW.id` 引用新行的欄位值。
  • 若應用層顯式指定了 `id` 值,觸發器會覆蓋它。如需「應用指定優先、否則自動生成」,可加判斷:IF :NEW.id IS NULL THEN ... END IF;。

驗證測試

INSERT INTO admin (name) VALUES ('zdz');
INSERT INTO admin (name) VALUES ('alice');
INSERT INTO admin (name) VALUES ('bob');

SELECT * FROM admin;
-- 結果:
-- ID  NAME
-- 1   zdz
-- 2   alice
-- 3   bob

進階話題:12c Identity Column

Oracle 12c 引入標準 SQL 的 `GENERATED AS IDENTITY` 語法,可簡化為:

CREATE TABLE admin_new (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    name VARCHAR2(10)
);

底層仍自動創建序列與觸發器,但語法更簡潔、可移植性更強。若專案已升級至 12c+,強烈建議採用新語法。

常見誤區與最佳實踐

  1. 不要在觸發器中使用 `CURRVAL`:必須用 `NEXTVAL` 獲取下一值;`CURRVAL` 只能在同一 Session 已調用過 `NEXTVAL` 後使用。
  2. 序列命名規範:建議採用 `SEQ_表名` 或 `表名_SEQ` 格式,便於維護。
  3. 大批量載入時關閉觸發器:使用 `ALTER TRIGGER admin_tg DISABLE;` 配合直接插入序列值,大幅提升載入速度,完成後再啟用。
copyright © 萬盛學電腦網 all rights reserved