mysql 游标 控制结构_mysql存储过程的游标和控制结构

DELIMITER $$

DROP PROCEDURE IF EXISTS `books`.`largest_order`$$

CREATE DEFINER=`root`@`192.168.18.248` 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 cl cursor for select orderid,amount from orders;

declare continue handler for sqlstate '02000' set done =1;

open cl;

repeat

fetch cl 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 cl;

set largest_id = l_id;

END$$

DELIMITER ;