Oracle PL/SQL如何获取存在缺失值的序列下一个插入编号？

针对你遇到的场景，这里提供两种Oracle SQL方案来获取下一次插入的编号：

方法一：利用层级查询找最小缺失值 #

这个方法会生成从1到当前最大编号+1的连续数字，再排除表中已存在的编号，最终取最小的缺失值：

SELECT MIN(n) AS next_insert_num
FROM (
    -- 生成1到当前最大num+1的连续序列
    SELECT LEVEL AS n
    FROM dual
    CONNECT BY LEVEL <= (SELECT COALESCE(MAX(num), 0) + 1 FROM your_table)
    -- 减去表中已有的num值
    MINUS
    SELECT num FROM your_table
)

• 当表中存在1、2、3、5时，查询返回4；
• 插入4后，表中num为1-5，查询返回6；
• 若表为空，返回1。

方法二：用存在性查询找间隙 #

通过检查每个编号的下一个值是否存在，定位最小的缺失间隙：

SELECT COALESCE(
    -- 找最小的num+1不存在的情况
    (SELECT MIN(num + 1)
     FROM your_table t1
     WHERE NOT EXISTS (SELECT 1 FROM your_table t2 WHERE t2.num = t1.num + 1)
       AND t1.num + 1 <= (SELECT MAX(num) FROM your_table)),
    -- 如果所有编号连续，返回最大num+1
    (SELECT COALESCE(MAX(num), 0) + 1 FROM your_table)
) AS next_insert_num

逻辑和方法一一致，只是实现方式不同。

注意事项 #

如果存在多会话并发插入的场景，建议结合原子操作避免重复插入，比如用带判断的插入语句：

INSERT INTO your_table (num)
SELECT next_num
FROM (
    SELECT MIN(n) AS next_num
    FROM (
        SELECT LEVEL AS n
        FROM dual
        CONNECT BY LEVEL <= (SELECT COALESCE(MAX(num), 0) + 1 FROM your_table)
        MINUS
        SELECT num FROM your_table
    )
)
WHERE NOT EXISTS (SELECT 1 FROM your_table WHERE num = next_num);

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