mysql insert字符串_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