-- forall_store_history.sql

DROP TABLE store_history
/
CREATE TABLE store_history(
  store_num NUMBER, 
  emp_count NUMBER,
  dt DATE)
/

DECLARE
  TYPE num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  snums num_type;
  counts num_type;
BEGIN

  SELECT store_number, count(id) 
    BULK COLLECT INTO snums, counts
    FROM employee
   WHERE store_number IS NOT NULL
   GROUP BY store_number;

  FORALL s IN 1..snums.COUNT
    INSERT INTO store_history
      VALUES(snums(s), counts(s), sysdate);
END;
/
