PlSql stuck in infinite loop

Question

i have a table with 7 columns with information on employeesas the following but with much more number of rows, and will be adding more as new employees arrive.

emp_numb	salary	dept_num	etc	etc	etc	etc
0001	111111	10	Cell 2	Cell 1	Cell 2	Cell 1
0002	222222	20	Cell 2	Cell 1	Cell 2	Cell 1
0003	333333	30	Cell 2	Cell 1	Cell 2	Cell 1
0004	444444	10	Cell 2	Cell 1	Cell 2	Cell 1

Im trying to code a plsql procedure so that i can be shown the 2 employees with the lowest salaries from every department (and need to be able to use this procedure if later i add more department numbers). The thing is, with what i coded so far, im stuck in an infinite loop and cant be able to understand where im failing so far. If its not much to ask, as i am kinda new to sql, and my knowledge is not vast, could anyone check my code and tell me where im failing? Thanks in advance!!

My code is as follows:

create or replace procedure lowemployees
as
cursor cur1 is select * from emple order by dept_no,salario;
empleadus emple%rowtype;
i number;
depcursor emple.dept_no%type;
depcompare emple.dept_no%type;
begin
i:=0;
open cur1;
fetch cur1 into empleadus;
depcursor:=empleadus.dept_no;
depcompare:=depcursor+1;
while cur1%found loop
while i<2 loop
dbms_output.put_line(empleadus.emp_no||'|'||empleadus.nombre||'|'||empleadus.oficio||'|'||empleadus.salario||'|'||empleadus.dept_no);
i:=i+1;
fetch cur1 into empleadus;
end loop;
depcursor:=empleadus.dept_no;
depcompare:=depcursor+1;
while depcursor<depcompare loop
fetch cur1 into empleadus;
i:=0;
depcursor:=empleadus.dept_no;
end loop;
dbms_output.put_line(empleadus.emp_no||'|'||empleadus.nombre||'|'||empleadus.oficio||'|'||empleadus.salario||'|'||empleadus.dept_no);
depcompare:=depcursor+1;
fetch cur1 into empleadus;
end loop;
close cur1;
end lowemployees;

Answer 1

You will have much better success if you format your code with indentation. Here's what you have after I've added intendations:

create or replace procedure lowemployees
as
  cursor cur1 is 
    select * from emple order by dept_no,salario;
  empleadus emple%rowtype;
  i number;
  depcursor emple.dept_no%type;
  depcompare emple.dept_no%type;
begin
  i:=0;
  
  open cur1;
  fetch cur1 into empleadus;
  
  depcursor:=empleadus.dept_no;
  depcompare:=depcursor+1;
  
  while cur1%found loop
    while i<2 loop
      dbms_output.put_line(empleadus.emp_no||'|'||empleadus.nombre||'|'||empleadus.oficio||'|'||empleadus.salario||'|'||empleadus.dept_no);
      i:=i+1;
      
      fetch cur1 into empleadus;
    end loop;
    
    depcursor:=empleadus.dept_no;
    depcompare:=depcursor+1;
    
    while depcursor<depcompare loop
      fetch cur1 into empleadus;
      
      i:=0;
      depcursor:=empleadus.dept_no;
    end loop;
    
    dbms_output.put_line(empleadus.emp_no||'|'||empleadus.nombre||'|'||empleadus.oficio||'|'||empleadus.salario||'|'||empleadus.dept_no);
    depcompare:=depcursor+1;
    
    fetch cur1 into empleadus;
  end loop;
  
  close cur1;
end lowemployees;

You have multiple inner LOOPs inside your main cursor LOOP, and you are fetching in the inner loops. Fetch only once per outer cursor LOOP, at the end. You also have a loop without a clear exit condition:

while depcursor<depcompare loop
  fetch cur1 into empleadus;

  i:=0;
  depcursor:=empleadus.dept_no;
end loop;

This is a easy way to get into an infinite loop. You can't be sure dept_no will be less than, well, anything. It's a data value you don't control. You'll need to use more careful exit conditions.

But I would also suggest you reconsider your use of PL/SQL. Your need is something very simple to do in plain old SQL. No need for procedural code and all this nested looping:

SELECT *
  FROM (SELECT e.*,
               ROW_NUMBER() OVER (PARTITION BY dept_no ORDER BY salario) rnk
          FROM emple e)
 WHERE rnk <= 2