-- bulk_forall.sql

DROP TABLE testoutput
/

CREATE TABLE testoutput ( output VARCHAR2(3000) )
/

DECLARE
  TYPE store_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  stores store_type;

  TYPE id_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  updated_emps id_type;

  TYPE amount_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  new_pay amount_type;
BEGIN
  SELECT store_number
    BULK COLLECT INTO stores
    FROM store
   WHERE manager_id IS NOT NULL;

  FORALL s IN INDICES OF stores 
       UPDATE employee
          SET pay_amount = pay_amount * (1 + 
                (SELECT SUM(quantity * price) * .00001
                   FROM order_item oi JOIN order_header oh
                                     USING (invoice_number)
                                      JOIN product p
                                     USING (product_id)
                  WHERE store_number = stores(s)))
        WHERE store_number = stores(s)
    RETURNING id, pay_amount 
         BULK COLLECT INTO updated_emps, new_pay;

 FORALL i IN INDICES OF updated_emps
   INSERT INTO testoutput (output)
     VALUES ( updated_emps(i) || ' now makes $' || new_pay(i));
END;
/

SELECT output FROM testoutput
/


