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;
Oracle Procedure之字符串分隔
最新推荐文章于 2021-08-11 11:24:02 发布