plsql cursor iterating problem

Question

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;

Jeffrey Kemp · Accepted Answer · 2009-07-24 12:12:44Z

4

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.

answered Jul 24, 2009 at 12:12

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eugene Loy · Accepted Answer · 2013-11-27 06:59:26Z

0

declare

cursor cl(ccode varchar2) is

Select * from employees where department_id=ccode;

z cl%rowtype;

cnt number:=0;

begin

    Open cl('90');

    fetch cl into Z;

    while (cl%found) loop 

    dbms_output.put_line ( 'nsme is  '  || z.last_name);

        fetch cl into Z;

        cnt := cnt +1;

        end  loop;

         dbms_output.put_line (cnt);

    close cl;

   end;

edited Nov 27, 2013 at 6:59

Eugene Loy

12.5k8 gold badges57 silver badges79 bronze badges

answered Nov 27, 2013 at 6:34

Ashish sinha

1481 gold badge2 silver badges9 bronze badges

Collectives™ on Stack Overflow

plsql cursor iterating problem

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related