mysql存储过程实例

mysql存储过程

delimiter $

drop function if exists `fun_convert`$

create definer=`root`@`%` function   `fun_convert`(para varchar(255))

 returns varchar(255) charset gbk

begin

         declare   result varchar(255) default '';

         declare   cnt int default 0;

         declare   i int default 0;

         declare   id bigint default 0;

         set   cnt = length(para) - length(replace(para,',',''))-1;

         while   i < cnt do

      select c.id into id from company c where c.code =   substring(para,i*3+2,2);

                   set   result = concat(result, ",",id);

                   set   i = i+1;

         end   while;

         set   result = concat(result, ",");

         return   result;

end

$

delimiter ;

 

update users u set u.company =   fun_convert(u.company);