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結束重復;
結束;