-- assoc_arr.sql
VAR total NUMBER

DECLARE
  TYPE emptab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

  emp_counts emptab;

  FUNCTION get_emp_counts RETURN emptab AS
    myemps emptab;
    cur_val NUMBER;
  BEGIN
    FOR s IN 1..7 LOOP
      SELECT COUNT(*)
        INTO cur_val
        FROM employee
       WHERE store_number = s;
      myemps(s) := cur_val;
    END LOOP;

    RETURN myemps;
  END get_emp_counts;

BEGIN

 :total := 0;
 emp_counts := get_emp_counts;
 FOR x IN 1..7 LOOP
   :total := :total + emp_counts(x);
 END LOOP;

END;
/

PRINT total
