Oracle - 从 SQL 文本中获取表名。

Question

Oracle - 从 SQL 文本中获取表名。

3

我在一个表格中有一列简单的SQL查询语句，我想从中使用regexp_substr函数提取表名。以下是一些文本示例：

SELECT PT.PT_PARTY_NAME VALUE,PT.PT_PARTY_NAME LABEL
    FROM DWH_OWNER.DWH_ACCOUNTS ACC,
         DWH_OWNER.DWH_PARTIES PT
   WHERE ACC.ACC_SOURCE_ID = :P_DOMVAL_REF1
    AND ACC.ACC_PT_KEY = PT.PT_KEY

或者

SELECT DISTINCT QRMPT_PRODUCT VALUE, QRMPT_PRODUCT LABEL FROM   
DWH_OWNER.DWH_QRM_PRODUCT_TYPES ORDER BY VALUE

所以我需要找到“FROM”，跳过一些空格，然后获取第一个单词。目前我不感兴趣第二个表。

我的做法：

select 
    sql, 
    regexp_substr(upper(sql), '(\s)FROM(\s)*([[:alnum:]]|\.|_)*') tablename
from my_table_with_queries;

输出：

 FROM DWH_OWNER.DWH_ACCOUNTS
FROM   
    DWH_OWNER.DWH_QRM_PRODUCT_TYPES

我的查询有什么问题：它输出带有表名之前的 "FROM "。我想直接获得表名，不带空格。

期望输出：

DWH_OWNER.DWH_ACCOUNTS
DWH_OWNER.DWH_QRM_PRODUCT_TYPES

编辑: 我通过在前一步中使用regexp_substr(tablename,'(\w|_|\.)+',1,2)完成了对表名的清理。但是是否可能使用单个正则表达式获得所需的输出结果？

这里应该有一个SQLFiddle，但此时该网站无法使用。查询语句：

  with a as (
select 'SELECT PT.PT_PARTY_NAME VALUE,PT.PT_PARTY_NAME LABEL
    FROM DWH_OWNER.DWH_ACCOUNTS ACC,
         DWH_OWNER.DWH_PARTIES PT
   WHERE ACC.ACC_SOURCE_ID = :P_DOMVAL_REF1
    AND ACC.ACC_PT_KEY = PT.PT_KEY' sql from dual
 union all

 select 'SELECT DISTINCT QRMPT_PRODUCT VALUE, QRMPT_PRODUCT LABEL FROM   
DWH_OWNER.DWH_QRM_PRODUCT_TYPES ORDER BY VALUE' from dual
  )

select 
       regexp_substr(upper(sql), '\sFROM\s*(\w|\.|_)*') tablename, sql
from a;

- Florin Ghita

可以简化为\sFROM\s*([\w.]*)吗？ - Thomas Ayoub

@Thomas 进行了一些小修改 - regexp_substr(sql,'\sFROM\s*(\w|\.)*') - 是的，但仍会输出 FROM。这就是我的问题。 - Florin Ghita

我不知道什么是“捕获组”。我对正则表达式是初学者。不是对Oracle，而是对正则表达式。 - Florin Ghita

使用您的表达式，我只得到了“FROM”。 - Florin Ghita

2

你是否在某个时候需要第二个表格？并且你需要允许使用CTE、内联视图和子查询吗？最终目标是找到语句所涉及的所有表格吗？ - Alex Poole

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Alex Poole · Accepted Answer

如果你不想自己编写解析器，可以通过explain plan让Oracle为你解析它，然后查看计划表以查看它所涉及的对象：

declare
  text varchar2(4000) := 'SELECT PT.PT_PARTY_NAME VALUE,PT.PT_PARTY_NAME LABEL
    FROM DWH_OWNER.DWH_ACCOUNTS ACC,
         DWH_OWNER.DWH_PARTIES PT
   WHERE ACC.ACC_SOURCE_ID = :P_DOMVAL_REF1
    AND ACC.ACC_PT_KEY = PT.PT_KEY';
begin
  execute immediate 'explain plan for ' || text;
end;
/

select distinct object_owner, object_name
from plan_table
where object_type = 'TABLE';

OBJECT_OWNER                   OBJECT_NAME                  
------------------------------ ------------------------------
DWH_OWNER                      DWH_ACCOUNTS                  
DWH_OWNER                      DWH_PARTIES

正如@Aleksej所建议的那样，如果优化器只使用一个索引（因此执行计划显示索引访问/扫描而不会访问表格，因为所有相关列都在索引中），则计划表仅报告索引。您可以通过加入索引视图来允许它; 如果它也命中了表，则会同时报告：

select distinct case when pt.object_type = 'INDEX' then ai.table_owner
    else pt.object_owner end as owner,
  case when pt.object_type = 'INDEX' then ai.table_name
    else pt.object_name end as table_name
from plan_table pt
left join all_indexes ai on ai.owner = pt.object_owner
and ai.index_name = pt.object_name
where pt.object_type in ('TABLE', 'INDEX');

在每次执行解释计划和查询之前，您还需要确保计划表为空，以避免混淆，或者设置语句ID以便识别与当前查询相关的表。