Oracle的split

首先，Oracle是没有split函数的，因为Oracle看不到和数组类似的结构，因此这里参照网上的fn_split的函数做了点改进，下面的可以直接用
--网上的fn_split好像过滤掉null值，这里做了点改进，请注意区别

create or replace type ty_str_split is table of varchar2 (4000);
create or replace function fn_split (p_str in varchar2, p_delimiter in varchar2)
	return ty_str_split
is
	j int := 0;
	i int := 0;
	getLen int :=0;
	len int := 0;
	len1 int := 0;
	str varchar2 (4000);
	str_split ty_str_split := ty_str_split ();
	v_isFound number:=0;
begin
	if p_delimiter is not null then
		select instr(p_str,p_delimiter) into v_isFound from dual;
		if v_isFound=0  then--没找到
			str_split.extend;
			str_split (str_split.count) := p_str;
		else
			len := length (p_str);
			len1 := length (p_delimiter);
			while j <= len
			loop
				i:=i+1;
				j := instr (p_str, p_delimiter,i);
				getLen:=j-i;

				if j = 0  then
					str := substr (p_str, i);
					str_split.extend;
					str_split (str_split.count) := str;
					exit;
				else
					str := substr (p_str, i, getLen);
					str_split.extend;
					str_split (str_split.count) := str;
					i := j-1 + len1;
					end if;
			end loop;
		end if ;
	else
		str_split.extend;
		str_split (str_split.count) := p_str;
	end if ;
	return str_split;
end fn_split;

--具体用法
select t.column_value,rownum row_ from
table(
	cast (
		fn_split ('1,2,3,4,5,,7,,', ',') as ty_str_split
	)
) t