I have a procedure that fills a table after some calculation.
I want to run this procedure several times in a loop and add the new calculated columns to another table.
create table first_pd as
select 1 l_id, 64 blnc from dual
union all
select 2 l_id, 23 blnc from dual
union all
select 3 l_id, 57 blnc from dual
union all
select 4 l_id, 43 blnc from dual
union all
select 5 l_id, 645 blnc from dual
union all
select 6 l_id, 55 blnc from dual;
create table s_pd
(
l_id number,
blnc number
);
create procedure ins_pd(forje number)
begin
insert into s_pd
select l_id, blnc * forje /*For simplicity, I changed the calculations to a simple multiplication.*/
from first_pd;
commit;
end ;
so I have this table
and I want after 5 iteration, I get the following table:
I need a script like this:
create procedure fin_pd(itrn number)
n integer := 1;
begin
ins_pd(n);
/* storing the data of pd like follow:
create table pd_final as
select * from pd;
*/
while n <= itrn loop
n := n + 1;
ins_pd(n);
/*
needed statement
joining pd_final with the output of procedure ins_pd and adding new column to pd_final:
create table pd_final as
select pd_final.id, pd_final.blnc, s_pd.blnc as blnc_n
from pd_final inner join s_pd on pd_final.id = s_pd.id;
*/
end loop;
end;
I tried to use a cursor, but it didn't work.