oracle怎么增加函数,Oracle中增加Split函数

首先，我们增加一个表类型:

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','#'))