Oracle 19c 如何为按部门分组的经理行自动生成数据库端序列ID

Oracle 19c 没有开箱即用的、单靠表定义就能实现「按部门分组、仅给MANAGER_ID为空的经理行生成从1起始的连续序列、非经理行自动留空」的内置能力，但可以通过数据库端纯原生的机制组合完成需求，全程不需要依赖应用层的计数逻辑。

• 普通Identity列、全局序列：生成的是全表范围的连续值，既无法按DEPARTMENT_ID分组独立计数，也做不到自动跳过非经理行不赋值。
• 应用层常用的Select NVL(MAX(SEQUENCE_ID), 0) + 1 FROM TEST_ORGANIZATION WHERE MANAGER_ID is NULL AND DEPARTMENT_ID = ? 编程式逻辑：没有并发锁保护，高并发插入同部门经理时会出现多个请求拿到相同MAX值的情况，最终导致SEQUENCE_ID重复。

方案1：行级BEFORE INSERT触发器（兼容所有19c版本，无额外依赖） #

所有计数逻辑完全放在数据库内，插入数据时自动按规则赋值：

1. 普通员工行（MANAGER_ID IS NOT NULL）插入时，SEQUENCE_ID自动保持为空
2. 经理行（MANAGER_ID IS NULL）插入时，在同部门行锁保护下，查询当前部门最大SEQUENCE_ID并+1赋值，彻底避免并发冲突

实现代码：

-- 新增SEQUENCE_ID列
ALTER TABLE TEST_ORGANIZATION ADD SEQUENCE_ID NUMBER;

-- 创建赋值触发器
CREATE OR REPLACE TRIGGER trg_org_manager_seq
BEFORE INSERT ON TEST_ORGANIZATION
FOR EACH ROW
-- 仅对待插入的经理行触发逻辑，普通员工行直接跳过
WHEN (NEW.MANAGER_ID IS NULL)
DECLARE
  v_new_seq NUMBER;
BEGIN
  -- 加行锁避免并发插入同部门经理时的序列值冲突
  SELECT NVL(MAX(SEQUENCE_ID), 0) + 1
  INTO v_new_seq
  FROM TEST_ORGANIZATION
  WHERE 
    DEPARTMENT_ID = :NEW.DEPARTMENT_ID
    AND MANAGER_ID IS NULL
  FOR UPDATE; -- 锁定该部门下所有经理行，序列化同部门的经理插入操作
  
  :NEW.SEQUENCE_ID := v_new_seq;
END;
/

历史存量数据初始化方法：如果表中已经存在经理数据，可直接用分析函数一次性刷入初始值，不需要手动逐行赋值：

UPDATE TEST_ORGANIZATION t
SET SEQUENCE_ID = (
  SELECT rn 
  FROM (
    SELECT 
      UUID,
      ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY EMPLOYEE_ID) as rn
    FROM TEST_ORGANIZATION
    WHERE MANAGER_ID IS NULL
  ) r
  WHERE r.UUID = t.UUID
)
WHERE MANAGER_ID IS NULL;

方案2：轻量序列方案（适合不要求严格连续的场景） #

如果业务允许序列值出现跳号（比如删除经理后不需要重排后续值），可以配合普通序列做默认值判断：

1. 先创建独立的全局序列：CREATE SEQUENCE seq_org_manager START WITH 1 INCREMENT BY 1;
2. 给SEQUENCE_ID列设置带判断的默认值：只有插入行满足MANAGER_ID IS NULL时取序列值，否则为NULL

注意：该方案的序列值是全局连续的，无法实现「同部门从1开始计数」的要求，仅适合不依赖部门内序号的场景，可根据业务灵活选择。

• 触发器中的FOR UPDATE锁仅会锁定目标部门的经理记录，不会阻塞其他部门的数据插入，并发性能可满足绝大多数组织表的业务场景。
• 如果需要对SEQUENCE_ID加唯一约束，一定要建联合唯一索引(DEPARTMENT_ID, SEQUENCE_ID)，避免跨部门的同序号值冲突。

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