Oracle-从SQL文本获取表名 [英] Oracle - get table name from sql text

问题描述

我在具有简单sql查询的表中有一列，并且我想通过它们对表名进行regexp_substr. 文字示例:

I have a column in a table with simple sql queries and I want to regexp_substr the table name from them. Examples of texts:

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"，跳过一些空格，然后得到第一个单词. 我暂时对第二张桌子不感兴趣.

So, I need to find the "FROM", skip some whitespaces then get the first word. I'm not interested for the moment in the second table.

我所做的:

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

我的qyery有什么问题: 它输出表名，并在表名之前输出"FROM".我要直接使用表名，而不能使用空格.

What's wrong with my qyery: It outputs the tablename with the "FROM " before the table name. I want the table name directly, without whitespaces.

所需的输出:

DWH_OWNER.DWH_ACCOUNTS
DWH_OWNER.DWH_QRM_PRODUCT_TYPES

在上一步中，我设法做了regexp_substr(tablename, '(\w|_|\.)+', 1,2)来清理表名.但是可以通过一个正则表达式获得所需的输出吗?

I managed to do regexp_substr(tablename, '(\w|_|\.)+', 1,2) over the previous step to do the tablename clean. But It is possible to get the desired output with a single regexp?

此处应该是sqlfiddle，但此刻此站点无法正常工作.查询:

Here should be an sqlfiddle, but the site does not work in this moment. The query:

  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;

推荐答案

与其尝试编写自己的解析器，不如让Oracle通过explain plan为您解析它，然后查看计划表以查看哪些对象.它指的是:

Rather than try to write your own parser, you could let Oracle parse it for you via explain plan, and then look at the plan table to see which objects it refers to:

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所建议的，如果优化器仅使用索引(因此执行计划显示索引访问/扫描而没有命中表，因为所有相关列都在索引中)，则计划表仅报告索引.您可以通过加入索引视图来实现这一点；如果它也碰到桌子，它只会为两个报告它:

As @Aleksej suggested, if the optimiser only uses an index (so the execution plan shows index access/scan without hitting the table, because all the relevant columns are in the index) then the plan table only reports the index. You could allow for that by joining to the index view; if it hits the table too it'll just report it for both:

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，以便标识与当前查询相关的表.

You also need to make sure the plan table is empty before each explain plan call and query to avoid confusion, or set a statement ID so you can identify which tables related to the current query.

这篇关于Oracle-从SQL文本获取表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！