using cursor inside cursor to loop through table values in oracle

Question

I have a problem where I am trying to update a value of a table using another table through cursor.

create table A (product varchar2, loc varchar2, qty1 number s_date date);
insert into table A values('123','1',40,sysdate+2);
insert into table A values('123','1',50,sysdate+4);
insert into table A values('124','1',0,sysdate+2);
insert into table A values('124','1',0,sysdate+2);

create table B (p1 varchar2, p2 varchar2,loc2 varchar2, qty2 number, a_date date);
insert into table B values('123','124','1',30,sysdate+1);
insert into table B values('123','124','1',20,sysdate+2);
insert into table B values('123','124','1',50,sysdate+3);

Now the requirement is first sort A and B in ascending order of dates then

for p1 = product and loc2 = loc
    for p2 = product and loc2=loc
   if qty1>=qty2 and qty1>0 then
      qty1 = qty1-qty2 where product = p1
   else 
     move to next row of table A
       next.qty1 = qty1-(qty2-previous.qty1)

then we move on to next row of Table B and check if current qty1 from table A >0 and repeat the same process.I am using oracle 18c.

I am trying to acheive this by using 2 cursors but not really getting it. Is there any other way to implement this? can someone please help?

Answer 1

Here is a code that stores whole a and b table into a collection and makes your calculations with A.qty1 if the conditions are satisfied. I made at the end of the code a dbms_output.put_line of the A table collection because I m not sure I understood right your "pseudo code" where u describe conditions and calculation. Look up the server output and if the calculation done on A table collection are right it is easy to update a table with collection. Also note that this is not a very fast solution we are loading whole two tables into a collection (if your table A or B has million rows this will be executing forever) so try to use where in the select into clause to load only the rows u need for that update.

Sample data:

create table A (product varchar2(10), loc varchar2(10), qty1 number, s_date date);
insert into A values('123','1',40,sysdate+2);
insert into  A values('123','1',50,sysdate+4);

create table B (p1 varchar2(10), p2 varchar2(10),loc2 varchar2(10), qty2 

insert into  B values('123','124','1',30,sysdate+1);
insert into  B values('123','124','1',20,sysdate+2);

Pl/SQL code:

set SERVEROUTPUT ON;
declare
    type t_A_collection is table of a%rowtype index by pls_integer;
    v_A_collection t_A_collection;
    
    type t_B_collection is table of b%rowtype index by pls_integer;
    v_B_collection t_B_collection;
    
    v_previous_qty1 a.qty1%type;
begin
    select * bulk collect into v_A_collection from a order by s_date asc;
    select * bulk collect into v_B_collection from b order by a_date asc;
    
    for i in v_B_collection.first..v_B_collection.last loop
        --for j in v_A_collection.first..v_A_collection.last loop
      
            if v_A_collection.exists(i-1) then
                if v_B_collection(i).qty2>v_A_collection(i-1).qty1 then
                   v_A_collection(i).qty1:=v_A_collection(i).qty1-(v_B_collection(i).qty2-v_A_collection(i-1).qty1);
                    v_A_collection(i-1).qty1:=0;
                end if;
            else
                if v_B_collection(i).p1=v_A_collection(i).product
                and  v_B_collection(i).loc2=v_A_collection(i).loc then
                    v_A_collection(i).qty1:= abs(v_A_collection(i).qty1-v_B_collection(i).qty2);
                elsif v_B_collection(i).p2=v_A_collection(i).product then
                    v_A_collection(i).qty1:= abs(v_A_collection(i).qty1-v_B_collection(i).qty2);
                end if;
            end if;       
    end loop;
    for i in v_A_collection.first..v_A_collection.last loop
        dbms_output.put_line(v_A_collection(i).product||' '||v_A_collection(i).loc||' '||v_A_collection(i).qty1||' '||v_A_collection(i).s_date);
    end loop;
end;

Server output of collection for table A:

PRODUCT|LOC |QTY1 |S_DATE
-----------------------------
123     1    0     30.04.2022
123     1    40    02.05.2022