CREATE OR REPLACE FUNCTION Strsplit(p_str VARCHAR2, s VARCHAR2)
/*
对字符串进行拆分。接受两个参数字符串和拆分字符。调用例子:
select * from table(cast(strsplit('sdfsdf,12,13', ',') as strtabletype ))
*/
RETURN strtabletype
AS
lstr VARCHAR2(1000) DEFAULT p_str||s;
LN NUMBER;
ldata strtabletype:=strtabletype();
BEGIN
LOOP
LN:=INSTR(lstr,s);
EXIT WHEN (NVL(LN,0)=0);
ldata.EXTEND;
ldata(ldata.COUNT):=LTRIM(RTRIM(SUBSTR(lstr,1,LN-1)));
lstr:=SUBSTR(lstr,LN+1);
END LOOP;
RETURN ldata;
END Strsplit;
/
--自定义数据类型
CREATE OR REPLACE
TYPE "STRTABLETYPE"
AS TABLE OF VARCHAR2(40);
/
调用:
--返回值放在游标中,strtabletype是自定义的数据类型cursor c_importData is select * from table(cast(strsplit(importData, ';') as strtabletype ));