mysql 存储过程中使用while循环

最新推荐文章于 2024-07-27 21:51:48 发布

wocjj

最新推荐文章于 2024-07-27 21:51:48 发布

阅读量1.5k

点赞数

分类专栏： mysql 文章标签：存储 mysql integer c insert date

mysql 专栏收录该内容

49 篇文章

订阅专栏

本文介绍了一种改进的存储过程，用于在特定条件下从表t110034批量复制数据到表t100044，并进一步复制到表t100044_p_big，同时更新原始表的日期字段。通过循环和事件调度器的结合使用，该过程能够高效地处理可能同时符合条件的多条记录。

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

本存储过程中的begin end 之间的语句如下：
BEGIN
select count(*) as t110034_count
from t110034
where locate(c110277 , weekday(c110270)+1) >0 and c110278 < CURRENT_DATE
into @t110034_count;

select min(tid110034)
from t110034
where locate(c110277 , weekday(c110270)+1) >0 and c110278 < CURRENT_DATE
into @t110034_min;

declare t110034_i integer;
set t110034_i = convert(integer(), @t110034_min);

while t110034_i< = convert(integer(), @t110034_count) do
begin
select getseq('t100044') into @tid100044_get;

insert into t100044 (tid100044, c100369, c100422,c110251, c100437, c100439, c100423, c100372,c100420,
c100373, c100374, c100375, c100377, c100383, c100384,c100399, c100421, c100462, c100464, c100378)
select @tid100044_get, c110255, c110256, c110257, c110258, c110259, c110260, c110261, c110262,c110263,
c110264, c110265, c110266, c110267, c110269, c110270, c110271, c110272, c110273, c110274
from t110034
where tid110034 = t110034_i;

insert into t100044_p_big(tid, user_id, usergroup_id)
select @tid100044_get, user_id, usergroup_id
from t110034_p_big
where tid = t110034_i;

update t110034 set c110278=CURRENT_DATE
where tid110034 = t110034_i;

end
end while;

END

大致意思就是：在条件“locate(c110277 , weekday(c110270)+1) >0 and c110278 < CURRENT_DATE”下查询出表t110034里面的数据，通过循环复制到表t100044中，为什么要用循环？因为t100044表中的主键和表t100044_p_big中的tid一样都是通过自定义函数getseq('t100044')获得！此处用循环还有一点，就是此存储过程结合事件调度器使用，在条件“locate(c110277 , weekday(c110270)+1) >0 and c110278 < CURRENT_DATE”下并不是一条数据，有可能同时有多条数据符合条件。
请高手修改下上面的存储过程，或者利用游标书写一个新的存储过程！谢谢！

我已经解决了贴上答案！
==========================
CREATE PROCEDURE `t1100044_tian_jia_ji_lu`()
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

declare b integer;
declare c110255 varchar(20);
declare c110256 integer;
declare c110257 varchar(50);
declare c110258 varchar(200);
declare c110259 varchar(200);
declare c110260 date;
declare c110261 varchar(200);
declare c110262 varchar(200);
declare c110263 varchar(200);
declare c110264 varchar(50);
declare c110265 integer;
declare c110266 varchar(200);
declare c110267 varchar(200);
declare c110269 varchar(300);
declare c110270 datetime;
declare c110271 double(15,2);
declare c110272 double(15,2);
declare c110273 double(15,2);
declare c110274 double(15,2);
declare tid100044_get_id integer;
declare oldtid integer;
declare user_id integer;
declare usergroup_id integer;

declare t110044_cursor cursor for
select getseq('t100044') as tid, t.tid110034 as oldtid, tg.user_id, tg.usergroup_id,
t.c110255, t.c110256, t.c110257, t.c110258, t.c110259, t.c110260, t.c110261,
t.c110262, t.c110263, t.c110264, t.c110265, t.c110266, t.c110267, t.c110269, t.c110270,
t.c110271, t.c110272, t.c110273, t.c110274
from t110034 as t , t110034_p_big as tg
where t.tid110034 = tg.tid
and locate(t.c110277 , weekday(t.c110270)+1) >0
and t.c110278 < CURRENT_DATE;

declare exit handler for not found
set b=1;

open t110044_cursor;
repeat
fetch t110044_cursor into tid100044_get_id, oldtid, user_id, usergroup_id,
c110255, c110256, c110257, c110258, c110259, c110260, c110261, c110262, c110263,
c110264, c110265, c110266, c110267, c110269, c110270, c110271, c110272, c110273,
c110274;

insert into t100044 (tid100044, c100369, c100422,c110251, c100437, c100439,
c100423, c100372,c100420, c100373, c100374, c100375, c100377, c100383,
c100384,c100399, c100421, c100462, c100464, c100378)
values (tid100044_get_id, c110255, c110256, c110257, c110258, c110259, c110260,
c110261, c110262,c110263, c110264, c110265, c110266, c110267, c110269,
c110270, c110271, c110272, c110273, c110274);

insert into t100044_p_big(tid, user_id, usergroup_id)
values(tid100044_get_id, user_id, usergroup_id);

update t110034 set c110278=CURRENT_DATE
where t110034.tid110034 = oldtid;

until b=1
end repeat;
close t110044_cursor;

END;