create database if not exists test;
drop table if exists test.color_count;
create table test.color_count(
  color_name varchar(32) charset ascii not null primary key,
  count      int unsigned
) engine=innnodb;
insert into test.color_count values ('green', 3), ('blue', 17), ('yellow', 4), ('white', 10), ('red', 8);
select * from test.color_count;


DELIMITER $$

drop procedure if exists test.review_colors $$

create procedure test.review_colors() 
deterministic
reads sql data
begin
  declare done bool default false;
  declare current_color varchar(32) default null;
  declare current_count int unsigned;
  declare color_cursor cursor for 
            select color_name, count from test.color_count order by color_name;
  declare continue handler for not found set done := true;
  
  open color_cursor;
  cursor_loop: while not done do
    fetch color_cursor into current_color, current_count;
    if done then
      leave cursor_loop;
    end if;
    -- select current_color, current_count;
    call review_single_color(current_color);
  end while;
  close color_cursor;
end
$$

DELIMITER ;

DELIMITER $$

drop procedure if exists test.review_single_color $$

create procedure test.review_single_color(
  color_name varchar(32) charset ascii
) 
deterministic
no sql

begin
  set @review_message := concat(color_name, ' is pretty');
  select @review_message;
end
$$

DELIMITER ;