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