SQL & PL/SQL

All,

What we do is ..
1) We populate tab_emp_sal_comm with Bulk collect and iterate through it in a loop.
2) Check whether the Passed deptno is available in tab_emp_sal_comm collection.
3) If its available populate one more collection called tab_emp_sal_comm_1 and push only matching records into it.
4) From the matched collection, we want to populate that to the package global collection table which is again of the same type.
5) By default for every new call the old values are overwritten, but i want to append the global variable upon every call and finally make a bulk update to corresponding table(s).
6) l_deptno will be a parameter and the values will change on every call to this procedure in real time code.

For the sake of easiness given a simulated example from EMP table. Aim is to append the Global table in the package. Because on every call the previously loaded values are overwritten. I want them to be available and the further calls should only append tthe values as next set of rows instead of over writing.

How do we acheive it, please discuss.

CREATE OR REPLACE PACKAGE employees_pkg
IS
  type rec_sal_comm is record(esal emp.sal%type, ecomm emp.comm%type,edeptno emp.deptno%type);
  type at_emp_sal_comm is table of rec_sal_comm index by pls_integer;
  pkg_tab_emp  at_emp_sal_comm;
  pkg_tab_emp_1 at_emp_sal_comm;
END;
/
-- Block Starts 
 declare
  -- Local variables here
  type emp_sal_comm is record(
    esal    emp.sal%type,
    ecomm   emp.comm%type,
    edeptno emp.deptno%type);
  type at_emp_sal_comm is table of emp_sal_comm index by pls_integer;
  tab_emp_sal_comm  at_emp_sal_comm;
  tab_emp_sal_comm1 at_emp_sal_comm;
  l_deptno          dept.deptno%type := 30;
  l_comm            number(7, 2) := 0;
  M_CNTR            NUMBER(7, 2) := 0;
begin

  select sal, comm, deptno bulk collect into tab_emp_sal_comm from emp;
  for indx in 1 .. tab_emp_sal_comm.count loop
    if tab_emp_sal_comm(indx).edeptno = l_deptno then
      tab_emp_sal_comm1(indx).ecomm := tab_emp_sal_comm(indx).ecomm * 0.5;
      tab_emp_sal_comm1(indx).esal  := tab_emp_sal_comm(indx).esal * 0.75;
    end if;
  end loop;
  dbms_output.put_line(tab_emp_sal_comm1.count);
  dbms_output.put_line('**');

  m_cntr := tab_emp_sal_comm1.FIRST;
  loop
    exit when M_CNTR is null;
--    dbms_output.put_line(M_CNTR || ' ** ' ||nvl(tab_emp_sal_comm1(M_CNTR).ecomm, 0));
    employees_pkg.pkg_tab_emp(m_cntr).ecomm := tab_emp_sal_comm1(M_CNTR)
                                                    .ecomm;
    employees_pkg.pkg_tab_emp(m_cntr).edeptno := tab_emp_sal_comm1(M_CNTR)
                                                      .edeptno;
    employees_pkg.pkg_tab_emp(m_cntr).esal := tab_emp_sal_comm1(M_CNTR).esal;
    m_cntr := tab_emp_sal_comm1.next(m_cntr);
---  other computations and calculations made based on the matched records
  end loop;

  employees_pkg.pkg_tab_emp_1 := employees_pkg.pkg_tab_emp;
 -- dbms_output.put_line('**');
--  dbms_output.put_line(employees_pkg.pkg_tab_emp_1.count);
end;
-- Code will work in a Scott Schema with Emp Table.