如何处理依赖同一张临时表的两个并行函数/存储过程调用

针对你遇到的临时表重复初始化、并行调用数据不一致的问题，以下是几个更优的解决方案，替代当前的UNION合并方案：

方案1：全局临时表（GTT）+ 原子化初始化逻辑（推荐） #

利用Oracle的全局临时表和锁机制，确保同一会话内临时表仅初始化一次，并行调用时不会出现数据未就绪的情况。

步骤： #

1. 定义全局临时表（按需调整字段）：

CREATE GLOBAL TEMPORARY TABLE temp_shared_data (
    id NUMBER,
    core_data VARCHAR2(200),
    -- 其他业务字段
) ON COMMIT PRESERVE ROWS; -- 保留数据直到会话结束，按需选择ON COMMIT DELETE ROWS

2. 编写带锁控制的初始化存储过程：

CREATE OR REPLACE PROCEDURE init_temp_data IS
    v_lock_handle VARCHAR2(128);
    v_lock_result INTEGER;
BEGIN
    -- 分配唯一锁标识，确保只有一个会话能执行初始化
    DBMS_LOCK.ALLOCATE_UNIQUE('TEMP_SHARED_INIT_LOCK', v_lock_handle);
    v_lock_result := DBMS_LOCK.REQUEST(
        lockhandle => v_lock_handle,
        lockmode => DBMS_LOCK.X_MODE,
        timeout => 0, -- 立即返回，不等待锁
        release_on_commit => TRUE
    );

    -- 仅获取到锁的会话执行初始化
    IF v_lock_result = 0 THEN
        DELETE FROM temp_shared_data; -- 清空旧数据
        -- 填充临时表的核心逻辑
        INSERT INTO temp_shared_data
        SELECT id, core_field FROM source_table WHERE filter_condition;
        COMMIT;
    END IF;

    DBMS_LOCK.RELEASE(v_lock_handle);
END;

3. 在两个流水线函数开头调用该存储过程：

CREATE OR REPLACE FUNCTION func_sheet1 RETURN sheet1_type PIPELINED IS
BEGIN
    init_temp_data();
    -- 查询临时表生成sheet1数据
    FOR rec IN (SELECT col1, col2 FROM temp_shared_data JOIN table1 USING(id)) LOOP
        PIPE ROW(sheet1_type(rec.col1, rec.col2));
    END LOOP;
    RETURN;
END;

CREATE OR REPLACE FUNCTION func_sheet2 RETURN sheet2_type PIPELINED IS
BEGIN
    init_temp_data();
    -- 查询临时表生成sheet2数据
    FOR rec IN (SELECT col_a, col_b FROM temp_shared_data JOIN table2 USING(id)) LOOP
        PIPE ROW(sheet2_type(rec.col_a, rec.col_b));
    END LOOP;
    RETURN;
END;

优点：无需修改BO报表结构，仅改造数据库端逻辑；锁机制保证初始化原子性，彻底避免重复计算；兼容并行调用场景。

方案2：BO报表预执行脚本 #

利用Business Objects的预报表脚本功能，在所有工作表查询执行前统一初始化临时表。

步骤： #

1. 复用方案1中的init_temp_data存储过程。
2. 在BO报表设计器中添加预执行脚本（以VBA为例）：

Dim conn As ADODB.Connection
Set conn = ThisDocument.DataConnections("Oracle_Conn").Connection
conn.Execute "BEGIN init_temp_data(); END;"
Set conn = Nothing

3. 修改两个流水线函数，去掉内部初始化逻辑，直接查询临时表即可。
   优点：完全分离初始化与查询逻辑，性能最优；代码结构清晰。
   缺点：需要调整BO报表配置，对维护人员有一定技术要求。

方案3：双游标存储过程替代UNION #

创建一个返回两个REF CURSOR的存储过程，一次性初始化临时表后分别返回两个数据集，在BO中绑定到对应工作表。

步骤： #

1. 编写双游标存储过程：

CREATE OR REPLACE PROCEDURE get_sheet_datasets(
    p_sheet1_cursor OUT SYS_REFCURSOR,
    p_sheet2_cursor OUT SYS_REFCURSOR
) IS
BEGIN
    init_temp_data(); -- 仅初始化一次
    -- 绑定sheet1数据游标
    OPEN p_sheet1_cursor FOR
        SELECT col1, col2 FROM temp_shared_data JOIN table1 USING(id);
    -- 绑定sheet2数据游标
    OPEN p_sheet2_cursor FOR
        SELECT col_a, col_b FROM temp_shared_data JOIN table2 USING(id);
END;

2. 在BO报表中创建基于该存储过程的数据源，将两个工作表分别绑定到对应的游标。
   优点：避免UNION带来的Null字段冗余和额外计算；数据逻辑更清晰。
   缺点：需要重新配置BO报表的数据源绑定。

当前UNION方案的不足 #

你当前的UNION方案存在明显缺陷：

• 数据冗余：每行携带大量Null字段，增加数据传输量。
• 逻辑复杂：需要额外标识字段区分数据集，报表端还要做过滤处理。
• 性能损耗：UNION操作本身会带来额外的排序、去重开销，数据量大时影响明显。

优先推荐方案1，它对现有报表结构改动最小，同时完美解决并行调用下的临时表初始化问题。

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