dimanche 30 août 2015

errors in while loop in pl/sql stored procedure when using with mysql

CREATE PROCEDURE grades() 

BEGIN 
DECLARE avrg,p,c,m,no int(3);
declare grd varchar(20);
set no=1 ;

while no < 11 do  
select phy,chem,math into p,c,m from stud where id=no; 
set avrg=(p+c+m)/3;

if (avrg%10)<4.00 then set grd="fail";
else if  (avrg % 100) < 0.50 then set grd="pass";
else if (avrg % 100) < 0.56 then set grd="sec";
else if (avrg % 100) < 0.60 then set grd="Hsec";
else if (avrg % 100) < 0.66 then set grd="First";
else if (avrg%100) > 0.66 then set grd="Distinction";
end if ;

update stud set avg='avrg',grade='grd' 
where id=no;
set no=no+1;

end while ;
end$$

this code is giving error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'while ; end' at line 1.

Please help.

Aucun commentaire:

Enregistrer un commentaire