mysql 存储过程中游标的使用

游标须在存储过程中使用.

 

 

delimiter $$

begin

declare rid integer(11) default 0;
declare name varchar(100) default '';
declare cid varchar(6) default '';
declare temp1 varchar(6) default '';
declare temp2 varchar(6) default '';

declare cur_exe cursor for(select rowid,taskname from tt_task1);
declare continue handler for not found set name = null;

open cur_exe;
fetch cur_exe into rid,name;

while (name is not null) do
 set temp1=substr(name,instr(name,'C'),6);
 set temp2=substr(name,char_length(name)-instr(reverse(name),'C')+1,6);
 if (temp1 like 'C0%' or temp1 like 'CT%') then
  set cid=temp1;
 else
  if (temp2 like 'C0%' or temp2 like 'CT%') then
   set cid=temp2;
  else
   set cid='';
  end if;
 end if;
 
 if cid!='' then
  update tt_task1 set chgid=cid where rowid=rid;
 end if;
 
 fetch cur_exe into rid,name;

end while;
close cur_exe;

end$$