Oracle SQL：跨PSI模式所有含OID列的表查询最大数字序列值

需要编写一条SELECT查询语句，在包含数百张表的Oracle数据库中，找出跨所有表共享的ID列的最大值（该序列并非正式Sequence对象）。数据库中，PSI模式下的任意表都会为每条记录生成带后缀的唯一连续ID，示例如下：

psi.Customer表：

OID
1001AAA
1002AAA
1003AAA
1006AAA

psi.Item表：

OID
1004BBB
1005BBB
1007BBB

可见数字序列在所有表中共享，目标是获取整个数据库中该数字序列的最大值（忽略后缀），即示例中的1007。

目前已能通过手动联合少量表的查询得到结果：

select * from (
    select to_number(substr(oid,1,length(oid)-3)) as num_id from psi.Customer
    union
    select to_number(substr(oid,1,length(oid)-3)) as num_id from psi.Item
    union
    select to_number(substr(oid,1,length(oid)-3)) as num_id from psi.Country
    order by num_id desc
)
where rownum=1

但不想逐个添加表名，希望自动联合PSI模式下所有包含OID列的表来查询。

利用Oracle的数据字典视图自动筛选目标表，通过以下两种方式实现需求：

方法1：PL/SQL块直接获取最大值 #

通过PL/SQL拼接动态查询并执行，直接输出结果：

DECLARE
    v_max_id NUMBER;
    v_sql CLOB;
BEGIN
    -- 拼接所有目标表的查询语句
    SELECT LISTAGG(
               'SELECT TO_NUMBER(SUBSTR(oid, 1, LENGTH(oid)-3)) AS num_id FROM ' || table_name,
               ' UNION ALL '
           ) WITHIN GROUP (ORDER BY table_name)
    INTO v_sql
    FROM all_tab_columns
    WHERE owner = 'PSI'
      AND column_name = 'OID';

    -- 组装外层取最大值的查询
    v_sql := 'SELECT MAX(num_id) FROM (' || v_sql || ')';

    -- 执行并获取结果
    EXECUTE IMMEDIATE v_sql INTO v_max_id;

    DBMS_OUTPUT.PUT_LINE('最大共享数字ID: ' || v_max_id);
END;
/

注意事项：

• 若当前用户仅能访问自身模式下的表，替换all_tab_columns为user_tab_columns即可。
• 使用UNION ALL替代UNION，避免不必要的去重操作，提升查询效率。

方法2：生成可直接执行的静态SQL #

如果需要生成一条完整的静态查询语句（方便复制执行），运行以下SQL：

SELECT 'SELECT MAX(num_id) FROM (' ||
       LISTAGG(
           'SELECT TO_NUMBER(SUBSTR(oid, 1, LENGTH(oid)-3)) AS num_id FROM PSI.' || table_name,
           ' UNION ALL '
       ) WITHIN GROUP (ORDER BY table_name) ||
       ')' AS full_query
FROM all_tab_columns
WHERE owner = 'PSI'
  AND column_name = 'OID';

执行后会输出拼接好的完整查询语句，复制该语句执行就能得到最大值。

内容的提问来源于stack exchange，提问作者L'le Tom