mysql cursor loop select

Question

I am writing a procedure which will go through a table and collect all the top 10 results. I am using a cursor then fetching the results. I'm a little stuck on how to print out the results. I used a select statement to get those results, but I'm told that there is an error.

Any help will be appreciated. Thanks.

create procedure plant_list()
begin
declare v_plant_id integer(5);
declare v_common_name varchar(30);
declare v_scientific_name varchar(20);


declare finished boolean default false;

declare cur_top_ten cursor for
    select P.plant_id, common_name, concat(genus, ' ', species)
    from plants P
        join plant_taxonomy PT on P.plant_id = PT.plant_id
    order by list_price desc
    limit 10
    ;

declare continue handler for not found set finished = true;

open cur_top_ten;
curloop: loop
    fetch cur_top_ten into v_plant_id, v_common_name, v_scientific_name;
    if finished then
        close cur_top_ten;
        set finished = false;
        leave curloop;
    end if

    select v_plant_id, v_common_name, v_scientific_name;


end loop curloop;

end;
#

user1682055 · Accepted Answer · 2012-09-30 07:35:23Z

1

There's a missing semi colon after the end if!

  if finished then
    close cur_top_ten;
    set finished = false;
    leave curloop;
  end if;

answered Sep 30, 2012 at 7:35

user1682055

1032 gold badges3 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql cursor loop select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related