首先，我们增加一个表类型:
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000)
其实，我们增加相对应的Split函数，该 函数返回ty_str_split表类型
CREATE OR REPLACE FUNCTION FN_SPLIT(P_STR IN VARCHAR2, P_DELIMITER IN VARCHAR2) RETURN TY_STR_SPLIT IS J INT := 0; I INT := 1; LEN INT := 0; LEN1 INT := 0; STR VARCHAR2(4000); STR_SPLIT TY_STR_SPLIT := TY_STR_SPLIT();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); STR_SPLIT.EXTEND; STR_SPLIT(STR_SPLIT.COUNT) := STR; IF I >= LEN THEN EXIT; END IF; ELSE STR := SUBSTR(P_STR, I, J - I); I := J + LEN1; STR_SPLIT.EXTEND; STR_SPLIT(STR_SPLIT.COUNT) := STR; END IF; END LOOP; RETURN STR_SPLIT;END FN_SPLIT;
接下来我们测试
SELECT * FROM TABLE(FN_SPLIT('1#2#3#,4','#'))