This article examines how to loop through multiple tables to process the data.

drop table temp_jp10;
create table temp_jp10(col1 number,col2 varchar2(20));
insert into temp_jp10 values(2,'RAMA');
insert into temp_jp10 values(4,'SITA');
insert into temp_jp10 values(6,'HANUMAN');
 
drop table temp_jp01;
create table temp_jp01(col1 number,col2 varchar2(20));
insert into temp_jp01 values(1,'RADHA');
insert into temp_jp01 values(2,'RADHA');
insert into temp_jp01 values(3,'RADHA');
insert into temp_jp01 values(4,'RADHA');
insert into temp_jp01 values(5,'RADHA');
 
drop table temp_jp02;
create table temp_jp02(col1 number,col2 varchar2(20));
insert into temp_jp02 values(1,'KRISHNA');
insert into temp_jp02 values(2,'KRISHNA');
insert into temp_jp02 values(3,'KRISHNA');
insert into temp_jp02 values(4,'KRISHNA');
insert into temp_jp02 values(5,'KRISHNA');
 
drop table temp_jp03;
create table temp_jp03(col1 number,col2 varchar2(20));
insert into temp_jp03 values(1,'SATYA');
insert into temp_jp03 values(2,'SATYA');
insert into temp_jp03 values(3,'SATYA');
insert into temp_jp03 values(4,'SATYA');
insert into temp_jp03 values(5,'SATYA');
commit;
 
 
select * from temp_jp10;
 
       COL1 COL2
---------- --------------------
         2 RAMA
         4 SITA
         6 HANUMAN
 
select * from temp_jp01;
 
       COL1 COL2
---------- --------------------
         1 RADHA
         2 RADHA
         3 RADHA
         4 RADHA
         5 RADHA
 
select * from temp_jp02;
 
       COL1 COL2
---------- ------------------
         1 KRISHNA
         2 KRISHNA
         3 KRISHNA
         4 KRISHNA
         5 KRISHNA
 
select * from temp_jp03;
 
       CO1 COL2
---------- -------------
         1 SATYA
         2 SATYA
         3 SATYA
         4 SATYA
         5 SATYA

Now I want to loop through the records of temp_jp10 table and if I find any matching records in the tables temp_jp01, temp_jp02,temp_jp03, then update col2 and if there are no matching records then insert the record.

I created the following procedure: I am specifically not using the merge command to test my multi table loop script and compare the difference.

declare
v_num number(10);
begin
for c1 in (select * from temp_jp10) loop
 
execute immediate 'select count(*) from temp_jp01 where col1 = '||c1.col1 into v_num;
if (v_num > 0) then
update temp_jp01 set col2 = c1.col2 where col1 = c1.col1;
else
insert into temp_jp01 values(c1.col1,c1.col2);
end if;
 
execute immediate 'select count(*) from temp_jp02 where col1 = '||c1.col1 into v_num;
if (v_num > 0) then
update temp_jp02 set col2 = c1.col2 where col1 = c1.col1;
else
insert into temp_jp02 values(c1.col1,c1.col2);
end if;
 
execute immediate 'select count(*) from temp_jp03 where col1 = '||c1.col1 into v_num;
if (v_num > 0) then
update temp_jp03 set col2 = c1.col2 where col1 = c1.col1;
else
insert into temp_jp03 values(c1.col1,c1.col2);
end if;
 
end loop;
commit;
end;
 
PL/SQL procedure successfully completed
 
select * from temp_jp01;
 
      COL1 COL2
---------- --------------------
         1 RADHA
         2 RAMA
         3 RADHA
         4 SITA
         5 RADHA
         6 HANUMAN
 
 
select * from temp_jp02;
 
      COL1 COL2
---------- --------------------
         1 KRISHNA
         2 RAMA
         3 KRISHNA
         4 SITA
         5 KRISHNA
         6 HANUMAN
 
 
select * from temp_jp03;
 
      COL1 COL2
---------- --------------------
         1 SATYA
         2 RAMA
         3 SATYA
         4 SITA
         5 SATYA
         6 HANUMAN

What if I had 100 tables to loop through to find matching records with my temp_jp10 table and update, if not insert. Imagine how my procedure will look.

select * from temp_jp10;
 
      COL1 COL2
---------- --------------------
         2 RAMA
         4 SITA
         6 HANUMAN
 
select * from temp_jp01;
 
      COL1 COL2
---------- --------------------
         1 RADHA
         2 RADHA
         3 RADHA
         4 RADHA
         5 RADHA
 
select * from temp_jp02;
 
      COL1 COL2
---------- --------------------
         1 KRISHNA
         2 KRISHNA
         3 KRISHNA
         4 KRISHNA
         5 KRISHNA
 
select * from temp_jp03;
 
      COL1 COL2
---------- --------------------
         1 SATYA
         2 SATYA
         3 SATYA
         4 SATYA
         5 SATYA

I want to create script to loop through all these tables in one go:

The following script does the required trick. I find the tables from a list of given tables and create a view inside my procedure and then perform the necessary dml operation on my view, which in turn updates the base tables.

declare
v_num number(10);
begin
for c1 in (select * from gzbgqt.temp_jp10) loop
 
for c2 in (select table_name from all_tables
           where owner= 'GZBGQT' 
                   and table_name in ('TEMP_JP01','TEMP_JP02','TEMP_JP03')) loop
 
execute immediate 
'create or replace view gzbgqt.temp_jp_vw as select * from gzbgqt.'||c2.table_name;
 
execute immediate 
'select count(*) from gzbgqt.temp_jp_vw where col1 ='||c1.col1 into v_num;
 
if (v_num > 0) then
update gzbgqt.temp_jp_vw set col2 = c1.col2  where col1 = c1.col1;
else
insert into gzbgqt.temp_jp_vw values(c1.col1,c1.col2);
end if;
 
end loop;
 
end loop;
commit;
end;

Once my multi table loop is executed the tables temp_jp01, temp_jp02 and temp_jp03 are updated as expected.

select * from temp_jp01;
 
      COL1 COL2
---------- --------------------
         1 RADHA
         2 RAMA
         3 RADHA
         4 SITA
         5 RADHA
         6 HANUMAN
 
 
select * from temp_jp02;
 
      COL1 COL2
---------- --------------------
         1 KRISHNA
         2 RAMA
         3 KRISHNA
         4 SITA
         5 KRISHNA
         6 HANUMAN
 
 
select * from temp_jp03;
 
      COL1 COL2
---------- --------------------
         1 SATYA
         2 RAMA
         3 SATYA
         4 SITA
         5 SATYA
         6 HANUMAN

Instead of looping through each and every table in the list, select the table from all_tables view and dynamically create a view based on the table and update the base table  through the view.

This simplifies my coding to write the if then logic for all of my tables.

HAPPY SCRIPTING