有一个数据表,其中的一个字段比较长,是由多个属性组成,例如:
SQL> select code_string,项目名称,code_value from code_standard;
CODE_STRING 项目名称 CODE_VALUE
-------------------- ------------------------------ --------------------------------------------------------------------------------
210403006011 螺母 物料大分类码段,标准件,203|标准件分类码段,紧固件,204|紧固件分类码段,螺母,215|标准代号码段,GB/T6178-1986,530|规格码段,M20,1371|材料(强度等级)码段,8,1718|表面处理码段,氧化,506
CODE_STRING 项目名称 CODE_VALUE
-------------------- ------------------------------ --------------------------------------------------------------------------------
210403006011 螺母 物料大分类码段,标准件,203|标准件分类码段,紧固件,204|紧固件分类码段,螺母,215|标准代号码段,GB/T6178-1986,530|规格码段,M20,1371|材料(强度等级)码段,8,1718|表面处理码段,氧化,506
第三个字段非常的长,希望猜分成 这样:
210403006011 螺母 标准件 紧固件 螺母 GB/T6178-1986 M20 8 氧化
1,创建一个 split 函数
create or replace type type_split as table of varchar2(50); --创建一个 type ,如果为了使split函数具有通用性,请将其size 设大些。
--创建function
create or replace function split
(
p_list varchar2,
p_sep varchar2 := ','
) return type_split pipelined
is
l_idx pls_integer;
v_list varchar2(50) := p_list;
begin
loop
l_idx := instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list,1,l_idx-1));
v_list := substr(v_list,l_idx+length(p_sep));
else
pipe row(v_list);
exit;
end if;
end loop;
return;
end split;
--创建function
create or replace function split
(
p_list varchar2,
p_sep varchar2 := ','
) return type_split pipelined
is
l_idx pls_integer;
v_list varchar2(50) := p_list;
begin
loop
l_idx := instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list,1,l_idx-1));
v_list := substr(v_list,l_idx+length(p_sep));
else
pipe row(v_list);
exit;
end if;
end loop;
return;
end split;
2,创建主函数
----创建类型
create or replace type m_type is object(a varchar2(50),b varchar2(50),c varchar2(50),d varchar2(50),e varchar2(50),f varchar2(50),g varchar2(50),h varchar2(50),k varchar2(50))
create or replace type m_table is table of m_type
-----创建function
create or replace function getCodeData return m_table
as
cursor ca is select code_string as item_code,项目名称 as item_name,code_value from code_standard;
rs ca%rowtype;
rs2 m_table:=m_table();
type cursor_type is ref cursor;
type record_type is record(
a varchar2(50)
);
c1 cursor_type;
r_c1 record_type;
c2 cursor_type;
r_c2 record_type;
TYPE type_arrry IS TABLE OF VARCHAR2(50);
myArray type_arrry:=type_arrry();
mysql varchar2(500);
i integer:=0;
m integer;
n integer;
begin
n:=1;
m:=1;
myArray.extend(10);
open ca;
loop
fetch ca into rs;
exit when ca%notfound;
rs2.extend(1);
myArray(1):=rs.item_code;
myArray(2):=rs.item_name;
i:=3;
mysql:='select * from table(split('''|| rs.code_value ||''',''|''))';
open c1 for mysql;
loop
fetch c1 into r_c1;
exit when c1%notfound;
mysql:='select * from table(split(''' || r_c1.a || ''','',''))';
n:=1;
open c2 for mysql;
loop
fetch c2 into r_c2;
exit when c2%notfound;
if n=2 then
--myArray.extend(1);
myArray(i):=r_c2.a;
end if;
n:=n+1;
end loop;
close c2;
i:=i+1;
end loop;
close c1;
rs2(m):=m_type(myArray(1),myArray(2),myArray(3),myArray(4),myArray(5),myArray(6),myArray(7),myArray(8),myArray(9));
m:=m+1;
end loop;
close ca;
return rs2;
end;
create or replace type m_type is object(a varchar2(50),b varchar2(50),c varchar2(50),d varchar2(50),e varchar2(50),f varchar2(50),g varchar2(50),h varchar2(50),k varchar2(50))
create or replace type m_table is table of m_type
-----创建function
create or replace function getCodeData return m_table
as
cursor ca is select code_string as item_code,项目名称 as item_name,code_value from code_standard;
rs ca%rowtype;
rs2 m_table:=m_table();
type cursor_type is ref cursor;
type record_type is record(
a varchar2(50)
);
c1 cursor_type;
r_c1 record_type;
c2 cursor_type;
r_c2 record_type;
TYPE type_arrry IS TABLE OF VARCHAR2(50);
myArray type_arrry:=type_arrry();
mysql varchar2(500);
i integer:=0;
m integer;
n integer;
begin
n:=1;
m:=1;
myArray.extend(10);
open ca;
loop
fetch ca into rs;
exit when ca%notfound;
rs2.extend(1);
myArray(1):=rs.item_code;
myArray(2):=rs.item_name;
i:=3;
mysql:='select * from table(split('''|| rs.code_value ||''',''|''))';
open c1 for mysql;
loop
fetch c1 into r_c1;
exit when c1%notfound;
mysql:='select * from table(split(''' || r_c1.a || ''','',''))';
n:=1;
open c2 for mysql;
loop
fetch c2 into r_c2;
exit when c2%notfound;
if n=2 then
--myArray.extend(1);
myArray(i):=r_c2.a;
end if;
n:=n+1;
end loop;
close c2;
i:=i+1;
end loop;
close c1;
rs2(m):=m_type(myArray(1),myArray(2),myArray(3),myArray(4),myArray(5),myArray(6),myArray(7),myArray(8),myArray(9));
m:=m+1;
end loop;
close ca;
return rs2;
end;
3,测试
SQL> select * from table(getcodedata) where rownum<10;
A B C D E F G H K
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
210403006011 螺母 标准件 紧固件 螺母 GB/T6178-1986 M20 8 氧化
210401001022 螺母 标准件 紧固件 螺母 GB/T6181-1986 M8 04 不经处理
210504004012 垫圈 标准件 紧固件 垫圈 GB/T853-1988 8 Q215 不经处理
210113026011 螺栓 标准件 紧固件 螺栓 GB/T37-1988 M10*40 8.8 氧化
210113002013 螺栓 标准件 紧固件 螺栓 GB/T37-1988 M12*45 8.8 镀铬
210113017011 螺栓 标准件 紧固件 螺栓 GB/T37-1988 M6*30 8.8 氧化
210113005011 螺栓 标准件 紧固件 螺栓 GB/T37-1988 M8*65 8.8 氧化
210111009012 螺栓 标准件 紧固件 螺栓 GB/T5780-2000 M20*110 4.8 不经处理
210107053012 螺栓 标准件 紧固件 螺栓 GB/T5781-2000 M10*25 4.8 不经处理
9 rows selected
SQL>
A B C D E F G H K
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
210403006011 螺母 标准件 紧固件 螺母 GB/T6178-1986 M20 8 氧化
210401001022 螺母 标准件 紧固件 螺母 GB/T6181-1986 M8 04 不经处理
210504004012 垫圈 标准件 紧固件 垫圈 GB/T853-1988 8 Q215 不经处理
210113026011 螺栓 标准件 紧固件 螺栓 GB/T37-1988 M10*40 8.8 氧化
210113002013 螺栓 标准件 紧固件 螺栓 GB/T37-1988 M12*45 8.8 镀铬
210113017011 螺栓 标准件 紧固件 螺栓 GB/T37-1988 M6*30 8.8 氧化
210113005011 螺栓 标准件 紧固件 螺栓 GB/T37-1988 M8*65 8.8 氧化
210111009012 螺栓 标准件 紧固件 螺栓 GB/T5780-2000 M20*110 4.8 不经处理
210107053012 螺栓 标准件 紧固件 螺栓 GB/T5781-2000 M10*25 4.8 不经处理
9 rows selected
SQL>