PL/SQL包级变量自增偶尔失效，调用时返回相同数值求助

你的问题核心在于PL/SQL包级变量的会话私有特性，以及全局临时表的数据隔离机制：

• 包级变量v_counter是每个数据库会话独立维护的，每个Java客户端的数据库连接对应一个单独会话，每个会话初始化时v_counter都会被设为0。当多个Java连接同时调用saveItem时，不同会话会各自维护自己的计数器，完全互不干扰，这就会出现不同会话生成相同v_counter值的情况。
• 全局临时表（Global Temporary Table）的数据是会话隔离的，每个会话只能看到自己插入到temptable中的数据。原逻辑中第一次调用时从temptable取MAX_SEQ+1，但这个最大值只是当前会话内的最大值，不是全局所有会话的最大值，这也会导致不同会话生成重复的计数器值。
• 另外，如果Java客户端复用连接池中的连接时，若连接被重置或会话被销毁，包级变量会被重新初始化回0，再次调用时会重新查询temptable的最大值，可能和之前会话生成的计数器值重复。

针对你的需求，推荐以下两种可靠的处理方式：

方案1：使用数据库序列（最推荐） #

数据库序列是全局唯一的递增生成器，能保证跨会话的唯一性和递增性，完全避免包级变量和临时表带来的隔离问题。

步骤1：创建序列 #

CREATE SEQUENCE item_counter_seq 
START WITH 1 
INCREMENT BY 1 
NOCACHE; -- 关闭缓存避免意外的数值跳跃

步骤2：修改包体逻辑 #

CREATE OR REPLACE PACKAGE BODY TestIncrement AS
PROCEDURE saveItem(evalId IN NUMBER, id IN NUMBER, name IN varchar2) IS
    v_counter NUMBER(19);
BEGIN
    -- 如果需要基于temptable的MAX_SEQ来保证计数器不小于现有最大值
    SELECT GREATEST(NVL(MAX(MAX_SEQ), 0) + 1, item_counter_seq.NEXTVAL)
    INTO v_counter
    FROM temptable
    WHERE eval_id = evalId;

    -- 若不需要依赖temptable的历史值，直接用序列即可
    -- v_counter := item_counter_seq.NEXTVAL;

    INSERT INTO item(id, counter, counter_name) VALUES(id, v_counter, name);
END;
END TestIncrement;

方案2：每次插入前查询最新最大值（适用于必须依赖temptable的场景） #

如果必须基于temptable的MAX_SEQ生成计数器，不要依赖包级变量缓存，每次插入前都查询最新的最大值，同时用FOR UPDATE锁保证并发下的原子性：

CREATE OR REPLACE PACKAGE BODY TestIncrement AS
PROCEDURE saveItem(evalId IN NUMBER, id IN NUMBER, name IN varchar2) IS
    v_counter NUMBER(19);
BEGIN
    -- 锁定对应evalId的行，避免并发查询时的重复值
    SELECT NVL(MAX_SEQ, 0) + 1
    INTO v_counter
    FROM temptable
    WHERE eval_id = evalId
    FOR UPDATE;

    -- 更新temptable的MAX_SEQ
    UPDATE temptable
    SET MAX_SEQ = v_counter
    WHERE eval_id = evalId;

    -- 如果temptable中没有对应evalId的行，先插入
    IF SQL%NOTFOUND THEN
        INSERT INTO temptable(eval_id, MAX_SEQ) VALUES(evalId, 1);
        v_counter := 1;
    END IF;

    INSERT INTO item(id, counter, counter_name) VALUES(id, v_counter, name);
END;
END TestIncrement;

• 全局临时表仅在当前会话内可见数据，如果你需要跨会话共享temptable的MAX_SEQ，那么全局临时表并不适合这个场景，应该改用普通表来存储序列值。
• 包级变量仅适合单会话内的状态维护，跨会话的全局状态必须用数据库级的对象（如序列、普通表）来实现。

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