oracle数据库中某一个字段可能存在以某些特殊符号隔开的字段,我们在查询使用的时候往往需要将这些字段spilt开
但是oracle没有这个函数,网上搜索了一下,找了一个可以使用的函数
下面直接上脚本:
1.先建立一个TYPE
create or replace type obj_target as object
(
obj_id varchar2(50),
obj_value varchar2(128)
);
2.TYPE映射table
create or replace type tabstr_t as table of varchar2(4000);
create or replace type typ_array_target as table of obj_target;
3.建立函数
create or replace function fun_split (
p_str in varchar2,
p_sep in varchar2 default ','
)
return tabstr_t
is
l_str long := p_str || p_sep;
l_tabstr tabstr_t := tabstr_t();
begin
while l_str is not null loop
l_tabstr.extend(1);
l_tabstr(l_tabstr.count) := rtrim(substr(
l_str,1,instr(l_str,p_sep)),p_sep);
l_str := substr(l_str,instr(l_str,p_sep)+1);
end loop;
return l_tabstr;
end;
4.使用
select column_value as val from table(fun_split(c.waybill_no,' '));
函数中使用
create or replace function fun_report_dealdetailed return typ_array_target
pipelined is
cursor cur is select id,waybill_no from t_tmk_order_track t where t.follow_result ='SUCCESS';
r_target_data obj_target := obj_target(null, null);
begin
for c in cur loop
r_target_data.obj_id := c.id;
for c1 in (select column_value as val from table(fun_split(c.waybill_no,' ')))
loop
r_target_data.obj_value := c1.val;
pipe row(r_target_data);
end loop;
end loop;
return;
end fun_report_dealdetailed;