GemiddeldWinstPerCategorie

CREATE OR REPLACE PROCEDURE GEMIDDELDEWINSTPERCATEGORIE AS
-- Cursor to loop through all categories
CURSOR catcur IS SELECT cat.category_id, cat.category_name, AVG(pi.list_price-pi.min_price)/AVG(pi.min_price) AS average
FROM product_information pi
  INNER JOIN OE.order_items o ON o.product_id = pi.product_id
  INNER JOIN OE.product_information pi ON pi.product_id = o.product_id
  INNER JOIN OE.categories cat ON cat.category_id = pi.category_id
  GROUP BY cat.category_id, cat.category_name
  ORDER BY cat.category_name;
BEGIN
  BEGIN
  FOR categori IN catcur LOOP
    DBMS_OUTPUT.put_line('category '||categori.category_name||': '||categori.average);
  END LOOP;
  EXCEPTION WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.put_line('Category has badly priced item');
  END;
END GEMIDDELDEWINSTPERCATEGORIE;
/*----Testapplicatie -----*/
BEGIN
  oe.gemiddeldewinstpercategorie();
END;