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:
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;
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?
Advertisement
Answer
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.