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