PL/SQL how can I loop through a table and directing the cursor-CodePudding

my code is basically here, the work to do is to use PL/SQL to get the total sales from each territory .

my way is to create cursor to loop over the Territory to get the sum. I try to use inner and outer loop but the result is looping over same Territory without moving to the next one. How can I fix it?

DECLARE
    TERRITORY_ID NUMBER(5);
    TERRITORY_DESC VARCHAR(50);
    TOTAL_SALES NUMBER(10);
    NUMBER_SOLD NUMBER(5);
    UNIT_PRICE NUMBER(10);
    SUBTOTAL NUMBER(10);
    TOTAL NUMBER(10):= 0;
    
    CURSOR CURSOR_2 IS 
        SELECT T.TERRITORYID, T.TERRITORYDESCRIPTION, OD.QUANTITY,OD.UNITPRICE
        FROM TERRITORIES T, ORDERS O, ORDERDETAILS OD
        WHERE T.TERRITORYID = O.TERRITORYID
        AND O.ORDERID = OD.ORDERID;

BEGIN
    DBMS_OUTPUT.put_line('TERRITORY_ID' || '|' ||'TERRITORY_DESC' || '|' ||'TOTAL');
    FOR T IN (SELECT UNIQUE T.TERRITORYID FROM TERRITORIES T) LOOP
    
    TOTAL := 0;
    OPEN CURSOR_2;
        LOOP 
            FETCH CURSOR_2 INTO TERRITORY_ID, TERRITORY_DESC, NUMBER_SOLD, UNIT_PRICE;
            EXIT WHEN CURSOR_2%NOTFOUND;
            SUBTOTAL := NUMBER_SOLD * UNIT_PRICE;
            TOTAL := TOTAL   SUBTOTAL;
            
        END LOOP;
    DBMS_OUTPUT.put_line(TERRITORY_ID || '|' ||TERRITORY_DESC || '|' ||TOTAL);
    
    CLOSE CURSOR_2;
    END LOOP;
END;

CodePudding user response：

You're having issues because you didn't correlate cursor_2 to territory fetched by the first cursor FOR loop.

If you rewrite that code (and simplify it a little bit), you'd get

DECLARE
    total NUMBER(10):= 0;
BEGIN
    FOR cur_t IN (SELECT UNIQUE T.territoryid, T.territorydescription 
                  FROM territories T) 
    LOOP
    
      total := 0;
      FOR cursor_2 IN (SELECT od.quantity,od.unitprice
                       FROM orders O JOIN orderdetails od ON O.orderid = od.orderid
                       WHERE O.territoryid = cur_t.territoryid)
      LOOP 
        total := total   (cursor_2.quantity * cursor_2.unitprice);            
      END LOOP;
      dbms_output.put_line(cur_t.territory_id || '|' || cur_t.territory_desc || '|' ||total);
    END LOOP;
END;

I can't test it as I don't have your tables nor data, though.

On the other hand, do you really need to use nested loops? How about only one loop?

begin
  for cur_t in (select t.territoryid, 
                       t.territorydescription,
                       sum(od.quantity * od.unitprice) as total
                from territories t join orders o on o.territoryid = t.territoryid
                                   join orderdetails od on od.orderid = o.orderid
                group by t.territoryid, 
                         t.territorydescription)
  loop
    dbms_output.put_line(cur_t.territory_id || '|' || cur_t.territory_desc || '|' || cur_t.total);
  end loop;
end;

Finally, you really don't need PL/SQL at all as cursor's query itself returns data you need:

            select t.territoryid, 
                   t.territorydescription,
                   sum(od.quantity * od.unitprice) as total
            from territories t join orders o on o.territoryid = t.territoryid
                               join orderdetails od on od.orderid = o.orderid
            group by t.territoryid, 
                     t.territorydescription;

CodePudding user response：

Don't use a cursor or loops (or even PL/SQL), just perform the aggregation in SQL:

SELECT T.TERRITORYID,
       MAX(T.TERRITORYDESCRIPTION) AS TERRITORYDESCRIPTION,
       SUM(OD.QUANTITY * OD.UNITPRICE)
FROM   TERRITORIES T
       INNER JOIN ORDERS O
       ON T.TERRITORYID = O.TERRITORYID
       INNER JOIN ORDERDETAILS OD
       ON O.ORDERID = OD.ORDERID
GROUP BY
       -- Just group by the primary key
       T.TERRITORYID;