如何在Oracle中为表字段追加按年度重置的递增编号？

针对你需要生成YYYY-N格式（每年从1开始递增）唯一ID的需求，以下是两种兼顾并发安全与性能的最优实现方案：

方案一：单独序列维护表（推荐高并发场景） #

通过专门的表维护每个年份的当前序列值，避免频繁查询主表的最大编号，性能更优。

1. 创建序列维护表 #

CREATE TABLE PLANE_INFO.YEAR_SEQUENCES (
    YEAR VARCHAR2(4) PRIMARY KEY,  -- 存储年份
    CURRENT_VALUE NUMBER DEFAULT 0 NOT NULL  -- 该年份当前的序列值
);

2. 创建获取下一个ID的存储过程 #

CREATE OR REPLACE PROCEDURE PLANE_INFO.GET_NEXT_PLANE_ID(
    o_plane_id OUT VARCHAR2
) AS
    v_current_year VARCHAR2(4) := TO_CHAR(SYSDATE, 'YYYY');
    v_next_seq NUMBER;
    v_lock_handle VARCHAR2(128);
BEGIN
    -- 针对当前年份加排他锁，避免并发插入导致重复ID
    DBMS_LOCK.ALLOCATE_UNIQUE('YEAR_SEQ_LOCK_' || v_current_year, v_lock_handle);
    DBMS_LOCK.REQUEST(v_lock_handle, DBMS_LOCK.X_MODE, 30, FALSE);

    BEGIN
        -- 尝试更新当前年份的序列值，若年份不存在则插入新记录
        UPDATE PLANE_INFO.YEAR_SEQUENCES
        SET CURRENT_VALUE = CURRENT_VALUE + 1
        WHERE YEAR = v_current_year
        RETURNING CURRENT_VALUE INTO v_next_seq;

        IF SQL%ROWCOUNT = 0 THEN
            INSERT INTO PLANE_INFO.YEAR_SEQUENCES (YEAR, CURRENT_VALUE)
            VALUES (v_current_year, 1)
            RETURNING CURRENT_VALUE INTO v_next_seq;
        END IF;

        -- 生成最终ID，如需固定长度编号可使用LPAD(v_next_seq,5,'0')补零
        o_plane_id := v_current_year || '-' || v_next_seq;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_LOCK.RELEASE(v_lock_handle);
            RAISE;
    END;

    DBMS_LOCK.RELEASE(v_lock_handle);
END;
/

3. 使用方式 #

调用存储过程获取ID后插入主表：

DECLARE
    v_plane_id VARCHAR2(20);
BEGIN
    PLANE_INFO.GET_NEXT_PLANE_ID(v_plane_id);
    INSERT INTO PLANE_INFO.ID_NUMBERS (PLANE_ID) VALUES (v_plane_id);
END;
/

方案二：触发器自动生成（轻量场景适用） #

通过触发器在插入主表时自动计算当年的最大编号并生成ID，无需额外维护序列表。

1. 调整主表结构（可选，添加创建日期字段辅助年份判断） #

ALTER TABLE PLANE_INFO.ID_NUMBERS ADD CREATE_DATE DATE DEFAULT SYSDATE;

2. 创建生成ID的触发器 #

CREATE OR REPLACE TRIGGER PLANE_INFO.TRG_ID_NUMBERS_PLANE_ID
BEFORE INSERT ON PLANE_INFO.ID_NUMBERS
FOR EACH ROW
DECLARE
    v_current_year VARCHAR2(4) := TO_CHAR(SYSDATE, 'YYYY');
    v_next_seq NUMBER;
    v_lock_handle VARCHAR2(128);
BEGIN
    -- 加年度锁避免并发冲突
    DBMS_LOCK.ALLOCATE_UNIQUE('PLANE_ID_YEAR_LOCK_' || v_current_year, v_lock_handle);
    DBMS_LOCK.REQUEST(v_lock_handle, DBMS_LOCK.X_MODE, 30, FALSE);

    BEGIN
        -- 查询当年已有的最大编号，无记录则从1开始
        SELECT NVL(MAX(TO_NUMBER(SUBSTR(PLANE_ID, INSTR(PLANE_ID, '-') + 1))), 0) + 1
        INTO v_next_seq
        FROM PLANE_INFO.ID_NUMBERS
        WHERE SUBSTR(PLANE_ID, 1, 4) = v_current_year;

        -- 生成最终ID，如需补零可替换为LPAD(v_next_seq,5,'0')
        :NEW.PLANE_ID := v_current_year || '-' || v_next_seq;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_LOCK.RELEASE(v_lock_handle);
            RAISE;
    END;

    DBMS_LOCK.RELEASE(v_lock_handle);
END;
/

3. 使用方式 #

直接插入主表即可，触发器自动生成PLANE_ID：

INSERT INTO PLANE_INFO.ID_NUMBERS DEFAULT VALUES;

关键注意事项 #

• 并发安全：两种方案均通过DBMS_LOCK添加年度排他锁，避免多会话同时插入导致重复ID；
• 编号格式：如需固定长度编号（如原代码的5位），将拼接逻辑改为v_current_year || '-' || LPAD(v_next_seq,5,'0')即可；
• 数据迁移：若主表已有历史数据，需初始化序列维护表（方案一），将各年份的最大编号插入YEAR_SEQUENCES；
• Oracle版本：若使用12c之前的版本，主表的自增主键（ENTRY_ID）需通过序列+触发器实现，无法直接使用IDENTITY列。

内容的提问来源于stack exchange，提问作者duk3csmaj0r