azeae

CREATE OR REPLACE PROCEDURE P3_1_HHOGSTE_VOORRAADWAARDE AS 
  CURSOR C_WAREHOUSE IS
    SELECT WAREHOUSE_ID, SUM(I.AMOUNT_IN_STOCK * P.SUGGESTED_WHLSL_PRICE) Waarde
    FROM S_INVENTORY I, S_PRODUCT P
    WHERE I.PRODUCT_ID = P.ID
    GROUP BY WAREHOUSE_ID
    ORDER BY WAARDE DESC;
 
    R_WAREHOUSE C_WAREHOUSE%ROWTYPE;
BEGIN
  OPEN C_WAREHOUSE;
    FOR I IN 1..3 LOOP
      FETCH C_WAREHOUSE INTO R_WAREHOUSE;
      IF C_WAREHOUSE%NOTFOUND THEN
        EXIT;
      END IF;
      DBMS_OUTPUT.PUT_LINE(R_WAREHOUSE.WAREHOUSE_ID || CHR(9) || R_WAREHOUSE.Waarde);
    END LOOP;
  CLOSE C_WAREHOUSE;
END P3_1_HHOGSTE_VOORRAADWAARDE;