我正在阅读有关存储过程的文章,这是代码:
delimiter //
create procedure largest_order(out largest_id int)
begin
declare this_id int;
declare this_amount float;
declare l_amount float default 0.0;
declare l_id int;
declare done int default 0;
declare continue handler for sqlstate '02000' set done = 1;
declare c1 cursor for select orderid, amount from orders;
open c1;
repeat
fetch c1 into this_id, this_amount;
if not done then
if this_amount > l_amount then
set l_amount=this_amount;
set l_id=this_id;
end if;
end if;
until done end repeat;
close c1;
set largest_id=l_id;
end
//
delimiter ;
我正在使用一个名为“ mydatabase”的简单数据库。运行完上面的代码后,它给了我这个错误:ERROR 1338 (42000): Cursor declaration after handler declaration
什么是错误的,我该如何解决?
这是我第一次使用存储过程。
每个MySql文档:
游标声明必须出现在处理程序声明之前,变量和条件声明之后。
所以我将代码更新如下:
delimiter //
create procedure largest_order(out largest_id int)
begin
declare this_id int;
declare this_amount float;
declare l_amount float default 0.0;
declare l_id int;
-- 1. cursor finished/done variable comes first
declare done int default 0;
-- 2. the curser declaration and select
declare c1 cursor for select orderid, amount from orders;
-- 3. the continue handler is defined last
declare continue handler for sqlstate '02000' set done = 1;
open c1;
repeat
fetch c1 into this_id, this_amount;
if not done then
if this_amount > l_amount then
set l_amount=this_amount;
set l_id=this_id;
end if;
end if;
until done end repeat;
close c1;
set largest_id=l_id;
end
//
delimiter ;
现在工作正常。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句