Oracle 實作自訂序號產生器：配置表 + PL/SQL Function 完整方案

需求場景

業務單據需依規則產生含年份、機構代碼、流水號的複雜序號 (如 2016年012805質字第0200001號)，標準 Sequence 無法滿足，需自訂邏輯。

方案架構

1. 配置表 S_AUTOCODE：儲存序號規則、當前流水號、格式模板。
2. PL/SQL Function SF_SYS_GEN_AUTOCODE：依傳入類別與擁有者，解析模板、遞增流水號、回傳完整序號。
3. 自主交易：Function 內 PRAGMA AUTONOMOUS_TRANSACTION 確保流水號更新獨立提交，不受外層交易回滾影響。

1. 配置表結構

CREATE TABLE S_AUTOCODE (
  PK1              VARCHAR2(32) PRIMARY KEY,
  ATYPE            VARCHAR2(20) NOT NULL,   -- 序號類別
  OWNER            VARCHAR2(10) NOT NULL,   -- 擁有者/機構
  INITCYCLE        CHAR(1)      NOT NULL,   -- 遞增步長 (通常 1)
  CUR_SERNUM       VARCHAR2(50) NOT NULL,   -- 當前流水號
  ZERO_FLG         VARCHAR2(2)  NOT NULL,   -- 流水號總長度 (含前導零)
  SEQUENCESTYLE    VARCHAR2(50),            -- 格式模板
  MEMO             VARCHAR2(60)
);
CREATE INDEX IDX_S_AUTOCODE_TYPE_OWNER ON S_AUTOCODE(ATYPE, OWNER);

2. 模板參數語法

3. 核心 Function 程式碼 (精簡版)

CREATE OR REPLACE FUNCTION SF_SYS_GEN_AUTOCODE(
  I_ATYPE IN VARCHAR2,  -- 序號類別
  I_OWNER IN VARCHAR2   -- 擁有者
) RETURN VARCHAR2 IS
  PRAGMA AUTONOMOUS_TRANSACTION;  -- 關鍵：獨立提交流水號更新
  V_SEQSTYLE   S_AUTOCODE.SEQUENCESTYLE%TYPE;
  V_CURSER     S_AUTOCODE.CUR_SERNUM%TYPE;
  V_INCCYCLE   S_AUTOCODE.INITCYCLE%TYPE;
  V_ZEROFLG    S_AUTOCODE.ZERO_FLG%TYPE;
  V_RESULT     VARCHAR2(200);
  V_NEXTSER    VARCHAR2(50);
  V_YEAR       CHAR(4);
  V_YM         CHAR(6);
  V_YMD        CHAR(8);
BEGIN
  -- 1. 讀取配置
  SELECT SEQUENCESTYLE, CUR_SERNUM, INITCYCLE, ZERO_FLG
    INTO V_SEQSTYLE, V_CURSER, V_INCCYCLE, V_ZEROFLG
    FROM S_AUTOCODE WHERE ATYPE = I_ATYPE AND OWNER = I_OWNER;

  -- 2. 取得日期變數
  SELECT TO_CHAR(SYSDATE,'YYYY'), TO_CHAR(SYSDATE,'YYYYMM'), TO_CHAR(SYSDATE,'YYYYMMDD')
    INTO V_YEAR, V_YM, V_YMD FROM DUAL;

  -- 3. 替換模板固定參數
  V_RESULT := REPLACE(REPLACE(REPLACE(V_SEQSTYLE, '$YEAR$', V_YEAR), '$YEAR_MONTH$', V_YM), '$DATE$', V_YMD);
  V_RESULT := REPLACE(V_RESULT, '$ORGAPP$', I_OWNER);

  -- 4. 遞增流水號並回寫
  V_NEXTSER := TO_CHAR(TO_NUMBER(V_CURSER) + TO_NUMBER(V_INCCYCLE));
  UPDATE S_AUTOCODE SET CUR_SERNUM = V_NEXTSER WHERE ATYPE = I_ATYPE AND OWNER = I_OWNER;

  -- 5. 前導零補齊
  V_NEXTSER := LPAD(V_NEXTSER, TO_NUMBER(V_ZEROFLG), '0');
  V_RESULT := REPLACE(V_RESULT, '$SER$', V_NEXTSER);

  COMMIT;  -- 自主交易提交
  RETURN V_RESULT;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ROLLBACK;
    RETURN 'ERROR';
END;
/

4. 初始化配置與測試

-- 插入配置：質押合同中文編號
INSERT INTO S_AUTOCODE (PK1, ATYPE, OWNER, INITCYCLE, CUR_SERNUM, ZERO_FLG, SEQUENCESTYLE, MEMO)
VALUES ('0A772AEDFBED4FEEA46442003CE1C6A6', 'ZDBCONTCN', '012805', '1', '200000', '7',
        '$YEAR$年$ORGAPP$質字第$SER$號', '質押合同中文編號');

-- 測試呼叫
SELECT SF_SYS_GEN_AUTOCODE('ZDBCONTCN', '012805') FROM DUAL;
-- 回傳：2016年012805質字第0200001號

設計亮點

• 模板化：業務調整格式只需更新 SEQUENCESTYLE，無需改程式。
• 自主交易：流水號遞增不受外層交易 Rollback 影響，杜絕號碼重複/跳號。
• 高併發安全：UPDATE ... SET CUR_SERNUM = ... 隱含行鎖，序列化存取。
• 擴充性：新增佔位符只需在 Function 增加 REPLACE 邏輯。

總結

「配置表 + 自主交易 Function」模式靈活支援複雜業務序號規則，適用於單據編號、合約編號、追蹤碼等多樣化場景。