Oracle 10g：含MARKET_ID表的行数统计及级联删除关联查询需求

一、统计含MARKET_ID列的表中MARKET_ID=1的行数 #

纯SQL实现（无需PL/SQL循环） #

利用DBMS_XMLGEN将动态查询结果转换为可查询数据集，适配Oracle 10g特性：

WITH main_tables AS (
    SELECT owner, table_name
    FROM all_tab_columns
    WHERE column_name = 'MARKET_ID'
)
SELECT
    mt.owner,
    mt.table_name,
    TO_NUMBER(
        EXTRACTVALUE(
            DBMS_XMLGEN.getxmltype(
                'SELECT COUNT(*) cnt FROM "' || mt.owner || '"."' || mt.table_name || '" WHERE MARKET_ID = 1'
            ),
            '/ROWSET/ROW/CNT'
        )
    ) AS rows_per_table
FROM main_tables mt
ORDER BY mt.table_name;

PL/SQL批量输出（灵活处理场景） #

SET SERVEROUTPUT ON;
DECLARE
    v_count NUMBER;
BEGIN
    FOR rec IN (
        SELECT owner, table_name
        FROM all_tab_columns
        WHERE column_name = 'MARKET_ID'
        ORDER BY table_name
    ) LOOP
        EXECUTE IMMEDIATE 
            'SELECT COUNT(*) FROM "' || rec.owner || '"."' || rec.table_name || '" WHERE MARKET_ID = 1'
            INTO v_count;
        DBMS_OUTPUT.PUT_LINE('OWNER: ' || rec.owner || ', TABLE_NAME: ' || rec.table_name || ', ROWS_PER_TABLE: ' || v_count);
    END LOOP;
END;
/

二、统计关联子表（外键关联）中MARKET_ID=1的行数 #

步骤1：获取主表与子表的外键关系 #

通过数据字典表定位外键关联（子表外键引用主表MARKET_ID列）：

WITH main_tables AS (
    SELECT owner, table_name
    FROM all_tab_columns
    WHERE column_name = 'MARKET_ID'
),
fk_relations AS (
    SELECT
        mc.owner AS main_owner,
        mc.table_name AS main_table,
        cc.owner AS child_owner,
        cc.table_name AS child_table,
        cc.column_name AS child_fk_column, -- 子表中对应的外键列名
        c.constraint_name AS fk_name
    FROM all_constraints c
    JOIN all_cons_columns cc 
        ON c.owner = cc.owner 
        AND c.constraint_name = cc.constraint_name
    JOIN all_cons_columns mc 
        ON c.r_owner = mc.owner 
        AND c.r_constraint_name = mc.constraint_name
    JOIN main_tables mt 
        ON mc.owner = mt.owner 
        AND mc.table_name = mt.table_name
    WHERE c.constraint_type = 'R' -- 标记外键约束
        AND mc.column_name = 'MARKET_ID'
)
SELECT * FROM fk_relations;

步骤2：统计子表关联行数 #

结合外键关系，动态统计子表中MARKET_ID=1的行数：

WITH main_tables AS (
    SELECT owner, table_name
    FROM all_tab_columns
    WHERE column_name = 'MARKET_ID'
),
fk_relations AS (
    SELECT
        mc.owner AS main_owner,
        mc.table_name AS main_table,
        cc.owner AS child_owner,
        cc.table_name AS child_table,
        cc.column_name AS child_fk_column,
        c.constraint_name AS fk_name
    FROM all_constraints c
    JOIN all_cons_columns cc 
        ON c.owner = cc.owner 
        AND c.constraint_name = cc.constraint_name
    JOIN all_cons_columns mc 
        ON c.r_owner = mc.owner 
        AND c.r_constraint_name = mc.constraint_name
    JOIN main_tables mt 
        ON mc.owner = mt.owner 
        AND mc.table_name = mt.table_name
    WHERE c.constraint_type = 'R'
        AND mc.column_name = 'MARKET_ID'
)
SELECT
    fr.main_owner,
    fr.main_table,
    fr.child_owner,
    fr.child_table,
    fr.fk_name,
    TO_NUMBER(
        EXTRACTVALUE(
            DBMS_XMLGEN.getxmltype(
                'SELECT COUNT(*) cnt FROM "' || fr.child_owner || '"."' || fr.child_table || '" WHERE "' || fr.child_fk_column || '" = 1'
            ),
            '/ROWSET/ROW/CNT'
        )
    ) AS child_rows
FROM fk_relations fr
ORDER BY fr.main_table, fr.child_table;

步骤3：合并主表与子表统计结果（可选） #

将主表行数与子表行数关联展示：

