-----oracle将字段字符分隔作为临时表
select column_value as site_id
from table (select fn_split_clob(dashboard_presend_sites_show, ',')
from ems.t_isdt_user_info
where hw_user_uuid = 'uuid~eVdYMzk3MzEy')
create or replace function fn_split_clob (p_str in clob,
p_delimiter in varchar2)
return ty_str_split is
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
STR varchar2(4000);
str_split ty_str_split := ty_str_split();
Begin
len := LENGTH(p_str);
len1 := length(p_delimiter);
while j < len loop
j := INSTR(P_str, p_delimiter, i);
if j = 0 then
j := len;
str := substr(p_str, i);
str_split.extend;
str_split(str_split.count) := str;
if i >= len then
exit;
end if;
else
str := substr(p_str, i, j - i);
i := j + len1;
str_split.extend;
str_split(str_split.count) := str;
end if;
end loop;
return str_split;
end fn_split_clob;