动态跨多表查询不确定所属表的数据库对象：此类SQL嵌套查询是否可行？

嘿，这个需求挺典型的——要在一堆同类型的表里面找数据，但又不想每次新增表都改查询语句对吧？先直接给结论：你写的那个SQL没法直接运行，得换用动态SQL来实现。

为什么你的写法不行？ #

你写的语句是：

SELECT * FROM (SELECT table_name FROM user_tables where table_name like '%PASSENGER') WHERE NAME LIKE 'John%'

这里的问题在于：子查询返回的是字符串形式的表名列表，但FROM子句需要的是实际存在的数据库表对象，数据库没办法把一串字符串直接当成表来查询。执行这个语句的话，你大概率会收到“表或视图不存在”或者“列名无效”的报错——因为数据库会把子查询的结果当成一个普通的结果集，而不是表的集合。

正确的实现方式：动态SQL #

因为你需要根据查询到的表名动态生成查询语句，所以得用动态SQL来拼接并执行。下面以Oracle为例（你用到了user_tables，应该是Oracle环境），给出具体的实现：

方法1：用PL/SQL块执行并处理结果

如果需要在PL/SQL中直接处理查询到的乘客数据，可以这么写：

DECLARE
  v_dynamic_sql VARCHAR2(4000);
  -- 定义游标匹配乘客表结构（假设所有表结构一致）
  CURSOR c_passengers IS SELECT * FROM Domestic_Passengers WHERE 1=0;
  v_passenger c_passengers%ROWTYPE;
BEGIN
  -- 拼接所有符合条件的表的查询语句，用UNION ALL合并结果
  SELECT LISTAGG(
           'SELECT * FROM ' || table_name || ' WHERE NAME LIKE ''John%''',
           ' UNION ALL '
         ) INTO v_dynamic_sql
  FROM user_tables
  WHERE UPPER(table_name) LIKE '%PASSENGERS'; -- 匹配所有以PASSENGERS结尾的表

  -- 执行动态SQL并遍历结果
  OPEN c_passengers FOR v_dynamic_sql;
  LOOP
    FETCH c_passengers INTO v_passenger;
    EXIT WHEN c_passengers%NOTFOUND;
    -- 这里可以添加自定义处理逻辑，比如打印或插入临时表
    DBMS_OUTPUT.PUT_LINE('找到匹配乘客：' || v_passenger.NAME);
  END LOOP;
  CLOSE c_passengers;
END;
/

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

如果你想生成一条可以复制出来直接运行的SQL语句，可以用下面的查询：

SELECT LISTAGG(
         'SELECT * FROM ' || table_name || ' WHERE NAME LIKE ''John%''',
         ' UNION ALL '
       ) AS full_query
FROM user_tables
WHERE UPPER(table_name) LIKE '%PASSENGERS';

执行这个查询后，会得到一条拼接好的完整SQL，直接复制执行就能得到所有表中符合条件的记录。

关键注意事项 #

• 表结构一致性：所有%PASSENGERS表必须都有NAME字段，否则拼接后的SQL会报错。如果存在结构不一致的表，需要额外加判断逻辑。
• 权限问题：确保当前用户有所有这些乘客表的查询权限，user_tables返回的都是当前用户有权限访问的表，所以只要表存在且有权限就没问题。
• 性能考量：如果表数量多或者数据量大，UNION ALL可能会影响性能，可以考虑给NAME字段加索引，或者分批查询。
• 扩展性：未来新增UK_Passengers这类表，只要表名符合%PASSENGERS的规则，这个逻辑会自动包含新表，不需要修改代码。

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