Oracle SQL中如何获取动态生成SQL的执行结果，实现跨表关联查询？

实现方案 #

你可以通过以下两种Oracle动态SQL方案实现需求，不需要单独为每个对象类型编写固定查询： #

方案1：动态拼接UNION ALL返回统一结果集（性能更优，适合大数据量场景）

该方案会自动读取所有需要处理的对象类型元数据，为每个类型生成对应的查询逻辑后合并返回：

DECLARE
    rc SYS_REFCURSOR;
    v_sql CLOB;
BEGIN
    -- 获取所有对象类型的元数据，动态拼接每个类型的查询段
    WITH type_meta AS (
        SELECT DISTINCT
            sro.sysrepobject_id,
            UPPER(NVL(sro.dbtablename, sro.name)) AS table_name,
            LOWER(UPPER(NVL(sro.dbtablename, sro.name))) || '_id' AS pk_col_name, -- 和你现有SQL的主键规则一致：表名_id
            UPPER(sra.dbcolumnname) AS target_col_name
        FROM configtrace
        LEFT JOIN sysrepobject sro ON sro.sysrepobject_id = configtrace.sysrepobject_id
        INNER JOIN sysrepattribute sra ON sra.sysrepobject_id = sro.sysrepobject_id AND sra.representation = 1
        WHERE configtrace.task = 'Task_1'
    )
    SELECT XMLAGG(XMLELEMENT(e, q'[
SELECT
    sro.sysrepobject_id,
    sro.name AS sysrepobject_name,
    configtrace.object_id,
    t.]' || target_col_name || q'[ AS object_desc
FROM configtrace
LEFT JOIN sysrepobject sro ON sro.sysrepobject_id = configtrace.sysrepobject_id
INNER JOIN sysrepattribute sra ON sra.sysrepobject_id = sro.sysrepobject_id AND sra.representation = 1
LEFT JOIN ]' || table_name || q'[ t ON t.]' || pk_col_name || q'[ = configtrace.object_id
WHERE configtrace.task = 'Task_1'
AND configtrace.sysrepobject_id = ]' || sysrepobject_id, ' UNION ALL ').EXTRACT('//text()') ORDER BY sysrepobject_id).GETCLOBVAL()
    INTO v_sql
    FROM type_meta;

    -- 执行动态SQL返回结果游标
    OPEN rc FOR v_sql;
    DBMS_SQL.RETURN_RESULT(rc);
END;
/

注意：如果你的业务表主键不符合表名_id的规则，可以在sysrepobject表中新增字段存储对应主键名，调整pk_col_name的取值逻辑即可。

方案2：通用查询函数（维护更简单，适合小数据量、类型频繁新增的场景）

先创建一个通用函数封装动态查询逻辑，直接在原有静态SQL中调用即可：

1. 先创建通用查询函数

CREATE OR REPLACE FUNCTION get_object_desc(p_sysrepobject_id NUMBER, p_object_id NUMBER) RETURN VARCHAR2 IS
    v_table_name VARCHAR2(128);
    v_pk_col VARCHAR2(128);
    v_target_col VARCHAR2(128);
    v_result VARCHAR2(4000);
BEGIN
    -- 查询当前对象类型的元数据
    SELECT UPPER(NVL(sro.dbtablename, sro.name)),
           LOWER(UPPER(NVL(sro.dbtablename, sro.name))) || '_id',
           UPPER(sra.dbcolumnname)
    INTO v_table_name, v_pk_col, v_target_col
    FROM sysrepobject sro
    INNER JOIN sysrepattribute sra ON sra.sysrepobject_id = sro.sysrepobject_id AND sra.representation = 1
    WHERE sro.sysrepobject_id = p_sysrepobject_id;

    -- 动态查询对应业务表的描述字段
    EXECUTE IMMEDIATE 'SELECT ' || v_target_col || ' FROM ' || v_table_name || ' WHERE ' || v_pk_col || ' = :1' INTO v_result USING p_object_id;
    RETURN v_result;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN NULL;
    WHEN OTHERS THEN RETURN NULL;
END;
/

2. 原有查询直接调用函数新增字段

DECLARE
    rc SYS_REFCURSOR;
BEGIN
    OPEN rc FOR SELECT
        sro.sysrepobject_id,
        sro.name as sysrepobject_name,
        configtrace.object_id,
        upper(nvl(sro.dbtablename,sro.name)) as table_name,
        upper(sra.dbcolumnname) as column_name,
        get_object_desc(sro.sysrepobject_id, configtrace.object_id) AS object_desc -- 新增对象描述列
    FROM
        configtrace
        LEFT OUTER JOIN sysrepobject sro ON sro.sysrepobject_id = configtrace.sysrepobject_id
        INNER JOIN sysrepattribute sra ON sra.sysrepobject_id = sro.sysrepobject_id and sra.representation = 1
    WHERE
        configtrace.task = 'Task_1';

    dbms_sql.return_result(rc);
END;
/

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