如何在Oracle存储过程内部重置sequence序列

Oracle本身没有提供直接重置序列的内置方法，以下是三种可在存储过程内部实现的方案，按优先级从高到低排列：

方案1：用窗口函数替代序列（最推荐，无权限/并发问题） #

如果序列仅用于给本次插入临时表的记录生成连续编号，完全可以不用序列，插入时直接用ROW_NUMBER()窗口函数生成从1开始的序号，性能更高也不需要额外权限：

BEGIN
  -- 清空原有临时表数据
  DELETE FROM your_temp_table;
  
  -- 插入数据时直接生成连续序号
  INSERT INTO your_temp_table (id, 其他字段1, 其他字段2)
  SELECT 
    ROW_NUMBER() OVER(ORDER BY 你的排序规则字段),
    其他字段1,
    其他字段2
  FROM 业务数据源表
  WHERE 筛选条件;
END;
/

方案2：修改序列增量步长回跳 #

如果必须保留序列的使用逻辑，可以通过动态修改序列增量的方式实现重置：

• 实现逻辑：先获取序列当前值，将序列增量设置为负的（当前值-1），调用一次nextval跳回1，再把增量改回1即可
• 代码示例：

DECLARE
  v_curr_val NUMBER;
BEGIN
  -- 获取序列当前值
  SELECT your_seq_name.NEXTVAL INTO v_curr_val FROM dual;
  
  -- 当前值大于1时才需要重置
  IF v_curr_val > 1 THEN
    -- 设置增量为负的（当前值-1）
    EXECUTE IMMEDIATE 'ALTER SEQUENCE your_seq_name INCREMENT BY ' || -(v_curr_val - 1);
    -- 调用一次nextval完成回跳
    SELECT your_seq_name.NEXTVAL INTO v_curr_val FROM dual;
    -- 把增量改回默认值1
    EXECUTE IMMEDIATE 'ALTER SEQUENCE your_seq_name INCREMENT BY 1';
  END IF;

  -- 后面接清空临时表、插入数据的原有逻辑
END;
/

• 注意事项：
  • 需要提前给存储过程所属用户授予序列的ALTER权限：GRANT ALTER ON your_seq_name TO 存储过程所属用户;
  • 如果序列开启了缓存，回跳时可能出现跳值，建议将序列设置为NOCACHE避免该问题
  • 避免多个会话同时调用该存储过程，否则会出现序列取值冲突

方案3：动态重建序列 #

如果序列仅为该存储过程独占使用，可以直接删除重建序列实现重置：

BEGIN
  -- 尝试删除序列，捕获序列不存在的异常避免报错
  BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE your_seq_name';
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -2289 THEN -- -2289是Oracle序列不存在的错误码
        RAISE;
      END IF;
  END;

  -- 重建序列，起始值为1
  EXECUTE IMMEDIATE 'CREATE SEQUENCE your_seq_name START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';

  -- 后面接清空临时表、插入数据的原有逻辑
END;
/

• 注意事项：需要给存储过程所属用户授予CREATE SEQUENCE、DROP SEQUENCE权限，不适合多会话共用序列的场景

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