create table test1 
  (
    id number primary key,
    value0 number
  );

create table test2 
  (
    id number,
    value1 number,
    value2 number,
    fk_test1 number references test1(id)
  );

insert all 

  INTO test1 values (1, 500)
  INTO test1 values (2, 600)
  INTO test1 values (3, 700)
  INTO test1 values (4, 800)
  INTO test1 values (5, 900)
  INTO test2 values (1, 400, 70, 1)
  INTO test2 values (2, 400, 70, 2)
  INTO test2 values (3, 400, 70, 5)
  INTO test2 values (4, 400, 70, 5)
  INTO test2 values (5, 400, 70, 5)
select * from dual;

-- TESTPAC specification
CREATE OR REPLACE PACKAGE AFESD2.testPac AS

  TYPE REC_COMtest IS RECORD
    (
       ID      NUMBER(4),
      value0  NUMBER(6),
      value1         NUMBER(6),
      value2              NUMBER(6)
    );

  -- Define cursor and table(array) of to hold COM CHARGE data
  TYPE CUR_COMtest IS REF CURSOR RETURN REC_COMtest;
  TYPE ARY_COMtest IS TABLE OF REC_COMtest;

PROCEDURE P_GET_DATA_TEST
  ( 
    I_NUM     IN NUMBER, 
    MyCur   OUT CUR_COMtest, 
    I_TOTAL_ROWS OUT NUMBER  
  );
end testpac;

CREATE OR REPLACE PACKAGE BODY AFESD2.testPac AS

  PROCEDURE P_GET_DATA_TEST 
  ( 
    I_NUM     IN NUMBER, 
    MyCur   OUT CUR_COMtest, 
    I_TOTAL_ROWS OUT NUMBER  
  )
  AS
    AR_MY_ARRAY ARY_COMtest;
    I_total_sum number;
  BEGIN
  
   OPEN MyCur FOR 
    SELECT  test1.id, value0, value1, value2 
    FROM  test1, test2
    WHERE test1.id = test2.FK_TEST1 ;

   I_total_rows:=0;

   --Op1: Normal fetch and use of ar_my_array to calculate a total value
   FETCH MyCur BULK COLLECT INTO ar_My_Array;
   FOR I IN 1..ar_My_Array.COUNT LOOP
    I_total_rows := I_total_rows+ ( ar_My_Array(I).value0 + ar_My_Array(I).value1 * ar_My_Array(I).value2);
   END LOOP;

   FOR I IN 1..ar_My_Array.COUNT LOOP
     INSERT INTO test1 (id, VALUE0) 
          SELECT 99+I , I_total_rows
     FROM DUAL; 
   END LOOP;
   
   I_total_sum :=0;
  
   --Op2: resue the array to calculate an aggregate value I_total_sum and insert it
   FOR I IN 1..ar_My_Array.COUNT LOOP
       I_total_sum := I_total_sum + ar_My_Array(I).value2;
   END LOOP;

   INSERT INTO test1 (id, VALUE0) 
          SELECT 77 , I_total_sum
   FROM DUAL; 
 
   --Op3: My main question, if there is a way to reuse the same array and get the needed resutl instead of running another query
--   CLOSE MyCur;    
--   OPEN MyCur FOR 
--    SELECT  FK_TEST1, sum(value0), sum(value1), sum(value2) 
--    FROM  test1, test2
--    WHERE test1.id = test2.FK_TEST1 
--    group by FK_TEST1;
--
--   FETCH MyCur BULK COLLECT INTO ar_My_Array;
--   FOR I IN 1..ar_My_Array.COUNT LOOP
--    INSERT INTO test1 (id, VALUE0) 
--          SELECT 1000+I , ar_My_Array(I).value2
--     FROM DUAL; 
--   END LOOP;

  END;

end testPac;