Oracle 主鍵自動生成方案：序列表 + 存儲過程實現高併發流水號

Oracle 主鍵自動生成方案：序列表 + 存儲過程實現高併發流水號

Oracle 12c 前無原生 IDENTITY 欄位（12c+ 有 GENERATED AS IDENTITY），傳統方案用 SEQUENCE + TRIGGER。但業務常需複雜流水號規則：前綴 + 日期（年/月/日重置）+ 遞增序號 + 分隔符。本文提供一套生產級「序列配置表 + 序列化存儲過程」方案，支援高併發、多租戶、動態規則。

一、序列配置表 T_EB_SYS_DN_SEQUENCE_CONFIG

CREATE TABLE T_EB_SYS_DN_SEQUENCE_CONFIG (
  SEQUENCE_ID        VARCHAR2(36) DEFAULT SYS_GUID() NOT NULL,  -- PK
  SEQUENCE_NAME      VARCHAR2(50) NOT NULL,                     -- 唯一業務名稱
  SEQUENCE_DESC      VARCHAR2(200),
  SEQUENCE_LEN       INTEGER DEFAULT 4 NOT NULL,                -- 序號長度（如 4→0001）
  RESET_TYPE         INTEGER DEFAULT 2 NOT NULL,                -- 0不重置 1年 2月 3日
  SEPARATOR          VARCHAR2(10) DEFAULT '-',                  -- 分隔符
  PREFIX             VARCHAR2(50),                              -- 前綴
  DATE_FORMAT        VARCHAR2(50) DEFAULT 'YYYYMM',             -- 日期格式
  CURRENT_VALUE      INTEGER DEFAULT 0 NOT NULL,                -- 當前序號
  INITIAL_VALUE      INTEGER DEFAULT 1 NOT NULL,                -- 起始值
  STEP               INTEGER DEFAULT 1 NOT NULL,                -- 步長
  CREATOR            VARCHAR2(50) NOT NULL,
  CREATED_DATE       TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
  MODIFIER           VARCHAR2(50) NOT NULL,
  LAST_UPDATED_DATE  TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
  IS_ENABLE          VARCHAR2(2) DEFAULT '1' NOT NULL,          -- 1啟用 0停用
  SDP_USER_ID        VARCHAR2(36) DEFAULT NVL(SYS_CONTEXT('SDP_CONTEXT','userid'),'88888') NOT NULL,
  SDP_ORG_ID         VARCHAR2(36) DEFAULT NVL(SYS_CONTEXT('SDP_CONTEXT','orgid'),'2') NOT NULL,
  UPDATE_CONTROL_ID  VARCHAR2(36) DEFAULT SYS_GUID() NOT NULL   -- 樂觀鎖版本號
) TABLESPACE EB_DATA_TBS;

-- 關鍵約束
ALTER TABLE T_EB_SYS_DN_SEQUENCE_CONFIG ADD CONSTRAINT PK_SEQ_CFG PRIMARY KEY (SEQUENCE_ID);
ALTER TABLE T_EB_SYS_DN_SEQUENCE_CONFIG ADD CONSTRAINT UK_SEQ_NAME UNIQUE (SEQUENCE_NAME);
-- 重置類型與日期格式一致性檢查
ALTER TABLE T_EB_SYS_DN_SEQUENCE_CONFIG ADD CONSTRAINT CK_SEQ_RESET_TYPE CHECK (RESET_TYPE IN (0,1,2,3));
ALTER TABLE T_EB_SYS_DN_SEQUENCE_CONFIG ADD CONSTRAINT CK_SEQ_DATE_FMT CHECK (
  RESET_TYPE=0 OR
  (RESET_TYPE=1 AND INSTR(UPPER(DATE_FORMAT),'Y')>0) OR
  (RESET_TYPE=2 AND INSTR(UPPER(DATE_FORMAT),'Y')>0 AND INSTR(UPPER(DATE_FORMAT),'M')>0) OR
  (RESET_TYPE=3 AND INSTR(UPPER(DATE_FORMAT),'Y')>0 AND INSTR(UPPER(DATE_FORMAT),'M')>0 AND INSTR(UPPER(DATE_FORMAT),'D')>0)
);