WITH main_tables AS (
    SELECT owner, table_name
    FROM all_tab_columns
    WHERE column_name = 'MARKET_ID'
),
main_counts AS (
    SELECT
        mt.owner AS main_owner,
        mt.table_name AS main_table,
        TO_NUMBER(
            EXTRACTVALUE(
                DBMS_XMLGEN.getxmltype(
                    'SELECT COUNT(*) cnt FROM "' || mt.owner || '"."' || mt.table_name || '" WHERE MARKET_ID = 1'
                ),
                '/ROWSET/ROW/CNT'
            )
        ) AS main_rows
    FROM main_tables mt
),
fk_relations AS (
    SELECT
        mc.owner AS main_owner,
        mc.table_name AS main_table,
        cc.owner AS child_owner,
        cc.table_name AS child_table,
        cc.column_name AS child_fk_column,
        c.constraint_name AS fk_name
    FROM all_constraints c
    JOIN all_cons_columns cc 
        ON c.owner = cc.owner 
        AND c.constraint_name = cc.constraint_name
    JOIN all_cons_columns mc 
        ON c.r_owner = mc.owner 
        AND c.r_constraint_name = mc.constraint_name
    JOIN main_tables mt 
        ON mc.owner = mt.owner 
        AND mc.table_name = mt.table_name
    WHERE c.constraint_type = 'R'
        AND mc.column_name = 'MARKET_ID'
),
child_counts AS (
    SELECT
        fr.main_owner,
        fr.main_table,
        fr.child_owner,
        fr.child_table,
        fr.fk_name,
        TO_NUMBER(
            EXTRACTVALUE(
                DBMS_XMLGEN.getxmltype(
                    'SELECT COUNT(*) cnt FROM "' || fr.child_owner || '"."' || fr.child_table || '" WHERE "' || fr.child_fk_column || '" = 1'
                ),
                '/ROWSET/ROW/CNT'
            )
        ) AS child_rows
    FROM fk_relations fr
)
SELECT
    mc.main_owner,
    mc.main_table,
    mc.main_rows,
    cc.child_owner,
    cc.child_table,
    cc.fk_name,
    cc.child_rows
FROM main_counts mc
LEFT JOIN child_counts cc 
    ON mc.main_owner = cc.main_owner 
    AND mc.main_table = cc.main_table
ORDER BY mc.main_table, cc.child_table;

完善后的PL/SQL版本（嵌套循环统计） #

在已有PL/SQL基础上扩展，遍历主表+子表并完成统计：

SET SERVEROUTPUT ON;
DECLARE
    v_main_count NUMBER;
    v_child_count NUMBER;
BEGIN
    -- 遍历所有含MARKET_ID的主表
    FOR main_rec IN (
        SELECT owner, table_name
        FROM all_tab_columns
        WHERE column_name = 'MARKET_ID'
        ORDER BY table_name
    ) LOOP
        -- 统计主表MARKET_ID=1的行数
        EXECUTE IMMEDIATE 
            'SELECT COUNT(*) FROM "' || main_rec.owner || '"."' || main_rec.table_name || '" WHERE MARKET_ID = 1'
            INTO v_main_count;
        DBMS_OUTPUT.PUT_LINE('主表：OWNER=' || main_rec.owner || ', TABLE_NAME=' || main_rec.table_name || ', 行数=' || v_main_count);
        
        -- 遍历当前主表的关联子表
        FOR child_rec IN (
            SELECT
                cc.owner AS child_owner,
                cc.table_name AS child_table,
                cc.column_name AS child_column,
                c.constraint_name AS fk_name
            FROM all_constraints c
            JOIN all_cons_columns cc 
                ON c.owner = cc.owner 
                AND c.constraint_name = cc.constraint_name
            JOIN all_cons_columns mc 
                ON c.r_owner = mc.owner 
                AND c.r_constraint_name = mc.constraint_name
            WHERE c.constraint_type = 'R'
                AND mc.owner = main_rec.owner
                AND mc.table_name = main_rec.table_name
                AND mc.column_name = 'MARKET_ID'
        ) LOOP
            -- 统计子表关联行数
            EXECUTE IMMEDIATE 
                'SELECT COUNT(*) FROM "' || child_rec.child_owner || '"."' || child_rec.child_table || '" WHERE "' || child_rec.child_column || '" = 1'
                INTO v_child_count;
            DBMS_OUTPUT.PUT_LINE('  子表：OWNER=' || child_rec.child_owner || ', TABLE_NAME=' || child_rec.child_table || ', 外键=' || child_rec.fk_name || ', 行数=' || v_child_count);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('----------------------------------------');
    END LOOP;
END;
/

内容的提问来源于stack exchange，提问作者Peter The Angular Dude