plsql cursor iterating problem

Question

hi everyone, i use oracle demo schema scott to do some plsql test ( the data in that schema are never changed ). i wrote the following program to get the employee number of each department. the problem is, there is just 4 departments but my program output 5 row. i can't find out the reason, anyone can help? great thanks.

declare
    cursor employees(department_id number) is
    select count(*) howmany
    from scott.emp
    where deptno=department_id;

    employees_per_dept employees%rowtype;


    cursor departments is
    select *
    from scott.dept;

    a_department departments%rowtype;

begin
    dbms_output.put_line('-----------------------------------');
    open departments;
    loop
     exit when departments%notfound;

     fetch departments into a_department;

     open employees(a_department.deptno);
     fetch employees into employees_per_dept;
     dbms_output.put_line(employees_per_dept.howmany);
     close employees;


    end loop;
    close departments;
    dbms_output.put_line('-----------------------------------');
end;

Answer 1

If you output the deptno in the dbms_output you'll see the reason.

You need to switch these two lines:

fetch departments into a_department;
exit when departments%notfound;

%NOTFOUND is meaningless before the initial FETCH; your code was counting the emps in the last dept twice.