-- stored procedure
drop procedure if exists getBoatsOfColor;

create procedure getBoatsOfColor(argcolor char(10))
select *
from boats
where color=argcolor;

call getBoatsOfColor('red');

create procedure getAverageAgeOfSailors(out avgage real)
select avg(age) into avgage
from sailors;

call getAverageAgeOfSailors(@avgage);

select @avgage;

-- variables
set @bid = 102;

select *
from boats
where bid = @bid;

set @bestsailorname = '';
select @bestsailorname := sname, rating
from sailors
order by rating
limit 1;

select @bestsailorname;

select avg(age) into @avgsailorage
from sailors;

select @avgsailorage;


delimiter //

create function getratingexplanation(rating integer)
returns varchar(30) deterministic
begin
	declare explanation varchar(30) default 'don\'t know';
	if rating < 3 then
		set explanation = 'bad';
	elseif rating < 7 then
		set explanation = 'great';
	elseif rating < 9 then
		set explanation = 'excellent';
	else
		set explanation = 'best';
	end if;
    return explanation;
end //
delimiter ;

select rating, getratingexplanation(rating)
from sailors
order by rating desc;