jljl

CREATE OR REPLACE PROCEDURE P3_6_LAGER_DAN_GEMIDDELD AS
  V_GEM_IN_STOCK NUMBER;
 
  CURSOR C_PRODUCTS(P_AVG IN NUMBER) IS
    SELECT WAREHOUSE_ID, PRODUCT_ID, (SELECT NAME FROM S_PRODUCT WHERE ID=PRODUCT_ID) NAME
    FROM S_INVENTORY
    WHERE AMOUNT_IN_STOCK < P_AVG
    ORDER BY WAREHOUSE_ID, PRODUCT_ID;
 
BEGIN
  SELECT AVG(MAX_IN_STOCK)
  INTO V_GEM_IN_STOCK
  FROM S_INVENTORY;
 
  FOR R_PRODUCT IN C_PRODUCTS(V_GEM_IN_STOCK) LOOP
    DBMS_OUTPUT.PUT_LINE(R_PRODUCT.WAREHOUSE_ID || ' ' || R_PRODUCT.PRODUCT_ID || ' ' || R_PRODUCT.NAME);
  END LOOP;
 
END P3_6_LAGER_DAN_GEMIDDELD;