首先在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;