Oracle Procedure之字符串分隔

create or replace procedure tokenizer(iStart in NUMBER,
	sPattern in VARCHAR2, sBuffer in VARCHAR2,
	sResult out VARCHAR2, iNextPos out NUMBER) is
nPos1 number;
nPos2 number;
begin
	nPos1 := Instr(sBuffer, sPattern, iStart);
	if nPos1 = 0 then
		sResult := NULL;
	else
		nPos2 := Instr (sBuffer, sPattern, nPos1 + 1);
		if nPos2 = 0 then
			sResult := Rtrim(Ltrim(Substr(sBuffer, nPos1 + 1)));
			iNextPos := nPos2;
		else
			sResult := Substr(sBuffer, nPos1 + 1, nPos2 - nPos1 - 1);
			iNextPos := nPos2;
		end if;
	end if;
end tokenizer;

--测试tokenizer
create or replace procedure test_tokenizer is
sepr varchar2(1);
sbuf varchar2(200);
sres varchar2(200);
pos number := 1;
istart number;
begin
	sbuf := '|Test1|Test2|Test3' || '|Test4';
	sepr := '|';
	--istart := 1;
	--tokenizer(istart, sepr, sbuf, sres, pos);
	--if (pos <> 0) then
	--dbms_output.put_line (sres);
	--end if;
	while (pos <> 0)
	loop
		istart := pos;
		tokenizer(istart, sepr, sbuf, sres, pos);
		dbms_output.put_line(sres);
	end loop;
end;