當前位置:成語大全網 - 書法字典 - Mysql用遊標遍歷數據批量更新數據,急求mysql解答。

Mysql用遊標遍歷數據批量更新數據,急求mysql解答。

根據妳的信息,正確答案應該是:望采納。

CREATE DEFINER = ` root `@ ` localhost '過程` p _ updateud 18 `( ` param ` int(11))

開始

聲明Rcount,I,j,k,Rparentid,myId int(11)默認為0;

聲明Robjname,Robjname23,Robjname45,Robjname67,rob jname 89 varchar(9)default " ";

從ud18中選擇count(id)到Rcount

重復

select id,objname into myId,Robjname from ud 18 order by id limit I,1;

set rob jname 23 = SUBSTRING(rob jname,2,2);

set rob jname 45 = SUBSTRING(rob jname,4,2);

set rob jname 67 = SUBSTRING(rob jname,6,2);

set rob jname 89 = SUBSTRING(rob jname,8,2);

如果Robjname89 & gt那麽0

set @ SQL = concat(" select id into @ ABC from ud 18 where objname like ' ",left(Robjname,7)," 00 "," ' ");

從@sql準備stmt

執行stmt

set Rparentid = @ abc

更新ud 18 set parentid = Rparentid,其中id = myId

elseif Robjname67 & gt那麽0

set @ SQL = concat(" select id into @ ABC from ud 18 where objname like ' ",left(Robjname,5)," 0000 "," ' ";

從@sql準備stmt

執行stmt

set Rparentid = @ abc

更新ud 18 set parentid = Rparentid,其中id = myId

elseif Robjname45 & gt那麽0

set @ SQL = concat(" select id into @ ABC from ud 18 where objname like ' ",left(Robjname,3)," 000000 "," ' ");

從@sql準備stmt

執行stmt

set Rparentid = @ abc

更新ud 18 set parentid = Rparentid,其中id = myId

elseif Robjname23 & gt那麽0

-set @ SQL = concat(" select id into @ ABC from ud 18 where objname like ' ",left(Robjname,1)," 00000000 "," ' ";

-從@sql準備stmt

-執行stmt

-set Rparentid = @ ABC;

更新ud18 set parentid=0,其中id = myId

其他

設j = 0;

結束if;

集合I = I+1;

直到我& gtRcount結束重復;

結束;