MySQL Forums
Forum List  »  InnoDB

Nested cursor issue
Posted by: Francesco Lamonica
Date: January 29, 2009 02:28PM

Hi all,
I've got a stored procedure (sp1) and a stored function (sf1)

in my sp1 i have:
begin
declare done boolean default false;
declare cur_tel cursor for select field1,f2,f3 from table1 limit 10;
declare continue handler for not found set done=true;

open cur_tel;
myloop: loop

fetch cur_tel into v1, v2, v3;
if done then
leave myloop;
end if;

select sf1(v1,v2,v3,2) into v4;

-- just for debugging purposes
insert into tempt (test1,test2,test3,test4) values (v1, v2, v3, v4);

end loop;
close cur_tel;
end

and in sf1 i have:

begin
-- vars
declare ccost double default 0;
declare done2 boolean default false;
-- cursor
declare cur_timerate cursor for select anotherfield,af2 from t1;
declare continue handler for not found set done2=true;

open cur_timerate;
cloop: loop

fetch cur_timerate into vv1,vv2;

if done2 then
leave cloop;
end if;
return 23;
end loop;
close cur_timerate;

return ccost;
end;

what happens is that i always get 0 as a result of the sf1. the "return 23" statement is never hit! not even if i change the cursor declaration to something like:

declare cur_timerate cursor for select 10,20;

it just seems to hit the not found event each and every time.

Any idea of what i might be doing wrong?

(i am using 5.0.70 on a gentoo amd64 box)

thanks in advance

Options: ReplyQuote


Subject
Views
Written By
Posted
Nested cursor issue
4577
January 29, 2009 02:28PM
2626
March 10, 2009 08:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.