Oracle 12c如何实现按DESIGN_NUM分组的REVISION列自动递增

首先直接回答你的疑问：

• 把REVISION设为identity/auto-increment列实现分组自增走不通。Oracle的identity列绑定的是全表级的全局序列，生成的值全表连续递增，不会按DESIGN_NUM分组重置。比如你插入DESIGN1的修订号0、1之后，首次插入DESIGN2的修订号会直接生成2，完全不符合每个设计项初始修订号从0开始的要求。
• 直接用裸count子查询计算修订号的写法能用，但有并发风险，不是最优解。两个同时提交的同DESIGN_NUM插入请求，可能查到相同的已有记录数，生成重复的REVISION值，违反联合主键约束。

你完全可以将(DESIGN_NUM, REVISION)定义为联合主键——这两个字段的组合天然具备唯一标识单条修订记录的业务意义，主键约束还能从数据库层兜底，避免重复修订号的脏数据写入。

下面是两种可直接落地的实现，按需选择即可：

方案1：低并发场景最简实现（无额外数据库对象） #

如果业务上不存在多个会话同时修改同一个设计项的场景，直接在INSERT语句中用子查询计算下一个修订号即可，写法最简单：

INSERT INTO DESIGN_REVISIONS (DESIGN_NUM, REVISION, COMMENT)
VALUES (
  '传入的设计项编号',
  (SELECT NVL(MAX(REVISION), -1) + 1 FROM DESIGN_REVISIONS WHERE DESIGN_NUM = '传入的设计项编号'),
  '修订备注'
);

如果有并发修改的可能，只需要在子查询末尾加FOR UPDATE，提前锁定对应设计项的所有修订记录，就能避免并发下重复值的问题：

(SELECT NVL(MAX(REVISION), -1) + 1 FROM DESIGN_REVISIONS WHERE DESIGN_NUM = '传入的设计项编号' FOR UPDATE)

方案2：通用最优实现（对业务层透明） #

如果不想每次写INSERT都带计算REVISION的子查询，可以创建一个BEFORE INSERT行级触发器，自动给REVISION赋值，业务层插入时只需要传DESIGN_NUM和COMMENT字段即可，不用关心修订号生成逻辑：

CREATE OR REPLACE TRIGGER TRG_SET_DESIGN_REVISION
BEFORE INSERT ON DESIGN_REVISIONS
FOR EACH ROW
DECLARE
  v_next_revision NUMBER;
BEGIN
  SELECT NVL(MAX(REVISION), -1) + 1
  INTO v_next_revision
  FROM DESIGN_REVISIONS
  WHERE DESIGN_NUM = :NEW.DESIGN_NUM
  FOR UPDATE;

  :NEW.REVISION := v_next_revision;
END;
/

不推荐尝试“给每个DESIGN_NUM单独建序列”的方案：一旦设计项数量变多，序列对象会爆炸式增长，维护成本极高，完全没有实用性。

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