-- store_history1.sql

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

DECLARE
   s NUMBER := 1;
   ecount NUMBER;
BEGIN
   LOOP 
      EXIT WHEN s > 7;
      SELECT count(id) 
        INTO ecount
        FROM employee
       WHERE store_number = s;
      INSERT INTO store_history
      VALUES(s, ecount, sysdate);
      s := s + 1;
   END LOOP;
END;
/
