首先,我们增加一个表类型:
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','#'))