mysql 存储过程 while 中断_mysql 存储过程中使用while循环？

本存储过程中的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;

2010年6月22日 09:58