delimiter $

MYSQL數據庫DROP FUNCTION IF EXISTS `fun_convert`$

MYSQL數據庫CREATE DEFINER=`root`@`%` FUNCTION?? `fun_convert`(para varchar(255))

MYSQL數據庫?RETURNS varchar(255) CHARSET gbk

MYSQL數據庫BEGIN

MYSQL數據庫???????? declare?? result varchar(255) default '';

MYSQL數據庫???????? declare?? cnt int default 0;

MYSQL數據庫???????? declare?? i int default 0;

MYSQL數據庫???????? declare?? id BIGINT default 0;

MYSQL數據庫???????? set?? cnt = length(para) - length(replace(para,',',''))-1;

MYSQL數據庫???????? WHILE?? i < cnt DO

MYSQL數據庫????? select c.id into id from company c where c.code =?? SUBSTRING(para,i*3+2,2);

MYSQL數據庫?????????????????? set?? result = CONCAT(result, ",",id);

MYSQL數據庫?????????????????? set?? i = i+1;

MYSQL數據庫???????? end?? WHILE;

MYSQL數據庫???????? set?? result = CONCAT(result, ",");

MYSQL數據庫???????? RETURN?? result;

MYSQL數據庫END

MYSQL數據庫$

MYSQL數據庫delimiter ;

MYSQL數據庫?

MYSQL數據庫update users u set u.company =?? fun_convert(u.COMPANY);