MySQL的存储过程示例002（字符串解析）

函数1：LOCATE  查找字符

函数2：SUBSTRING  截取字符

CREATE DEFINER=`root`@`%` PROCEDURE `f_DispAtta`(vi_ID varchar(50))
BEGIN
	
	declare vi_split varchar(2);
	
	declare v_Str      varchar(3000);  -- 根据MySQL的版本不同，最长字符长度不一定，RDS定义一个30000都行，有的却是16383
	declare v_Category varchar(50);
	declare v_AID      varchar(50);

  set vi_split=',';

	select ID into v_AID from lfcp_attachment where ID = vi_ID;
	select SHARE_IDS into v_Str from lfcp_attachment where ID = vi_ID;
	select CATEGORY_CODE into v_Category from lfcp_attachment where ID = vi_ID;
	
	while (LOCATE(vi_split, v_Str) <> 0) do
	  if LENGTH(substring(v_Str, 1, LOCATE(vi_split, v_Str)-1)) > 3 then
      insert into lfcp_attachment_guarantee (attachment_id, guarantee_id, CATEGORY_CODE) values (v_AID, substring(v_Str, 1, LOCATE(vi_split, v_Str)-1), v_Category);
		end if;
		
    set v_Str = SUBSTRING(v_Str, LOCATE(vi_split, v_Str)+1, LENGTH(v_Str));
  end while;
  
  if LENGTH(v_Str) > 3 then
	  insert into lfcp_attachment_guarantee (attachment_id, guarantee_id, CATEGORY_CODE) 
    values (v_AID, v_Str, v_Category);
	end if;

END