dokuwiki

 CREATE OR REPLACE TYPE "HR"."EMP_LIST" 
 AS TABLE OF VARCHAR2(100)

CREATE TABLE DEP_EMP as select department_name from departments;
alter table dep_emp add (emp_names emp_list)
nested table emp_names store as nested_tab return as value;

PROCEDURE fill_dep_emp
   IS
   TYPE emp_table_type IS TABLE OF emp_list
      INDEX BY varchar2(100);

   t_emp    emp_table_type;
   v_index  varchar2(100);

BEGIN

 for r_ed in (SELECT FIRST_NAME||' '||LAST_NAME e_name, DEPARTMENT_NAME
                     FROM DEPARTMENTS , EMPLOYEES
                    WHERE ( (DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID) ))
 loop
   if (not t_emp.exists(r_ed.department_name)) then
     t_emp(r_ed.department_name) := emp_list();
   end if;
   t_emp(r_ed.department_name).extend;
   t_emp(r_ed.department_name)(t_emp(r_ed.department_name).last) := r_ed.e_name;
 end loop;

v_index := t_emp.first;
loop
  exit when v_index is null;
  update dep_emp set emp_names = t_emp(v_index)
  where department_name = v_index;
  v_index := t_emp.next(v_index);
end loop;


END;