首先在types那里定义T_VARRAY的表类型
CREATE OR REPLACE TYPE "T_VARRAY"
IS
TABLE OF VARCHAR2(200)
定义函数 STR_SPLIT,P_LIST为要拆分的字符串aa,bb,cc 。P_SEP 为拆分的字符,
CREATE OR REPLACE FUNCTION STR_SPLIT(
P_LIST IN VARCHAR2,P_SEP IN VARCHAR2
) RETURN T_VARRAY
IS
L_IDX number;
V_LIST VARCHAR2(250) := P_LIST;
RES VARCHAR2(250);
J NUMBER(4):=1;
CSTR T_VARRAY := T_VARRAY(); --声明集合
BEGIN
LOOP
L_IDX := INSTR(V_LIST, P_SEP);
IF L_IDX > 0 THEN
RES := SUBSTR(V_LIST, 1, L_IDX - 1);
CSTR.EXTEND(1);
CSTR(J) := RES;
V_LIST := SUBSTR(V_LIST, L_IDX + LENGTH(P_SEP));
ELSE
RES := V_LIST;
CSTR.EXTEND(1);
CSTR(J) := RES;
EXIT;
END IF;
J :=J+1;
END LOOP;
RETURN CSTR;
END STR_SPLIT;
测试
declare
-- Non-scalar parameters require additional processing
result T_VARRAY;
begin
-- Call the function
result := STR_SPLIT(P_LIST => :P_LIST,
P_SEP => :P_SEP);
for j in 1..result.count loop
dbms_output.put_line(j||'='||result(j));
end loop;
end;