字符串分割

type tt_type is table of varchar2(32) INDEX BY BINARY_INTEGER;

--使用”,”将分割字符串转换为数组，返回为数组个数，

--闫磊 Email:Landgis@126.com,yanleigis@21cn.com 2002.06.03编写


function toArray(Liststr in varchar2, V1 out tt_type) return integer is



--FieldNames转化为数组
TmpStr varchar2(32);
Str varchar2(4000);
j integer;
begin
Str := Liststr;
j := 0;
IF Instr(Liststr, ',', 1, 1) = 0 THEN
V1(j) := Liststr;
j := j + 1;
else
While Instr(str, ',', 1, 1) > 0 Loop
TmpStr := Substr(str, 1, Instr(str, ',', 1, 1) - 1);

V1(j) := TmpStr;
str := SubStr(Str, Instr(str, ',', 1, 1) + 1, length(str));
j := j + 1;
end loop;
if not str is null then
--将最后一个保存
V1(j) := str;
j := j + 1;
end if;
end if;
return J;
end;


另一种写法：
1. CREATE OR REPLACE TYPE mytable AS TABLE OF varchar2(100)
2. /


# CREATE OR REPLACE FUNCTION split
# (src VARCHAR2, delimiter varchar2)
# RETURN mytable IS
# psrc VARCHAR2(500);
# a mytable := mytable();
# i NUMBER := 1; --
# j NUMBER := 1;
# BEGIN
# psrc := RTrim(LTrim(src, delimiter), delimiter);
# LOOP
# i := InStr(psrc, delimiter, j);
# --Dbms_Output.put_line(i);
# IF i>0 THEN
# a.extend;
# a(a.Count) := Trim(SubStr(psrc, j, i-j));
# j := i+1;
# --Dbms_Output.put_line(a(a.Count-1));
# END IF;
# EXIT WHEN i=0;
# END LOOP;
# IF j < Length(psrc) THEN
# a.extend;
# a(a.Count) := Trim(SubStr(psrc, j, Length(psrc)+1-j));
# END IF;
# RETURN a;
# END;