--拆分字符串函数: --定义字符串类型的table create or replace type type_return_str is table of varchar2(4000); --创建拆分字符串函数 create or replace function orcl_split_str(p_string varchar2, --待拆分字符串 p_fh varchar2 --拆分符号 ) return type_return_str pipelined as v_length number := length(p_string); v_del_length number := length(p_fh); v_start number := 1; v_index number; begin while (v_start <= v_length) loop v_index := instr(p_string, p_fh, v_start); if v_index = 0 then pipe row(substr(p_string, v_start)); v_start := v_length + v_del_length; else pipe row(substr(p_string, v_start, v_index - v_start)); v_start := v_index + v_del_length; end if; end loop; return; end orcl_split_str; / --使用函数 select column_value from table(orcl_split_str('sd,weg,kjyu,gfhd', ','));
--使用场景: select * from table t where t.col in(select column_value from table(orcl_split_str('sd,weg,kjyu,gfhd', ',')));
