时间戳

首页 > 解决方案 > 从包含这些数据列的所有表中选择数据

oracle - 从包含这些数据列的所有表中选择数据

问题描述

（我认为对于这里的大多数用户来说这可能是一个简单的问题..）

简短描述：我需要一种方法（可能使用我不知道的 PL/SQL ..）来“从包含此类数据的所有表中选择定义的数据”

详细说明（示例）：我有不同数量的不同表。其中一个经常变化的部分——我不知道编号和名称——包含“FID”列。现在我需要两个步骤： a) 选择所有包含“FID”列的表。（我知道如何单步执行此操作） b）从所有找到的表中选择值 FID 并显示它。

对我来说，问题是从a）到b）的步骤。对于已知的表，我会使用 UNION，但是对于表的动态结果，我不知道..

标签： oracleplsqloracle-sqldeveloper

解决方案

您可以使用XML 魔术的变体，通过使用dbms_xmlgen以下查询将所有值获取到 XML 文档中user_tab_columns：

select dbms_xmlgen.getxmltype(
       'select "' || column_name || '" from "' || table_name || '"')
from user_tab_columns
where upper(column_name) = 'FID'
and data_type = 'NUMBER';

...我假设FID应该是数字 ID，因此仅限于数字列（并且还允许表和列名称的混合大小写/引号标识符，以防万一）。这为每个表提供了一行，其中一个 XML 文档列出了该表中的 FID 值。

然后，您可以从该 XML 中提取单个值，再次作为数字：

with cte (xml) as (
  select dbms_xmlgen.getxmltype(
         'select "' || column_name || '" as fid from "' || table_name || '"')
  from user_tab_columns
  where upper(column_name) = 'FID'
  and data_type = 'NUMBER'
)
select x.fid
from cte
cross apply xmltable(
  '/ROWSET/ROW'
  passing cte.xml
  columns fid number path 'FID'
) x;

或者，如果您想查看每个值来自的表/列，只需将它们包含在 CTE 和选择列表中：

with cte (table_name, column_name, xml) as (
  select table_name, column_name, dbms_xmlgen.getxmltype(
         'select "' || column_name || '" as fid from "' || table_name || '"')
  from user_tab_columns
  where upper(column_name) = 'FID'
  and data_type = 'NUMBER'
)
select cte.table_name, cte.column_name, x.fid
from cte
cross apply xmltable(
  '/ROWSET/ROW'
  passing cte.xml
  columns fid number path 'FID'
) x;

如果要搜索其他模式，请all_tab_columns改用，并可选择包含每个表的所有者：

with cte (owner, table_name, column_name, xml) as (
  select owner, table_name, column_name, dbms_xmlgen.getxmltype(
         'select "' || column_name || '" as fid from "' || owner || '"."' || table_name || '"')
  from all_tab_columns
  where upper(column_name) = 'FID'
  and data_type = 'NUMBER'
)
select cte.owner, cte.table_name, cte.column_name, x.fid
from cte
cross apply xmltable(
  '/ROWSET/ROW'
  passing cte.xml
  columns fid number path 'FID'
) x;

db<>小提琴

这个技巧的基础至少可以追溯到2007 年，但可能比以前getxmltype()存在的更早（它似乎已在 10g 中添加）；我最初使用的是xmltype(getxml())：

select xmltype(dbms_xmlgen.getxml(
       'select "' || column_name || '" from "' || table_name || '"'))
from user_tab_columns
where upper(column_name) = 'FID'
and data_type = 'NUMBER';

大多数情况下都有效，但如果任何表为空，则会抛出 "ORA-06502: PL/SQL: numeric or value error"。

推荐阅读