Oracle中如何用WITH子句的双SELECT实现多表合并/插入？

针对你需要从单一数据源通过CTE复用数据，同时完成两张表的合并/插入需求，以下三种Oracle环境下的可行方案：

方案1：PL/SQL集合缓存CTE结果 #

通过将CTE的结果一次性加载到PL/SQL集合中，再分别基于集合对两张表执行MERGE/INSERT操作，避免重复扫描源数据源。

步骤1：定义数据类型 #

CREATE OR REPLACE TYPE t_source_rec IS OBJECT (
    id NUMBER,
    month DATE,
    detail_col VARCHAR2(100),
    agg_col NUMBER
);
/
CREATE OR REPLACE TYPE t_source_tab IS TABLE OF t_source_rec;
/

步骤2：编写存储过程 #

CREATE OR REPLACE PROCEDURE daily_refresh_proc IS
    l_source_data t_source_tab;
BEGIN
    -- 一次性获取源数据并加载到集合
    WITH t1 AS (
        SELECT 
            id,
            TRUNC(create_date, 'MM') AS month,
            detail_info AS detail_col,
            amount AS agg_col
        FROM your_single_datasource
        WHERE create_date >= TRUNC(SYSDATE) - 1 -- 每日刷新过滤条件
    )
    SELECT t_source_rec(id, month, detail_col, agg_col)
    BULK COLLECT INTO l_source_data
    FROM t1;

    -- 处理明细目标表（按ID映射）
    MERGE INTO target_detail_table dt
    USING (
        SELECT id, detail_col 
        FROM TABLE(l_source_data)
    ) src
    ON (dt.id = src.id)
    WHEN MATCHED THEN
        UPDATE SET dt.detail_col = src.detail_col
    WHEN NOT MATCHED THEN
        INSERT (id, detail_col)
        VALUES (src.id, src.detail_col);

    -- 处理月聚合目标表（按月分组）
    MERGE INTO target_monthly_table mt
    USING (
        SELECT month, SUM(agg_col) AS total_amount
        FROM TABLE(l_source_data)
        GROUP BY month
    ) src
    ON (mt.month = src.month)
    WHEN MATCHED THEN
        UPDATE SET mt.total_amount = src.total_amount
    WHEN NOT MATCHED THEN
        INSERT (month, total_amount)
        VALUES (src.month, src.total_amount);

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

方案2：管道函数封装数据源 #

通过管道函数封装CTE查询逻辑，两张表的MERGE操作直接调用该函数，实现数据源的复用，无需重复扫描原表。

步骤1：创建管道函数 #

CREATE OR REPLACE FUNCTION get_daily_source_data RETURN t_source_tab PIPELINED IS
BEGIN
    WITH t1 AS (
        SELECT 
            id,
            TRUNC(create_date, 'MM') AS month,
            detail_info AS detail_col,
            amount AS agg_col
        FROM your_single_datasource
        WHERE create_date >= TRUNC(SYSDATE) - 1
    )
    FOR rec IN (SELECT * FROM t1) LOOP
        PIPE ROW(t_source_rec(rec.id, rec.month, rec.detail_col, rec.agg_col));
    END LOOP;
    RETURN;
END;
/

步骤2：编写存储过程 #

CREATE OR REPLACE PROCEDURE daily_refresh_proc IS
BEGIN
    -- 处理明细目标表
    MERGE INTO target_detail_table dt
    USING (
        SELECT id, detail_col 
        FROM TABLE(get_daily_source_data())
    ) src
    ON (dt.id = src.id)
    WHEN MATCHED THEN
        UPDATE SET dt.detail_col = src.detail_col
    WHEN NOT MATCHED THEN
        INSERT (id, detail_col)
        VALUES (src.id, src.detail_col);

    -- 处理月聚合目标表
    MERGE INTO target_monthly_table mt
    USING (
        SELECT month, SUM(agg_col) AS total_amount
        FROM TABLE(get_daily_source_data())
        GROUP BY month
    ) src
    ON (mt.month = src.month)
    WHEN MATCHED THEN
        UPDATE SET mt.total_amount = src.total_amount
    WHEN NOT MATCHED THEN
        INSERT (month, total_amount)
        VALUES (src.month, src.total_amount);

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

方案3：全局临时表（GTT）中转数据 #

使用Oracle全局临时表存储CTE结果，临时表数据仅当前会话可见，事务提交后自动清空，既避免了永久表的存储开销，又能简单实现数据复用，适合大数据量场景。

步骤1：创建全局临时表 #

CREATE GLOBAL TEMPORARY TABLE temp_daily_source (
    id NUMBER,
    month DATE,
    detail_col VARCHAR2(100),
    agg_col NUMBER
) ON COMMIT DELETE ROWS; -- 提交后自动清空数据

步骤2：编写存储过程 #

CREATE OR REPLACE PROCEDURE daily_refresh_proc IS
BEGIN
    -- 将CTE结果插入临时表
    INSERT INTO temp_daily_source
    WITH t1 AS (
        SELECT 
            id,
            TRUNC(create_date, 'MM') AS month,
            detail_info AS detail_col,
            amount AS agg_col
        FROM your_single_datasource
        WHERE create_date >= TRUNC(SYSDATE) - 1
    )
    SELECT id, month, detail_col, agg_col FROM t1;

    -- 处理明细目标表
    MERGE INTO target_detail_table dt
    USING (
        SELECT id, detail_col 
        FROM temp_daily_source
    ) src
    ON (dt.id = src.id)
    WHEN MATCHED THEN
        UPDATE SET dt.detail_col = src.detail_col
    WHEN NOT MATCHED THEN
        INSERT (id, detail_col)
        VALUES (src.id, src.detail_col);

    -- 处理月聚合目标表
    MERGE INTO target_monthly_table mt
    USING (
        SELECT month, SUM(agg_col) AS total_amount
        FROM temp_daily_source
        GROUP BY month
    ) src
    ON (mt.month = src.month)
    WHEN MATCHED THEN
        UPDATE SET mt.total_amount = src.total_amount
    WHEN NOT MATCHED THEN
        INSERT (month, total_amount)
        VALUES (src.month, src.total_amount);

    COMMIT; -- 提交后临时表数据自动清空
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

方案选择建议 #

• 数据量中等（百万级以内）：优先选择PL/SQL集合，内存操作性能最优。
• 数据量较大且不想占用过多内存：选择管道函数，按需取数减少内存压力。
• 大数据量（千万级以上）：优先选择全局临时表，Oracle对临时表的IO优化更成熟，稳定性更高。

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