Oracle实现类似SPLIT函数功能
步骤1:创建函数的返回类型,它为自定义类型;
CREATE OR REPLACE TYPE type_split AS TABLE OF varchar2(4000);
步骤2: 实现SPLIT函数
CREATE OR REPLACE FUNCTION f_split(p_list varchar2, p_seperator varchar2) return type_split
PIPELINED IS
l_idx PLS_INTEGER;
v_list varchar2(32767):=p_list;
BEGIN
LOOP
l_idx:=instr(v_list,p_seperator);
IF l_idx>0 THEN
PIPE ROW(substr(v_list,1,l_idx-1));
v_list:=substr(v_list,l_idx+length(p_seperator));
ELSE
PIPE ROW(v_list);
EXIT;
END IF;
END LOOP;
END;
/
步骤3:检验函数功能
select * from table(f_split('aaa,bbb,ccc,ddd,eee,fff,ggg,hhh,jjj',','));
Result: