jpuriol jpuriol - 7 months ago 39
SQL Question

Fix infinite loop PL/SQL. Cursos. Exit when c%NOTFOUND not working

Hello I am new to databases and PL/SQL, right now I am learning how to use cursors.

My problem is that when I execute the following code in Oracle SQL Developer I get an infinite loop.

set serveroutput on

DECLARE

CURSOR c1
IS
select tipoanimal, count(*), avg(precio)
from mi_cursos
group by tipoanimal;

xtotal_cursos number;
xtipo_tipoanimal mi_cursos.tipoanimal%type;
xcuenta_curso_animal number;
xprecio_medio_curso_animal number;
total_porcentaje number;

BEGIN
select count(*) into xtotal_cursos from mi_cursos;

OPEN c1;

LOOP
EXIT WHEN c1%NOTFOUND;
total_porcentaje:= xcuenta_curso_animal/xtotal_cursos*100;
dbms_output.put_line(rpad(xtipo_tipoanimal,10,' ')||
lpad(to_char(xcuenta_curso_animal,'999999'),10,' ')||
lpad(to_char(total_porcentaje,'99999.99'),10,' ')||
lpad(to_char(xprecio_medio_curso_animal, '999999'),10,' ') );
END LOOP;

CLOSE c1;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error -10: error no conocido');
dbms_output.put_line('Error Oracle ' || TO_CHAR(SQLCODE) || ' Mensaje: ' || SUBSTR(SQLERRM,1,200));
END;


That's weird because
EXIT WHEN c1%NOTFOUND
shout get me out of the loop when the cursor is done.

Any thoughts?

PD: Sorry for the Spanish in the code. I am from Spain :)

Answer

You should use FETCH c1 INTO <variables> clause.

It is also possible to use implicit cursors without open-fetch-close routines.

By the way use can give column aliases for count(*), avg(precio) functions.