如何为Oracle自动生成关联所有子表的SELECT查询脚本？

针对Oracle数据库中父表关联多张子表的批量查询需求，可通过读取Oracle内置数据字典视图，编写PL/SQL脚本自动生成关联查询语句，无需手动编写数十个子表的关联逻辑。

核心实现思路 #

利用Oracle的ALL_CONSTRAINTS（约束信息）、ALL_CONS_COLUMNS（约束列映射）、ALL_TAB_COLUMNS（表列信息）三个核心数据字典视图，自动识别：

• 父表的主键列
• 所有引用该主键的子表及对应的外键列
• 所有子表的全部列

PL/SQL 脚本实现 #

SET SERVEROUTPUT ON;
DECLARE
    v_parent_table VARCHAR2(100) := 'CLASS'; -- 替换为你的父表名（Oracle默认表名大写，小写需加双引号）
    v_parent_owner VARCHAR2(100) := USER; -- 父表所属用户，默认当前用户
    v_pk_constraint VARCHAR2(100);
    v_pk_column VARCHAR2(100);
    v_sql CLOB := '';
BEGIN
    -- 1. 获取父表的主键约束名
    SELECT constraint_name INTO v_pk_constraint
    FROM all_constraints
    WHERE owner = v_parent_owner
      AND table_name = v_parent_table
      AND constraint_type = 'P';

    -- 2. 获取父表的主键列名
    SELECT column_name INTO v_pk_column
    FROM all_cons_columns
    WHERE owner = v_parent_owner
      AND constraint_name = v_pk_constraint;

    -- 3. 初始化SELECT子句：先选父表所有列
    v_sql := 'SELECT ' || v_parent_owner || '.' || v_parent_table || '.*';

    -- 4. 遍历所有关联子表，拼接子表列
    FOR sub_tabs IN (
        SELECT 
            ac.owner AS sub_owner,
            ac.table_name AS sub_table,
            acc.column_name AS sub_fk_column
        FROM all_constraints ac
        JOIN all_cons_columns acc 
            ON ac.owner = acc.owner AND ac.constraint_name = acc.constraint_name
        WHERE ac.r_owner = v_parent_owner
          AND ac.r_constraint_name = v_pk_constraint
          AND ac.constraint_type = 'R'
    ) LOOP
        -- 拼接子表所有列（带表限定符避免列名冲突）
        v_sql := v_sql || ', ' || sub_tabs.sub_owner || '.' || sub_tabs.sub_table || '.*';
    END LOOP;

    -- 5. 拼接FROM子句和JOIN条件
    v_sql := v_sql || ' FROM ' || v_parent_owner || '.' || v_parent_table;

    FOR sub_tabs IN (
        SELECT 
            ac.owner AS sub_owner,
            ac.table_name AS sub_table,
            acc.column_name AS sub_fk_column
        FROM all_constraints ac
        JOIN all_cons_columns acc 
            ON ac.owner = acc.owner AND ac.constraint_name = acc.constraint_name
        WHERE ac.r_owner = v_parent_owner
          AND ac.r_constraint_name = v_pk_constraint
          AND ac.constraint_type = 'R'
    ) LOOP
        -- 默认生成INNER JOIN，需左关联可改为LEFT JOIN
        v_sql := v_sql || ' INNER JOIN ' || sub_tabs.sub_owner || '.' || sub_tabs.sub_table
                 || ' ON ' || v_parent_owner || '.' || v_parent_table || '.' || v_pk_column
                 || ' = ' || sub_tabs.sub_owner || '.' || sub_tabs.sub_table || '.' || sub_tabs.sub_fk_column;
    END LOOP;

    -- 输出最终生成的SQL
    DBMS_OUTPUT.PUT_LINE(v_sql);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('父表未找到主键约束，请检查表名或用户权限');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('生成失败：' || SQLERRM);
END;
/

脚本说明 #

• 替换v_parent_table为目标父表名，注意Oracle表名大小写规则
• 若父表属于其他用户，修改v_parent_owner为对应用户名
• 可根据需求将INNER JOIN改为LEFT JOIN，保留父表无对应子表的数据
• 生成的SQL自动包含父表和所有子表的全部列，无需手动指定

示例输出（以Class和Student表为例） #

假设Class表主键为CLASS_ID，Student表外键为CLASS_ID，脚本运行后生成的SQL如下：

SELECT CLASS.*, STUDENT.* FROM CLASS 
INNER JOIN STUDENT ON CLASS.CLASS_ID = STUDENT.CLASS_ID

关于PowerBI/ASP.NET MVC模型方案的补充 #

通过PowerBI或Visual Studio生成数据库模型的方式，确实能快速获取关联数据，但无法直接得到可复用的SQL语句。如果需要直接在数据库端执行查询、导出SQL或进行性能调优，上述PL/SQL脚本的方案更直接高效。

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