create OR REPLACE type Split_Type as object
(
s_id integer,
s_value varchar2(1000)
);
CREATE OR REPLACE TYPE Split_Table IS TABLE OF Split_Type ;
CREATE OR REPLACE FUNCTION Split (
p_str IN VARCHAR2,
p_delimiter IN VARCHAR2 default(',') --分隔符,默认逗号
)
RETURN Split_Table
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
return_split Split_Table := Split_Table();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);
WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);
IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
return_split.EXTEND;
return_split (return_split.COUNT) := Split_Type(return_split.Count, str);
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
return_split.EXTEND;
return_split (return_split.COUNT) := Split_Type(return_split.Count, str);
END IF;
END LOOP;
RETURN return_split;
END Split;