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);