-- loop_stores.sql

DECLARE
  snum NUMBER := 1;	-- Initialize store_number to 1
  maxsnum NUMBER;
  TYPE id_tab IS TABLE OF store.manager_id%TYPE; -- Definition
  mgr id_tab;	-- Table variable declaration

  PROCEDURE calculate_bonuses (manager id_tab) IS
    rev NUMBER(9,2);
  BEGIN
    FOR s IN 1 .. manager.COUNT LOOP
      SELECT SUM(oi.quantity*p.price) INTO rev
        FROM order_item oi INNER JOIN product p USING (product_id)
                           INNER JOIN order_header oh 
                             USING (invoice_number)
       WHERE oh.store_number = s;
      UPDATE employee SET pay_amount = pay_amount + (rev * .05)
       WHERE id = manager(s);
    END LOOP;
  END;

BEGIN
  mgr := id_tab();	-- Table constructor
  SELECT max(store_number) INTO maxsnum FROM store;

  -- Load table with manager_ids:
  WHILE snum <= maxsnum
  LOOP
    mgr.EXTEND;	-- Add an element to the table
    SELECT manager_id INTO mgr(snum)
      FROM store
     WHERE store_number = snum;
    snum := snum + 1;
  END LOOP;

  calculate_bonuses(mgr);

END;
/
