create procedure average_rate in game_id int 11 out avg_rate decimal 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create procedure average_rate(in game_id int(11), out avg_rate decimal(10,2))
begin
declare i, maxid, currgame,numrate int(3);
declare currate int(3);
set i=1;
set avg_rate=0;
set numrate=0;
select count(*) into maxid from reviews;
while i<=maxid
do
select id_game into currgame from reviews where id_review=i;
if(currgame=game_id)
then
begin
select rate into currate from reviews where id_review=i;
set avg_rate = avg_rate+currate;
set numrate = numrate+1;
end;
end if;
set i=i+1;
end while;
set avg_rate = avg_rate / numrate;
end//