How to reuse the part of SQL code in Oracle

Question

I have 4 tables in my database: t1, t2, t3, t4

And I would like to run 4 insert statements:

insert into t1
with a as (select from another_table1 where condition1)
**XXX**;

insert into t2
with a as (select from another_table2 where condition2)
**XXX**;

insert into t3
with a as (select from another_table3 where condition3)
**XXX**;

insert into t4
with a as (select from another_table4 where condition4)
**XXX**;

where XXX is a large piece of SQL code (with multiple unions of selection from a), but this code is the same in all 4 insert statements

Is there a way to reuse XXX in the insert statements and not to write it 4 times? (if I need to change something I don't want to change it in 4 different places of my SQL code)

[EDIT #1]

Text of XXX is the kind of:

select col1, sum(col10) from a group by col1 union all
select col2, sum(col11) from a group by col2 union all
select col3, sum(col12) from a group by col3 union all
...

a in every insert statement is different because of different with statement, but the text of XXX is the same

Answer 1

If there are no foreign keys between the tables you're inserting into, you can do this in a single insert all statement. (An insert all statement may fail due to foreign key constraint violations, as it doesn't guarantee the order of the tables it will insert into.)

First off, you can write your select statements into a single query such as:

WITH t1 AS (SELECT 'a' col1, 'b' col2, 'c' col3, 10 col10, 11 col11, 12 col12 FROM dual UNION ALL
            SELECT 'aa' col1, 'bb' col2, 'cc' col3, 20 col10, 21 col11, 22 col12 FROM dual UNION ALL
            SELECT 'aaa' col1, 'b' col2, 'cc' col3, 30 col10, 31 col11, 42 col12 FROM dual),
     t2 AS (SELECT 'a' col1, 'b' col2, 'c' col3, 100 col10, 111 col11, 122 col12 FROM dual UNION ALL
            SELECT 'a' col1, 'bb' col2, 'cc' col3, 200 col10, 211 col11, 222 col12 FROM dual UNION ALL
            SELECT 'a' col1, 'b' col2, 'cc' col3, 300 col10, 311 col11, 422 col12 FROM dual),
      a AS (SELECT 't1' table_name, col1, col2, col3, col10, col11, col12 FROM t1
            UNION ALL
            SELECT 't2' table_name, col1, col2, col3, col10, col11, col12 FROM t2),
  dummy AS (SELECT LEVEL id
            FROM   dual
            CONNECT BY LEVEL <= 3)
SELECT table_name,
       CASE WHEN d.id = 1 THEN 'col1'
            WHEN d.id = 2 THEN 'col2'
            WHEN d.id = 3 THEN 'col3'
       END main_col,
       CASE WHEN d.id = 1 THEN col1
            WHEN d.id = 2 THEN col2
            WHEN d.id = 3 THEN col3
       END main_col_vals,
       SUM(CASE WHEN d.id = 1 THEN col10
                WHEN d.id = 2 THEN col11
                WHEN d.id = 3 THEN col12
           END) sum_vals
FROM   a
       CROSS JOIN dummy d
GROUP BY table_name,
         CASE WHEN d.id = 1 THEN 'col1'
              WHEN d.id = 2 THEN 'col2'
              WHEN d.id = 3 THEN 'col3'
         END,
         CASE WHEN d.id = 1 THEN col1
              WHEN d.id = 2 THEN col2
              WHEN d.id = 3 THEN col3
         END
ORDER BY table_name,
         main_col,
         main_col_vals;

This uses the old-style method of pivoting to pivot all the values from all the tables in one go, labeling the rows with the appropriate table_name.

Then it's just a matter of adding that into an insert all statement, e.g.:

insert all
  when table_name = 't1' then into
    another_t1 (cola, val) values (main_col_vals, sum_vals)
  when table_name = 't2' then into
    another_t2 (cola, val) values (main_col_vals, sum_vals)
WITH a AS (SELECT 't1' table_name, col1, col2, col3, col10, col11, col12 FROM t1
           UNION ALL
           SELECT 't2' table_name, col1, col2, col3, col10, col11, col12 FROM t2),
 dummy AS (SELECT LEVEL id
           FROM   dual
           CONNECT BY LEVEL <= 3)
SELECT table_name,
       CASE WHEN d.id = 1 THEN 'col1'
            WHEN d.id = 2 THEN 'col2'
            WHEN d.id = 3 THEN 'col3'
       END main_col,
       CASE WHEN d.id = 1 THEN col1
            WHEN d.id = 2 THEN col2
            WHEN d.id = 3 THEN col3
       END main_col_vals,
       SUM(CASE WHEN d.id = 1 THEN col10
                WHEN d.id = 2 THEN col11
                WHEN d.id = 3 THEN col12
           END) sum_vals
FROM   a
       CROSS JOIN dummy d
GROUP BY table_name,
         CASE WHEN d.id = 1 THEN 'col1'
              WHEN d.id = 2 THEN 'col2'
              WHEN d.id = 3 THEN 'col3'
         END,
         CASE WHEN d.id = 1 THEN col1
              WHEN d.id = 2 THEN col2
              WHEN d.id = 3 THEN col3
         END;

Here's the test case.

Answer 2

if your consideration is not executing it 4 times then you can create temporary table for it. use it and then drop it.

create global temporary table temp_table_transaction on commit preserve rows
as 
<your query goes here>

but otherwise (if you want to reuse it, you can create views). Views executes SQLs on time.

Answer 3

Using a view?

create or replace view xxx as
  select whatever 
  from blabla;

insert into t1
with a as (select ... from another_table1 where condition1)
select * from a
join xxx on ...