二、核心存儲過程 P_EB_GET_SEQUENCE

CREATE OR REPLACE PROCEDURE P_EB_GET_SEQUENCE(
  PARA_SEQUENCE_NAME      IN  VARCHAR2,   -- 序列配置名
  PARA_DYNAMIC_CONTENT    IN  VARCHAR2,   -- 動態內容（如業務分類代碼）
  PARA_SEQUENCE           OUT VARCHAR2    -- 返回生成的流水號
) IS
  -- 變數宣告
  V_SEQ_LEN         INTEGER;
  V_RESET_TYPE      INTEGER;
  V_SEPARATOR       VARCHAR2(10);
  V_PREFIX          VARCHAR2(50);
  V_DATE_FMT        VARCHAR2(50);
  V_CURR_VAL        INTEGER;
  V_INIT_VAL        INTEGER;
  V_STEP            INTEGER;
  V_LAST_UPD        TIMESTAMP;
  V_YYYY            VARCHAR2(4);
  V_MM              VARCHAR2(2);
  V_DD              VARCHAR2(2);
  V_HH              VARCHAR2(2);
  V_II              VARCHAR2(2);
  V_SS              VARCHAR2(2);
  V_DATE_STR_FULL   VARCHAR2(50);
  V_DATE_STR        VARCHAR2(50);
