Цикл FOR в Oracle SQL или применение SQL к нескольким таблицам Oracle

Мой SQL немного ржавый, поэтому я не знаю, возможно ли следующее:

У меня есть несколько таблиц t_a, t_b, t_c с одинаковым расположением столбцов, и я хочу применить к ним одну и ту же операцию, а именно вывести некоторую агрегацию в другую таблицу. Для таблицы t_x это будет выглядеть так:

CREATE TABLE t_x_aggregate (
    <here the col definitions which are the same for all new tables t_[abc]_aggregate>
);

INSERT INTO t_x_aggregate(id, ...)
SELECT id, SUM(factor*amount)
FROM t_x
WHERE some fixed condition
GROUP BY id;

Теперь я хочу выполнить что-то вроде цикла FOR вокруг этого:

for t_x in t_a, t_b, t_c
    CREATE TABLE ...
    INSERT INTO ...
end for

Возможно ли это в SQL? Или мне нужно будет создать оболочку на другом языке для этого?


sql oracle oracle18c
person halloleo    schedule 28.09.2020    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Итак, результатом этой операции будет 3 новые таблицы? T_A_AGGREGATE, T_B_AGGREGATE и T_C_AGGREGATE?

Я думаю, что самый быстрый способ - написать 3 отдельных оператора CREATE TABLE, например.

create table t_a_aggregate as
  select id, sum(factor * amount) suma
  from t_a
  where some_condition
  group by id;

create table t_b_aggregate as
  select id, sum(factor * amount) suma
  from t_b
  where some_condition
  group by id;

create table t_c_aggregate as
  select id, sum(factor * amount) suma
  from t_c
  where some_condition
  group by id;

ХОРОШО; Я понимаю, что запросы не такие уж простые, но особо ничего не меняется - только имена таблиц в CREATE и FROM (возможно, где-то еще, но это более-менее). Возможности поиска/замены любого приличного текстового редактора должны быть в состоянии сделать это быстро.

Если вы хотите сделать это динамически в цикле (читай: PL/SQL), вы можете, но динамический SQL не масштабируется, его сложно поддерживать и сложно отлаживать. Поэтому, если вы делаете это только один раз, рассмотрите возможность запуска 3 отдельных инструкций.

Как это сделать динамически?

Вам нужно будет создать строку (мы обычно помещаем их в локально объявленную переменную), содержащую весь оператор DDL. Почему? Потому что иначе вы не сможете выполнить DDL из PL/SQL.

Если задействовано несколько таблиц и/или столбцов, вам придется объединить фиксированные части оператора (например, create table, select, from, order by) с динамическими частями, такими как имена столбцов. Обратите внимание, что между ними вы должны соединить запятые в качестве разделителей. Обратите внимание на использование нескольких одинарных кавычек, так как вам нужно экранировать их (или использовать механизм q-цитирования).

Кроме того, для нескольких столбцов вам, вероятно, придется делать это в цикле, объединяя каждый новый столбец с ранее составленной строкой.

Это (оператор, хранящийся в переменной) выполняется EXECUTE IMMEDIATE. Если правильно написать, то получится. В противном случае произойдет сбой, но он не сообщит вам, почему сбой (именно поэтому я сказал о сложной отладке).

Поэтому вместо выполнения мы обычно отображаем эту строку (используя dbms_output.put_line), чтобы увидеть, как она выглядит, и — используя копирование/вставку — пытаемся выполнить ее.

По сути, он может быть довольно сложным и, как я уже сказал, трудным в обслуживании и отладке.

person Littlefoot    schedule 28.09.2020
comment
Я ценю твое практичное мышление, @Littlefoot, но дело не в этом. Как это будет выглядеть с PL/SQL? - person halloleo; 28.09.2020
comment
Я добавил дополнительную информацию (начинается с Как сделать это динамически?. Посмотрите, пожалуйста. - person Littlefoot; 28.09.2020

arrow_upward
0
arrow_downward

Для цикла FOR вам нужно использовать PL/SQL следующим образом: (*)

declare
  type array_t is table of varchar2(10);
  array array_t := array_t('a', 'b', 'c'); 
  lo_stmt varchar2(2000);
begin
  lo_stmt :=
  'CREATE TABLE t_'||array(i)||'_aggregate ('||
  '    <here the col definitions which are the same for all new tables t_[abc]_aggregate>'||
  ');'||
  ''||
  'INSERT INTO t_'||array(i)||'_aggregate(id, ...)'||
  'SELECT id, SUM(factor*amount)'||
  'FROM t_'||array(i)||
  'WHERE some fixed condition'||
  'GROUP BY id;'||   
  execute immediate lo_stmt;
  end loop;
end;
/

Посмотрите также на этот вопрос SO: данный-оракул-источник">Как использовать Oracle PL/SQL для создания...

(*) @Littlefoot описывает во 2-й части своего ответа ценную информацию об этой программе.

person halloleo    schedule 21.10.2020