拆分字符串函数:

--拆分字符串函数:
--定义字符串类型的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', ',')));

--统计条数
select a.*,count(a.caml_cli_num) over() as 统计条数 from table a where a.xx = 'xxxxxx';