oracle PL/SQL如何循环遍历一个表并定向游标

z5btuh9x 于 2022-11-03 发布在 Oracle

关注(0)|答案(2)|浏览(208)

我的代码基本上在这里，要做的工作是使用PL/SQL获得每个地区的总销售额。
我的方法是创建一个光标在区域上循环以得到总和。2我尝试使用内部和外部循环，但结果是在同一个区域上循环而不移动到下一个。3我该如何解决这个问题？

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;

oracle

来源：https://stackoverflow.com/questions/74064175/pl-sql-how-can-i-loop-through-a-table-and-directing-the-cursor

2条答案

按热度按时间

2izufjch1#

您遇到问题是因为您没有将cursor_2与第一个游标FOR循环获取的区域关联起来。
如果重写代码（并稍微简化），您将得到

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;

我不能测试它，因为我没有你的表和数据，虽然。
另一方面，你真的需要使用嵌套循环吗？只使用一个循环怎么样？

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;

最后，您实际上根本不需要PL/SQL，因为游标的查询本身会返回您需要的数据：

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;

赞(0）回复(0）举报 2022-11-03

7kjnsjlb2#

不要使用游标或循环（甚至PL/SQL），只需在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;

赞(0）回复(0）举报 2022-11-03

我来回答

oracle PL/SQL如何循环遍历一个表并定向游标

2条答案

相关问题

热门标签

最新问答