Oracle如何实现包内多存储过程同次调用共享相同序列值

两个失败方案的失效原因 #

• 包级常量方案：包级常量在包首次被会话加载时完成唯一一次初始化，只要会话未断开、包未被重编译/卸载，常量值就不会更新，因此多次调用包也不会重新获取序列值。
• 独立函数每次取NEXTVAL方案：Oracle序列的NEXTVAL每调用一次就会自动推进一次，因此三个存储过程分别调用会拿到连续递增的不同值。

推荐实现方案（无事务依赖，适配任意调用顺序） #

该方案通过包私有全局变量存储当前调用周期的序列值，结合调用深度判断是否为新的顶层调用，自动决定是否更新序列值，不需要修改外部调用逻辑，也不依赖存储过程的执行顺序。

包规范代码 #

CREATE OR REPLACE PACKAGE your_pkg IS
  -- 对外暴露的三个存储过程
  PROCEDURE pr_1;
  PROCEDURE pr_2;
  PROCEDURE pr_3;
END your_pkg;
/

包体代码 #

CREATE OR REPLACE PACKAGE BODY your_pkg IS
  -- 私有变量：存储当前调用周期的序列值
  g_curr_seq NUMBER;
  -- 私有变量：记录上一次调用的栈深度，用于判断是否为新的顶层调用
  g_last_call_depth NUMBER := 0;

  -- 私有工具函数：统一获取当前周期的序列值
  FUNCTION get_curr_seq RETURN NUMBER IS
  BEGIN
    -- 调用深度小于等于上次记录值，说明是新的顶层调用，重新取序列
    IF DBMS_UTILITY.CALL_DEPTH <= g_last_call_depth THEN
      g_curr_seq := your_sequence.NEXTVAL;
    END IF;
    -- 更新调用深度记录
    g_last_call_depth := DBMS_UTILITY.CALL_DEPTH;
    RETURN g_curr_seq;
  END get_curr_seq;

  PROCEDURE pr_1 IS
  BEGIN
    -- 直接调用工具函数拿序列值即可
    DBMS_OUTPUT.PUT_LINE('next_seq_val in pr_1: ' || get_curr_seq);
    -- 此处写pr_1的业务逻辑
  END pr_1;

  PROCEDURE pr_2 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('next_seq_val in pr_2: ' || get_curr_seq);
    -- 此处写pr_2的业务逻辑
  END pr_2;

  PROCEDURE pr_3 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('next_seq_val in pr_3: ' || get_curr_seq);
    -- 此处写pr_3的业务逻辑
  END pr_3;
END your_pkg;
/

效果验证 #

同一次顶层调用内任意顺序调用三个存储过程，拿到的都是同一个序列值：

-- 第一次调用
BEGIN
  your_pkg.pr_2;
  your_pkg.pr_3;
  your_pkg.pr_1;
END;
/
-- 输出
-- next_seq_val in pr_2: 22
-- next_seq_val in pr_3: 22
-- next_seq_val in pr_1: 22

-- 第二次调用
BEGIN
  your_pkg.pr_1;
  your_pkg.pr_2;
END;
/
-- 输出
-- next_seq_val in pr_1: 23
-- next_seq_val in pr_2: 23

可选方案（基于事务判断，适合同事务共用序列的场景） #

如果你的“同一次包调用周期”对应同一个数据库事务，可以用事务ID判断替换调用深度判断，稳定性更高：

CREATE OR REPLACE PACKAGE BODY your_pkg IS
  g_curr_seq NUMBER;
  g_last_txn_id VARCHAR2(100);

  FUNCTION get_curr_seq RETURN NUMBER IS
    l_curr_txn_id VARCHAR2(100) := DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(TRUE);
  BEGIN
    IF l_curr_txn_id != g_last_txn_id OR g_last_txn_id IS NULL THEN
      g_curr_seq := your_sequence.NEXTVAL;
      g_last_txn_id := l_curr_txn_id;
    END IF;
    RETURN g_curr_seq;
  END get_curr_seq;

  -- 三个存储过程实现和上面一致，直接调用get_curr_seq即可
  PROCEDURE pr_1 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('next_seq_val in pr_1: ' || get_curr_seq);
  END pr_1;

  PROCEDURE pr_2 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('next_seq_val in pr_2: ' || get_curr_seq);
  END pr_2;

  PROCEDURE pr_3 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('next_seq_val in pr_3: ' || get_curr_seq);
  END pr_3;
END your_pkg;
/

注意事项 #

以上两个方案的包变量都是会话级的，不同会话之间的序列值互不干扰，符合绝大多数业务场景的使用要求。

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