Вопрос по oracle – Вернуть имена столбцов Oracle в формате table.column?

6

Существуют ли какие-либо настройки или методы, которые я могу использовать, чтобы Oracle возвращал результаты в<table>.<column> формат? Например:

Запрос:

SELECT     *
FROM       foo f
INNER JOIN bar b
ON         b.foo_id = f.id

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

F.ID  F.BLAH  B.ID  B.FOO_ID  B.BLAH
--------------------------------------------------------
1     blah    7     1         blah
2     blah    8     2         blah
3     blah    9     2         blah

Очевидное решение состоит в том, чтобы по отдельности псевдоним каждого столбцаSELECT f.id AS F_ID, ...; однако мне нужно экспортировать некоторые очень большие устаревшие таблицы (более 300 столбцов), поэтому использование этого метода приведет к тому, что запросы будут огромными и непрактичными.

Как вы экспортируете данные? Может быть, эта логика принадлежит этому инструменту экспорта, а не отдельным операторам? Jon Heller
Я не компетентен в PL / SQL, но, возможно, вы можете попытаться переименовать столбцы таблиц, прежде чем присоединиться к ним. bonsvr

Ваш Ответ

2   ответа
8

Нет "опции" в Oracle, чтобы сделать это; выmay быть в состоянии найти клиента, который позволяет вам это сделать, поскольку эта работа обычно выполняется на клиенте; Я не знаю ни одного.

Расширить наответ Tbone вам придется делать это динамически. этоdoes not означает, что вы должны перечислить каждый столбец. Вы бы использовалисловарь данныхконкретноall_tab_columns или жеuser_tab_columns создать свой запрос. Было бы проще создать представление с точным определением, которое вы хотите, чтобы вы могли использовать его повторно, если хотите.

Цель состоит в том, чтобы использовать тот факт, что существование столбцов хранится в таблице в виде строки, чтобы создать запрос для использования этого столбца. Поскольку имена столбцов и имена таблиц хранятся в виде строк, вы можете использовать методы агрегирования строк, чтобы легко создать запрос или оператор DDL, который затем можно выполнить вручную или динамически.

Если вы используете Oracle 11g Release 2,listagg Функция доступна, чтобы помочь вам:

select 'create or replace view my_view as 
        select '
      || listagg( table_name || '.' || column_name 
               || ' as ' 
               || substr(table_name,1,1) || '_' 
               || column_name, ', ')
        within group 
         ( order by case when table_name = 'FOO' then 0 else 1 end
                  , column_id
          )
       || ' from foo f
            join bar b
              on f.id = b.foo_id'
  from user_tab_columns
 where table_name in ('FOO','BAR')
        ;

Предполагая эту структуру таблицы:

create table foo ( id number, a number, b number, c number);
create table bar ( foo_id number, a number, b number, c number);

Этот единственный запрос производит следующее:

create or replace view my_view as 
 select FOO.ID as F_ID, FOO.A as F_A, FOO.B as F_B, FOO.C as F_C
      , BAR.FOO_ID as B_FOO_ID, BAR.A as B_A, BAR.B as B_B, BAR.C as B_C 
   from foo f 
   join bar b on f.id = b.foo_id

и здесьSQL Fiddle чтобы доказать это.

Если вы не используете 11.2, вы можете достичь точно таких же результатов, используя недокументированную функциюwm_concat или пользовательская функцияstragg, который был создан Tom Kyte. Oracle Base имеет статью ометоды агрегирования строк и есть много сообщений о переполнении стека.

В качестве небольшого дополнения вы можете создать именно то, что вы ищете, с небольшим изменением вышеприведенного запроса. Вы можете использоватьцитируемый идентификатор создать столбец вTABLE_NAME.COLUMN_NAME формат. Выhave процитировать это как. не является допустимым символом для имени объекта в Oracle. Преимущество этого заключается в том, что вы получаете именно то, что вы хотите. Недостатком является то, что запрос созданного представления является огромной болью, если вы не используетеselect * from ...; Выбор именованных столбцовrequire их нужно процитировать.

select 'create or replace view my_view as
        select '
      || listagg( table_name || '.' || column_name 
               || ' as ' 
               || '"' || table_name || '.'
               || column_name || '"', ', ')
        within group 
         ( order by case when table_name = 'FOO' then 0 else 1 end
                  , column_id
          )
       || ' from foo f
            join bar b
              on f.id = b.foo_id'
  from user_tab_columns
 where table_name in ('FOO','BAR')
        ;

Этот запрос возвращает:

create or replace view my_view as 
 select FOO.ID as "FOO.ID", FOO.A as "FOO.A", FOO.B as "FOO.B", FOO.C as "FOO.C"
      , BAR.FOO_ID as "BAR.FOO_ID", BAR.A as "BAR.A"
      , BAR.B as "BAR.B", BAR.C as "BAR.C"
   from foo f 
   join bar b on f.id = b.foo_id
3

Использование псевдонимов не сделает запросы неосуществимыми, просто это не так удобно, как ввод *. Используйте динамический SQL для генерации столбцов для вас:

select 'f.' || column_name || ' as F_' || column_name || ','
from all_tab_columns
where table_name = 'FOO'
order by column_id;

Сделайте то же самое для любых других широких таблиц, которые вам нужны, и скопируйте / вставьте в свой запрос. Также обратите внимание на ограничение в 30 символов, надеюсь, ни один из ваших столбцов не превышает 28.

Спасибо за предложение, но я специально просил решение, которое не предполагает явного перечисления столбцов. FtDRbwLXw6

Похожие вопросы