MySQL通过游标来实现通过查询结果集循环

本文介绍了一个使用MySQL存储过程更新数据库记录的具体实例。该存储过程通过游标遍历指定表中的所有记录,并从另一个表中收集相关联的数据,然后更新主表中的字段。此外,还提供了一个将表中的ID更新为UUID格式的存储过程示例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

学习
借鉴 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();
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值