Multi Table Loop

by JP Vijaykumar

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