学习
借鉴 http://www.cnblogs.com/silentdoer/p/6274576.html
drop procedure if exists lopp_health;
create procedure lopp_health()
BEGIN
/*这种写法也可以:DECLARE done INT DEFAULT FALSE;*/
declare done int default 0; /*用于判断是否结束循环*/
declare solarNames VARCHAR(1000); /*用于存储结果集S_S的记录(因为我这里S_S的记录只有一列且为bigint类型)*/
/*定义游标*/
declare solarCur cursor for select solar_term from health;
/*定义 设置循环结束标识done值怎么改变 的逻辑*/
declare continue handler for not FOUND set done = 1; /*done = true;亦可*/
open solarCur;
REPEAT
FETCH solarCur into solarNames;
if not done THEN
update health
set staple_ingredients
= (select group_concat(staple_ingredients) from health_copy where solar_term = solarNames and staple_ingredients <> ''),
dishes_ingredients
= (select group_concat(dishes_ingredients) from health_copy where solar_term = solarNames and dishes_ingredients <> ''),
breakfast_ingredients
= (select group_concat(breakfast_ingredients) from health_copy where solar_term = solarNames and breakfast_ingredients <> ''),
tea_ingredients
= (select group_concat(tea_ingredients) from health_copy where solar_term = solarNames and tea_ingredients <> ''),
disable_ingredients
= (select group_concat(disable_ingredients) from health_copy where solar_term = solarNames and disable_ingredients <> '')
where solar_term = solarNames;
end if;
until done END repeat;
close solarCur;
END
/** 更新 id 为 UUID **/
drop procedure if exists update_id_uuid;
create procedure update_id_uuid()
BEGIN
/*这种写法也可以:DECLARE done INT DEFAULT FALSE;*/
declare done int default 0; /*用于判断是否结束循环*/
DECLARE my_uuid char(36);
DECLARE my_uuid_ char(36);
/*定义游标*/
declare idCur cursor for select id from health;
/*定义 设置循环结束标识done值怎么改变 的逻辑*/
declare continue handler for not FOUND set done = 1; /*done = true;亦可*/
open idCur;
REPEAT
FETCH idCur into my_uuid;
if not done THEN
set my_uuid_ = (select replace(my_uuid,'-',''));
update health set id=my_uuid_ where id = my_uuid;
end if;
until done END repeat;
close idCur;
END
/* 执行 */
call lopp_health();