--拆分字符串函数:
--定义字符串类型的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';
--拆分字符串函数:--定义字符串类型的tablecreate or replace type type_return_str is table of varchar2(4000);--创建拆分字符串函数create or replace function orcl_split_str(p_string varchar2, --待拆分字符串...