Oefening3Procedure

CREATE OR REPLACE
PROCEDURE ProductenPerWareHouse AS
--First cursor: get all warehouses
CURSOR warehousec IS SELECT * FROM OE.Warehouses;
--Needed in next cursor
warehouserow warehousec%ROWTYPE;
--Second cursor: get all distinct ordered products for a specific warehouse
CURSOR productc (param warehouserow%TYPE) IS
SELECT DISTINCT o.product_id, pd.product_name FROM OE.Order_Items o
INNER JOIN OE.Inventories i ON i.product_id = o.product_id
INNER JOIN OE.Product_information pd ON pd.product_id = o.product_id
WHERE i.warehouse_id = param.warehouse_id
ORDER BY o.product_id ASC;

productrow productc%ROWTYPE;
BEGIN
FOR warehouserow IN warehousec
LOOP
  DBMS_OUTPUT.PUT_LINE('Warehouse ' || warehouserow.warehouse_id || ', '
        || warehouserow.warehouse_name);
  FOR productrow IN productc(warehouserow)
  LOOP
  DBMS_OUTPUT.put_line(productrow.product_id || ' '
        || productrow.product_name);
  END LOOP;
END LOOP;
END;
/
/*---- Testprogramma ---*/
BEGIN
oe.productenperwarehouse();
END;