mysql存储过程，用游标遍历表数据

BEGIN

#Routine body goes here...

DECLARE nidCode varchar(50);

DECLARE m smallint(2);

DECLARE e smallint(2);

DECLARE p smallint(2);

DECLARE j smallint(6);

DECLARE x smallint(6);

DECLARE jpushpage varchar(32);

 

#渠道

DECLARE channels VARCHAR(20) default '';

DECLARE smsC VARCHAR(20)  default 'sms';

DECLARE smsCD VARCHAR(20)  default ',sms';

DECLARE emailC VARCHAR(20)  default 'email';

DECLARE emailCD VARCHAR(20)  default ',email';

 

DECLARE sysC VARCHAR(20)  default 'sys';

DECLARE sysD VARCHAR(20)  default ',sys';

 

DECLARE jpushC VARCHAR(20)  default 'jpush';

DECLARE jpushCD VARCHAR(20)  default ',jpush';

 

DECLARE xgC VARCHAR(20) default 'xg';

DECLARE xgCD VARCHAR(20) default ',xg';

 

#t_message_template中是否存在nid的记录

DECLARE cnt int default 0;

#总共修改了message.t_message_template几条记录

DECLARE modifyCount int default 0;

 

#这个用于处理游标到达最后一行的情况  

DECLARE s int default 0;

 

#声明游标cursor_name（cursor_name是个多行结果集）  

DECLARE cursor_name CURSOR FOR select nid,message,email,phone,jpush,xg,jpush_page from ucdai.yyd_remind;  

 

#设置一个终止标记   

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;  

 

#打开游标  

OPEN cursor_name; 

 

#获取游标当前指针的记录，读取一行数据并传给变量a,b 

fetch  cursor_name into nidCode,m,e,p,j,x,jpushpage ;  

set modifyCount = 0;

 

#开始循环，判断是否游标已经到达了最后作为循环条件   

while s <> 1 do  

 

            

set channels = '';

#系统消息

if m =1 || m=3 THEN

if channels = '' THEN

set channels = concat(channels,sysC);

else

set channels = concat(channels,sysCD);

end if;

end if;

#email

if e=1 || e=3 THEN

if channels = '' THEN

set channels = concat(channels,emailC);

else

set channels = concat(channels,emailCD);

end if;

end if;

#sms

if p=1 || p=3 THEN

if channels = '' THEN

set channels = concat(channels,smsC);

else

set channels = concat(channels,smsCD);

end if;

end if;

#jpush

if j=1 || j=3 THEN

if channels = '' THEN

set channels = concat(channels,jpushC);

else

set channels = concat(channels,jpushCD);

end if;

end if;

#xg

if x=1 || x=3 THEN

if channels = '' THEN

set channels = concat(channels,xgC);

else

set channels = concat(channels,xgCD);

end if;

end if;

 

#set nidCode = '41';

select count(*) into cnt from t_message_template where nid = nidCode;

#SELECT cnt;

if cnt > 0 THEN

update t_message_template set channel = channels,jpush_page= jpushpage,UPDATE_TIME =SYSDATE() where nid= nidCode;

set modifyCount = modifyCount + cnt;

end if;

 

#读取下一行的数据  

           fetch  cursor_name into nidCode,m,e,p,j,x,jpushpage;  

            

  end while;  

                   

  #关闭游标  

  CLOSE cursor_name ;  

select modifyCount;

   

END