MYSQL——Split字符串并且插入值

Demo

BEGIN
	#添加项目资料 3,1,1,2,'1;2'
  DECLARE tdocId int;#声明一个输出值

  DECLARE s int default 0; #声明一个判断游标到最后的检测值
  DECLARE tyoub CURSOR FOR SELECT `value` from zzsplittable;#设置一个游标
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;  #设置游标到最后的条件
  
  call zzSplist(i_docIds,';');#调取函数分割字符串

  OPEN tyoub; #打开游标开始遍历
  FETCH tyoub INTO tdocId;   
  while s <> 1 do  
     INSERT INTO oa_doc_pro(projectid,docid) VALUES(i_proId,tdocId); #输出值的操作
 
     FETCH tyoub INTO tdocId; #游标到下一个
  end while;  
  CLOSE tyoub; #关闭游标

END

zzSplit存储过程

CREATE  PROCEDURE `zzSplist`(inputstring varchar(1000),
    delim char(1))
begin
    declare strlen int DEFAULT length(inputstring);
    declare last_index int DEFAULT 0;
    declare cur_index int DEFAULT 1;
    declare cur_char VARCHAR(200);
    declare len int;
    drop temporary table if exists zzsplittable;
    create TEMPORARY table zzsplittable(
        value VARCHAR(20)
    ) ;
    WHILE(cur_index<=strlen) DO    
    begin
        if substring(inputstring from cur_index for 1)=delim or cur_index=strlen then
            set len=cur_index-last_index-1;
            if cur_index=strlen then
               set len=len+1;
            end if;
            insert into zzsplittable(`value`)values(substring(inputstring from (last_index+1) for len));
            set last_index=cur_index;
        end if;
        set cur_index=cur_index+1;
    END;
    end while;
end

转载于:https://www.cnblogs.com/zzzili/p/6685265.html