如何避免未保存操作占用Sequence生成的药品编码编号？

针对药品录入时取消操作导致Sequence编号被占用、后续跳号的问题，可采用以下几种解决方案：

方案一：延迟生成编号至提交阶段 #

• 药品录入过程中不调用Sequence的NEXTVAL，仅暂存录入数据（如临时表、内存对象）；
• 当用户确认保存并提交事务时，再调用F_SQ.NEXTVAL生成正式编号并写入业务表。
• 优势：彻底避免取消操作导致的编号浪费，因为未提交时不会消耗Sequence值；
• 注意：需要调整业务逻辑，将编号生成环节后置到提交前。

方案二：用表模拟Sequence（完全避免跳号） #

放弃原生Sequence，改用一张专用编号表来控制编号生成，确保编号仅在事务提交后才被永久占用：

1. 创建编号表：

CREATE TABLE DRUG_CODE_SEQ (
    CURRENT_VAL NUMBER(20) DEFAULT 1 NOT NULL
);
INSERT INTO DRUG_CODE_SEQ VALUES (1);
COMMIT;

2. 获取新编号时，通过排他锁更新并返回当前值：

UPDATE DRUG_CODE_SEQ SET CURRENT_VAL = CURRENT_VAL + 1 RETURNING CURRENT_VAL INTO :new_drug_code;

• 优势：事务回滚时，UPDATE操作也会回滚，编号不会被消耗，完全解决跳号问题；
• 劣势：高并发场景下可能存在锁等待，适合药品录入这类并发量较低的业务。

方案三：关闭Sequence缓存（减少批量跳号） #

修改现有Sequence，取消缓存配置，避免预取批量编号导致的大量跳号：

ALTER SEQUENCE F_SQ NOCACHE;

• 说明：原生Sequence的特性是调用NEXTVAL后，即使事务回滚，该值也会被消耗，所以仍可能出现单个编号跳号，但不会再出现因缓存预取导致的批量跳号（比如原配置CACHE 20可能一次性浪费20个编号）；
• 优势：无需大幅修改业务逻辑，仅调整Sequence配置即可。

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