mysql游标自动化_MySQL游标的使用 - 简书

drop procedure if exists StatisticStore2;

CREATE PROCEDURE StatisticStore2()

BEGIN

declare c int;

declare n varchar(20);

declare total int default 0;

declare done int default false;

declare cur cursor for select name,count from store where name = 'iphone';

declare continue HANDLER for not found set done = true;

set total = 0;

open cur;

repeat

fetch cur into n,c;

if not done then

set total = total + c;

end if;

until done end repeat;

close cur;

select total;

在mysql中，每个begin end 块都是一个独立的scope区域，由于MySql中同一个error的事件只能定义一次，如果多定义的话在编译时会提示Duplicate handler declared in the same block。

drop procedure if exists StatisticStore3;

CREATE PROCEDURE StatisticStore3()

BEGIN

declare _n varchar(20);

declare done int default false;

declare cur cursor for select name from store group by name;

declare continue HANDLER for not found set done = true;

open cur;

read_loop:loop

fetch cur into _n;

if done then

leave read_loop;

end if;

begin

declare c int;

declare n varchar(20);

declare total int default 0;

declare done int default false;

declare cur cursor for select name,count from store where name = 'iphone';

declare continue HANDLER for not found set done = true;

set total = 0;

open cur;

iphone_loop:loop

fetch cur into n,c;

if done then

leave iphone_loop;

end if;

set total = total + c;

end loop;

close cur;

select _n,n,total;

begin

declare c int;

declare n varchar(20);

declare total int default 0;

declare done int default false;

declare cur cursor for select name,count from store where name = 'android';

declare continue HANDLER for not found set done = true;

set total = 0;

open cur;

android_loop:loop

fetch cur into n,c;

if done then

leave android_loop;

end if;

set total = total + c;

end loop;

close cur;

select _n,n,total;

begin

end loop;

close cur;

call StatisticStore3();