如何动态获取从all_tab_columns筛选出的表的最大创建日期

问题背景 #

已从all_tab_columns中筛选出包含DATE类型字段的表及对应列，需要获取每张表对应日期字段的最大值。由于筛选all_tab_columns时的WHERE条件会变化，表名和列名也会随之改变，因此需要动态实现该需求。

样例数据 #

WITH
    tabs (TABLE_NAME, COLUMN_NAME, DATA_TYPE) AS
        (
            Select 'A_ZR_6', 'CREATED_DATE', 'DATE' From dual Union All
            Select 'A_ZR_8', 'CREATEDDATE', 'DATE' From dual Union All
            Select 'A_ZR_2', 'CREATED_DATE', 'DATE' From dual Union All
            Select 'A_ZR_4', 'CREATED_DATE', 'DATE' From dual Union All
            Select 'A_ZR_9', 'CREATED_DATE', 'DATE' From dual 
        )

预期结果 #

当前筛选代码 #

select 
  table_name,
  column_name 
from
  all_tab_columns 
where 
  owner='ABC' and 
  table_name not like 'V_%' and
  lower(column_name) like '%create%' and 
  lower(column_name) like '%date%' 
group by
  table_name,
  column_name 

动态实现方案 #

方案1：PL/SQL生成并执行动态SQL（推荐） #

通过游标遍历筛选出的表和列，动态拼接每个表的最大值查询语句，统一执行后输出结果。

SET SERVEROUTPUT ON;
DECLARE
  v_sql VARCHAR2(4000);
  v_first BOOLEAN := TRUE;
BEGIN
  -- 初始化动态SQL框架
  v_sql := 'SELECT * FROM (';
  
  -- 遍历筛选出的表和列
  FOR rec IN (
    select 
      table_name,
      column_name 
    from
      all_tab_columns 
    where 
      owner='ABC' and 
      table_name not like 'V_%' and
      lower(column_name) like '%create%' and 
      lower(column_name) like '%date%' 
    group by
      table_name,
      column_name 
  ) LOOP
    IF NOT v_first THEN
      v_sql := v_sql || ' UNION ALL ';
    END IF;
    -- 拼接单表最大值查询语句
    v_sql := v_sql || 'SELECT ''' || rec.table_name || ''' AS TABLE_NAME, MAX(' || rec.column_name || ') AS MAX_DATE FROM ' || rec.table_name;
    v_first := FALSE;
  END LOOP;
  
  v_sql := v_sql || ') ORDER BY TABLE_NAME';
  
  -- 执行动态SQL并打印结果
  DECLARE
    TYPE result_rec IS RECORD (
      table_name VARCHAR2(128),
      max_date DATE
    );
    TYPE result_tab IS TABLE OF result_rec;
    v_result result_tab;
  BEGIN
    EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_result;
    
    DBMS_OUTPUT.PUT_LINE('| TABLE_NAME | MAX_DATE   |');
    DBMS_OUTPUT.PUT_LINE('|------------|------------|');
    FOR i IN v_result.FIRST .. v_result.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('| ' || RPAD(v_result(i).table_name, 10) || ' | ' || TO_CHAR(v_result(i).max_date, 'DD-MON-RR') || ' |');
    END LOOP;
  END;
END;
/

方案2：XMLTABLE结合动态SQL一次性查询 #

利用Oracle XML特性，将单表查询结果转换为XML后统一解析输出。

WITH tab_columns AS (
  select 
    table_name,
    column_name 
  from
    all_tab_columns 
  where 
    owner='ABC' and 
    table_name not like 'V_%' and
    lower(column_name) like '%create%' and 
    lower(column_name) like '%date%' 
  group by
    table_name,
    column_name 
)
SELECT
  xt.table_name,
  xt.max_date
FROM tab_columns tc,
     XMLTABLE(
       '/ROWSET/ROW'
       PASSING DBMS_XMLGEN.GETXMLTYPE('SELECT ''' || tc.table_name || ''' AS TABLE_NAME, MAX(' || tc.column_name || ') AS MAX_DATE FROM ' || tc.table_name)
       COLUMNS 
         table_name VARCHAR2(128) PATH 'TABLE_NAME',
         max_date DATE PATH 'MAX_DATE'
     ) xt
ORDER BY xt.table_name;

注意事项 #

• 执行用户需拥有目标表的SELECT权限，否则会触发权限错误。
• 若筛选条件变更，仅需修改游标或CTE中的WHERE子句，核心逻辑无需调整。

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