Live ORACLE

DROP TABLE emp_by_dept CASCADE CONSTRAINTS PURGE;


CREATE TABLE emp_by_dept
 AS
 SELECT employee_id,
        department_id
 FROM   employees
 WHERE  1 = 0;



DECLARE
 TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
 deptnums  DEPT_TAB;
BEGIN
 SELECT department_id
 BULK COLLECT INTO deptnums
 FROM   departments;
 FORALL i IN 1..deptnums.COUNT
   INSERT INTO emp_by_dept
   SELECT employee_id,
          department_id
   FROM   employees
   WHERE  department_id = Deptnums(i);
 FOR i IN 1.. deptnums.COUNT LOOP
   -- Count how many rows were inserted for each department; that is,
   -- how many employees are in each department.
   dbms_output.Put_line('Dept '
                        ||Deptnums(i)
                        ||': inserted '
                        ||SQL%Bulk_rowcount(i)
                        ||' records');
 END LOOP;
 dbms_output.Put_line('Total records inserted: '
                      ||SQL%ROWCOUNT);
END;
/  

Dept 10: inserted 1 records
Dept 20: inserted 2 records
Dept 30: inserted 6 records
Dept 40: inserted 1 records
Dept 50: inserted 45 records
Dept 60: inserted 5 records
Dept 70: inserted 1 records
Dept 80: inserted 34 records
Dept 90: inserted 3 records
Dept 100: inserted 6 records
Dept 110: inserted 2 records
Dept 120: inserted 0 records
Dept 130: inserted 0 records
Dept 140: inserted 0 records
Dept 150: inserted 0 records
Dept 160: inserted 0 records
Dept 170: inserted 0 records
Dept 180: inserted 0 records
Dept 190: inserted 0 records
Dept 200: inserted 0 records
Dept 210: inserted 0 records
Dept 220: inserted 0 records
Dept 230: inserted 0 records
Dept 240: inserted 0 records
Dept 250: inserted 0 records
Dept 260: inserted 0 records
Dept 270: inserted 0 records
Total records inserted: 106

PL/SQL procedure successfully completed.

Counting Rows Affected by FORALL With %BULK_ROWCOUNT