CODEWITHSUNDEEP
sqloracle-databaseplsql
jpuriol
jpuriol

Reputation: 372

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?

Upvotes: 0

Views: 1191

Answers (1)

Mennan
Mennan

Reputation: 81

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.

Upvotes: 1

Related Questions