Oracle中如何遍历ALL_TABLES获取指定表的MAX(LOADED_TIMESTAMP)

你的代码核心问题是执行了动态查询但没有捕获结果，而且没配置好输出环境，所以要么报错要么看不到结果。我帮你一步步修正，同时实现自动统计和历史记录留存的需求：

一、先解决「执行成功但无结果输出」的问题 #

首先，你需要捕获动态SQL返回的MAX(LOADED_TIMESTAMP)值，然后通过DBMS_OUTPUT输出。另外要确保SQL Developer已经开启了DBMS_OUTPUT面板（顶部菜单「视图」→「DBMS输出」，然后点击面板上的绿色加号启用）。

修正后的测试代码：

DECLARE
    v_last_loaded TIMESTAMP; -- 存储每个表的最大时间戳
    my_sql VARCHAR(1000);
BEGIN
    -- 遍历符合条件的表
    FOR t IN (
        SELECT t.table_name, t.owner
        FROM all_tables t
        WHERE owner = 'ME'
          AND SUBSTR(t.table_name, 1, 2) IN ('A_', 'F_', 'P_')
    ) LOOP
        BEGIN
            -- 构造动态SQL，注意列名是dw_loaded_timestamp（你代码里写的是这个，和问题描述的LOADED_TIMESTAMP一致吗？注意核对）
            my_sql := 'SELECT MAX(dw_loaded_timestamp) FROM ' || t.owner || '.' || t.table_name;
            -- 执行查询并将结果存入变量
            EXECUTE IMMEDIATE my_sql INTO v_last_loaded;
            
            -- 输出表名和对应最大时间戳
            DBMS_OUTPUT.PUT_LINE('表名: ' || t.table_name || ' | 最后加载时间: ' || NVL(TO_CHAR(v_last_loaded, 'YYYY-MM-DD HH24:MI:SS'), '无数据'));
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('表名: ' || t.table_name || ' | 无数据');
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('表名: ' || t.table_name || ' 出错 -- ' || SQLCODE || ' -- ' || SQLERRM);
        END;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('全局错误!! -- ' || SQLCODE || '-- ' || SQLERRM || ' --' );
END;
/

关键修改点： #

• 新增v_last_loaded变量存储查询结果，用EXECUTE IMMEDIATE ... INTO捕获动态SQL的返回值
• 给每个表的查询单独加了局部异常处理，避免一个表出错导致整个循环终止
• 用NVL处理空值（表无数据时显示「无数据」）
• 明确输出格式，方便查看

二、实现「每日写入历史表留存记录」的需求 #

首先需要创建一个历史记录表，用来存储每日的统计结果：

CREATE TABLE ME.TABLE_LOAD_HISTORY (
    RECORD_DATE DATE DEFAULT SYSDATE, -- 统计日期（默认当天）
    OWNER VARCHAR2(30),
    TABLE_NAME VARCHAR2(30),
    LAST_LOADED_TIMESTAMP TIMESTAMP
);

然后修改PL/SQL代码，在输出的同时插入数据到历史表：

DECLARE
    v_last_loaded TIMESTAMP;
    my_sql VARCHAR(1000);
BEGIN
    FOR t IN (
        SELECT t.table_name, t.owner
        FROM all_tables t
        WHERE owner = 'ME'
          AND SUBSTR(t.table_name, 1, 2) IN ('A_', 'F_', 'P_')
    ) LOOP
        BEGIN
            my_sql := 'SELECT MAX(dw_loaded_timestamp) FROM ' || t.owner || '.' || t.table_name;
            EXECUTE IMMEDIATE my_sql INTO v_last_loaded;
            
            -- 插入历史表
            INSERT INTO ME.TABLE_LOAD_HISTORY (OWNER, TABLE_NAME, LAST_LOADED_TIMESTAMP)
            VALUES (t.owner, t.table_name, v_last_loaded);
            
            DBMS_OUTPUT.PUT_LINE('已记录: ' || t.table_name || ' | 最后加载时间: ' || NVL(TO_CHAR(v_last_loaded, 'YYYY-MM-DD HH24:MI:SS'), '无数据'));
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- 无数据时也插入记录，时间戳为空
                INSERT INTO ME.TABLE_LOAD_HISTORY (OWNER, TABLE_NAME)
                VALUES (t.owner, t.table_name);
                DBMS_OUTPUT.PUT_LINE('已记录: ' || t.table_name || ' | 无数据');
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('表名: ' || t.table_name || ' 出错，未记录 -- ' || SQLCODE || ' -- ' || SQLERRM);
        END;
    END LOOP;
    -- 提交事务（如果是自动提交模式可以省略，建议显式提交）
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('全局错误!! -- ' || SQLCODE || '-- ' || SQLERRM || ' --' );
        ROLLBACK;
END;
/

注意事项： #

1. 确认列名：你代码里用的是dw_loaded_timestamp，但问题描述里是LOADED_TIMESTAMP，请核对实际列名，避免报错
2. 权限：确保用户ME有ALL_TABLES的查询权限，以及目标表的查询权限，还有TABLE_LOAD_HISTORY的插入权限
3. 自动化：如果要每日自动执行，可以把这段PL/SQL封装成存储过程，然后通过Oracle的Job或者DBMS_SCHEDULER设置定时任务

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