Multi Table Loop

May 7, 2008

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers