FOR loop in Oracle SQL or Apply SQL to multiple Oracle tables

Question

My SQL is a bit rusty, so I don't know whether the following is even possible:

I have multiple tables t_a, t_b, t_c with the same column layout and I want to apply the same operation to them, namely output some aggregation into another table. For a table t_x this would look like this:

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;

I now want to execute something like a FOR loop around this:

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

Is this possible in SQL? Or would I need to build a wrapper in another language for this?

Answer 1

For the FOR loop you need to use PL/SQL like this:(*)

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;
/

Look also at this SO question: How to use Oracle PL/SQL to create...

(*) @Littlefoot describes in the 2nd part of his answer valuable background to this program.