BEGIN
  -- 1. 序列化事務隔離級別：防止併發產生重複流水號
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

  -- 2. 鎖定配置行（SELECT ... FOR UPDATE 隱含在隔離級別中）
  SELECT SEQUENCE_LEN, RESET_TYPE, SEPARATOR, PREFIX, UPPER(DATE_FORMAT),
         CURRENT_VALUE, INITIAL_VALUE, STEP, LAST_UPDATED_DATE
  INTO   V_SEQ_LEN, V_RESET_TYPE, V_SEPARATOR, V_PREFIX, V_DATE_FMT,
         V_CURR_VAL, V_INIT_VAL, V_STEP, V_LAST_UPD
  FROM   T_EB_SYS_DN_SEQUENCE_CONFIG
  WHERE  SEQUENCE_NAME = PARA_SEQUENCE_NAME
  AND    IS_ENABLE = '1';

  -- 3. 解析系統日期組件
  V_DATE_STR_FULL := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');
  V_YYYY := SUBSTR(V_DATE_STR_FULL, 1, 4);
  V_MM   := SUBSTR(V_DATE_STR_FULL, 6, 2);
  V_DD   := SUBSTR(V_DATE_STR_FULL, 9, 2);
  V_HH   := SUBSTR(V_DATE_STR_FULL, 12, 2);
  V_II   := SUBSTR(V_DATE_STR_FULL, 15, 2);
  V_SS   := SUBSTR(V_DATE_STR_FULL, 18, 2);

  -- 4. 組裝日期字串（支援 YYYY/YY/MM/DD/HH/II/SS 任意組合）
  IF V_DATE_FMT IS NOT NULL THEN
    V_DATE_STR := REPLACE(V_DATE_FMT, 'YYYY', V_YYYY);
    V_DATE_STR := REPLACE(V_DATE_STR, 'YY', SUBSTR(V_YYYY, -2));
    V_DATE_STR := REPLACE(V_DATE_STR, 'MM', V_MM);
    V_DATE_STR := REPLACE(V_DATE_STR, 'M', SUBSTR(V_MM, -1));
    V_DATE_STR := REPLACE(V_DATE_STR, 'DD', V_DD);
    V_DATE_STR := REPLACE(V_DATE_STR, 'D', SUBSTR(V_DD, -1));
    V_DATE_STR := REPLACE(V_DATE_STR, 'HH', V_HH);
    V_DATE_STR := REPLACE(V_DATE_STR, 'H', SUBSTR(V_HH, -1));
    V_DATE_STR := REPLACE(V_DATE_STR, 'II', V_II);
    V_DATE_STR := REPLACE(V_DATE_STR, 'I', SUBSTR(V_II, -1));
    V_DATE_STR := REPLACE(V_DATE_STR, 'SS', V_SS);
    V_DATE_STR := REPLACE(V_DATE_STR, 'S', SUBSTR(V_SS, -1));
  END IF;

  -- 5. 重置邏輯：依 RESET_TYPE 比較當前日期與上次更新日期
  IF V_RESET_TYPE <> 0 THEN
    IF (V_RESET_TYPE = 1 AND TO_CHAR(SYSDATE,'YYYY') > TO_CHAR(V_LAST_UPD,'YYYY')) OR
       (V_RESET_TYPE = 2 AND TO_CHAR(SYSDATE,'YYYYMM') > TO_CHAR(V_LAST_UPD,'YYYYMM')) OR
       (V_RESET_TYPE = 3 AND TO_CHAR(SYSDATE,'YYYYMMDD') > TO_CHAR(V_LAST_UPD,'YYYYMMDD')) THEN
      V_CURR_VAL := V_INIT_VAL;  -- 重置為初始值
    ELSE
      V_CURR_VAL := V_CURR_VAL + V_STEP;  -- 正常遞增
    END IF;
  ELSE
    V_CURR_VAL := V_CURR_VAL + V_STEP;  -- 不重置，持續遞增
  END IF;

  -- 6. 組裝最終流水號：前綴 + 分隔符 + 動態內容 + 分隔符 + 日期 + 分隔符 + 零填充序號
  PARA_SEQUENCE :=
    CASE WHEN V_PREFIX IS NOT NULL THEN V_PREFIX || V_SEPARATOR END ||
    CASE WHEN PARA_DYNAMIC_CONTENT IS NOT NULL THEN PARA_DYNAMIC_CONTENT || V_SEPARATOR END ||
    CASE WHEN V_DATE_STR IS NOT NULL THEN V_DATE_STR || V_SEPARATOR END ||
    LPAD(TO_CHAR(V_CURR_VAL), V_SEQ_LEN, '0');

  -- 7. 更新配置表
  UPDATE T_EB_SYS_DN_SEQUENCE_CONFIG
  SET    CURRENT_VALUE = V_CURR_VAL,
         LAST_UPDATED_DATE = SYSTIMESTAMP,
         UPDATE_CONTROL_ID = SYS_GUID()
  WHERE  SEQUENCE_NAME = PARA_SEQUENCE_NAME;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;  -- 拋給調用端處理
END;

三、調用範例

  設計點方案優勢
  高併發唯一性SET TRANSACTION ISOLATION LEVEL SERIALIZABLE + 行鎖資料庫層面保證序列化，無需應用層分佈式鎖
  樂觀鎖防更新丟失UPDATE_CONTROL_ID = SYS_GUID() 每次更新重生若併發 UPDATE 衝突，SERIALIZABLE 會拋 ORA-08177，應用重試即可
  多租戶隔離SDP_USER_ID, SDP_ORG_ID 記錄歸屬支援 SaaS 多租戶共用序列表，邏輯隔離
  動態規則配置表驅動，無需改代碼即可調整前綴/日期格式/重置週期業務變更熱生效，減少發布
  效能單行鎖 + 索引查找，極低延遲適合高頻呼叫（如訂單號、單據號）


五、遷移到 12c+ Identity 的建議
若僅需簡單遞增主鍵，12c+ 原生支援：
CREATE TABLE t (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ...
);
複雜流水號規則仍建議用上述配置表方案，Identity 無法表達「前綴+日期重置+分隔符」等業務語義。

總結
「配置表 + 序列化存儲過程」是 Oracle 實現業務語義豐富、高併發安全、動態可配置流水號的經典模式。核心三寶：**SERIALIZABLE 隔離級別、行級鎖更新、樂觀鎖版本號**。配合唯一索引約束、Check Constraint 校驗規則一致性，可直接投入核心交易系統生產環境。