jljl

CREATE OR REPLACE PROCEDURE P3_9_VOORRAAD_PER_PRODUCT AS 
  CURSOR C_PRODUCTS IS
    SELECT ID, NAME, SUGGESTED_WHLSL_PRICE
    FROM S_PRODUCT
    WHERE SUGGESTED_WHLSL_PRICE > 100;
 
  CURSOR C_WAREHOUSES(P_PRODUCT_ID IN S_PRODUCT.ID%TYPE) IS
    SELECT WAREHOUSE_ID, AMOUNT_IN_STOCK, (SELECT CITY 
                                           FROM S_WAREHOUSE
                                           WHERE ID=WAREHOUSE_ID) CITY
    FROM S_INVENTORY
    WHERE PRODUCT_ID = P_PRODUCT_ID;
BEGIN
  FOR R_PRODUCT IN C_PRODUCTS LOOP
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE(R_PRODUCT.ID || ' ' || R_PRODUCT.NAME ||' ' || R_PRODUCT.SUGGESTED_WHLSL_PRICE);
    DBMS_OUTPUT.PUT_LINE('--------------------------');
    FOR R_WAREHOUSE IN C_WAREHOUSES(R_PRODUCT.ID) LOOP
      DBMS_OUTPUT.PUT_LINE(R_WAREHOUSE. WAREHOUSE_ID || ' ' || R_WAREHOUSE.CITY || ' ' || R_WAREHOUSE.AMOUNT_IN_STOCK);
    END LOOP;
  END LOOP;
END P3_9_VOORRAAD_PER_PRODUCT;