存储过程，函数相关

//函数 function

//得到某个字符串某个符号分割之后的个数

CREATE DEFINER=`daojia_root`@`%` FUNCTION `calc_length`(str varchar(200), splitstr varchar(5)) RETURNS int(11)

BEGIN
return length(str)-length(replace(str, splitstr, ''))+1;  
END



//得到某个字符串按照某个符号分割之后的的第几个的值

CREATE DEFINER=`daojia_root`@`%` FUNCTION `split_string`(str varchar(200), splitstr varchar(5), strindex int) RETURNS varchar(255) CHARSET utf8
BEGIN
declare result varchar(700) default '';  
set result =reverse(substring_index( reverse(substring_index(str, splitstr, strindex)), splitstr, 1));  
return result;  
END



//得到某个字符串 按照符号分割将分割之后的值重新拼接成新的字符串返回

CREATE DEFINER=`daojia_root`@`%` FUNCTION `hebing`(str varchar(700)) RETURNS varchar(700) CHARSET utf8
BEGIN


declare cnt int default 0;  
declare i int default 0;  
declare result varchar(700) default '';
set cnt = calc_length(str, ';');  


while i<cnt
do 


set i=i+1;
 set @onegps =split_string(str,';',i);
 set @cnt2= calc_length(@onegps, ',');//调用上边的方法
 set @j=0;
 while @j<@cnt2
 do 
 set @j=@j+1;
  set @oneonegps =split_string(@onegps,',',@j);//调用上边的方法
  if @j=1 
  then
  set @lng=concat('{"lng":','"',@oneonegps,'"');
  else
  set @lat=concat(',"lat":','"',@oneonegps,'"}');
  END IF;  
  set @gps=concat(@lng,@lat);
  if result=''
  then 
   set result=concat( result,@gps);
   else
  set result=concat( result,',',@gps);
  end if;
 end while;
end while;
set  result=concat( "[",result,"]");
return result;


END



//示例  直接在select的返回字段值上调用上边的函数

    SELECT 
    CAST(id AS CHAR),
    market AS biz_name,
    HEBING(points) AS biz_range,
    city_id,
    biz_center,
    'SY',
    '2018-01-01',
    '2099-01-01'
FROM
    bdap.t_dim_biz_suyun_new

//存储过程

CREATE DEFINER=`daojia_root`@`%` PROCEDURE `hecheng`(in str varchar(500) , out result varchar(1000))
BEGIN


declare cnt int default 0;  
declare i int default 0;  
set cnt = calc_length(str, ';');  
set result ='';
while i<cnt
do 


set i=i+1;
 set @onegps =split_string(str,';',i);
 set @cnt2= calc_length(@onegps, ',');
 set @j=0;
 while @j<@cnt2
 do 
 set @j=@j+1;
  set @oneonegps =split_string(@onegps,',',@j);
  if @j=1 
  then
  set @lng=concat('{"lng":','"',@oneonegps,'"');
  else
  set @lat=concat(',"lat":','"',@oneonegps,'"}');
  END IF;  
  set @gps=concat(@lng,@lat);
  if result=''
  then 
   set result=concat( result,@gps);
   else
  set result=concat( result,',',@gps);
  end if;
 end while;
end while;
set  result=concat( "[",result,"]");
END

//调用示例



 call hecheng('116.341645,39.646486;116.377645,39.646486;116.377645,39.673486;116.341645,39.673486',@s);

 select @s;