Oracle – получить имя таблицы из текста sql

У меня есть столбец в таблице с простыми 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

or

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

Итак, мне нужно найти «ОТ», пропустить несколько пробелов, а затем получить первое слово. Меня пока не интересует вторая таблица.

Что я сделал:

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» перед именем таблицы. Я хочу имя таблицы напрямую, без пробелов.

Желаемый результат:

DWH_OWNER.DWH_ACCOUNTS
DWH_OWNER.DWH_QRM_PRODUCT_TYPES

EDIT: мне удалось сделать 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;

regex oracle regexp-substr
person Florin Ghita    schedule 09.02.2016    source источник
comment
Можно ли упростить его до \sFROM\s*([\w.]*)?   -  person Thomas Ayoub    schedule 09.02.2016
comment
@Thomas с небольшой модификацией - regexp_substr(sql,'\sFROM\s*(\w|\.)*') - да, но по-прежнему будет выводить FROM. Это моя проблема.   -  person Florin Ghita    schedule 09.02.2016
comment
Почему вы используете группы захвата вокруг \s ?   -  person Thomas Ayoub    schedule 09.02.2016
comment
Я не знаю, что такое группа захвата. Я новичок в регулярных выражениях. Не с Oracle, а с регулярными выражениями да :)   -  person Florin Ghita    schedule 09.02.2016
comment
С вашим выражением я получаю только 'FROM'.   -  person Florin Ghita    schedule 09.02.2016
comment
Вы хотите второй стол в какой-то момент? И нужно ли будет разрешать CTE, встроенные представления и подзапросы? Какова конечная цель — найти все таблицы, на которые ссылается оператор?   -  person Alex Poole    schedule 09.02.2016
comment
@Alex Алекс, мне понадобятся другие таблицы, но запросы плоские. Нет подзапросов. Таблицы — это первый шаг, столбцы — второй, но это моя работа. Я хочу только знать, как получить следующее слово после определенного слова.   -  person Florin Ghita    schedule 09.02.2016

Ответы (1)


arrow_upward
7
arrow_downward

Вместо того, чтобы пытаться написать свой собственный синтаксический анализатор, вы можете позволить Oracle проанализировать его для вас через explain plan, а затем просмотреть таблицу плана, чтобы увидеть, к каким объектам он относится:

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');

Вы также должны убедиться, что таблица плана пуста перед каждым вызовом и запросом плана объяснения, чтобы избежать путаницы, или установить идентификатор оператора, чтобы вы могли определить, какие таблицы связаны с текущим запросом.

person Alex Poole    schedule 09.02.2016
comment
Это приятно :) это нестандартное мышление. - person Florin Ghita; 09.02.2016
comment
Хороший. Только одно: что, если CBO будет использовать индекс вместо своей таблицы? может это быть проблемой или нет? Я пытался использовать DBMS_SQL, но мне кажется, что он может отдавать столбцы, а не таблицы - person Aleksej; 09.02.2016
comment
@Aleksej - да, я думал об использовании dbms_sql.parse, но не нашел способа получить информацию о таблице. Хорошее замечание об индексах. Хм. Играя с запросом Флорина, если у меня есть индекс для DHW_ACCOUNTS(ACC_SOURCE_ID, ACC_PT_KEY), то отображение плана показывает, что он выполняет сканирование диапазона по этому индексу, и не говорит, что он попадает в таблицу; но мой запрос по-прежнему показывает обе таблицы. - person Alex Poole; 09.02.2016
comment
@Aleksej - нет, это неправда; У меня все еще был план предыдущего запроса. Однако вы можете присоединиться к all_indexes, чтобы получить имя таблицы, если object_type является индексом. - person Alex Poole; 09.02.2016
comment
В итоге отличное, элегантное решение. Спасибо - person Aleksej; 09.02.2016
comment
Немного неловко признавать, что я опубликовал почти проблему xy - person Florin Ghita; 10.02.2016