create or replace type t_vc is table of varchar2(200);CREATE OR REPLACE Function convertstring(p_str varchar2,p_delim varchar2)
/* 将一个字符串 p_str 根据 p_delim 分割成 单个字符串
-- 并且不包含 p_delim值
--- 查询 select * from table(cast(convertstring('ad,s,a,d,f,d,a',',') as t_vc))
--- order by 1 nulls first
create function by Perray on 20060602 15:34
*/
return t_vc
as
v_result t_vc;
v_delimlen number := length(p_delim);
i Number :=1 ;
j Number;
p integer; --point to p_str
pd integer; --point to p_delim
begin
If p_str is null Then
return Null;
ElsIf p_delim is null then
return t_vc(p_str);
End if;
v_result := t_vc(); --付值为一个空表
p:=1;
loop
pd := instr(p_str,p_delim,p);
v_result.extend;
If pd > 0 then
v_result(i) := substr(p_str,p,pd-p);
Else
v_result(i) := substr(p_str,p);
Exit;
End if; /*
如果相同的两个值 则做出错处理
现在因为可以有两个相同数值所以注释这点的数据
for j in 1..i loop
if j<> i and v_result(i)=v_result(j) Then
raise_application_error(-20000, 'Duplicate value on '||j||' and '||i||' :'||v_result(i));
End if;
End loop ;
*/
P := pd + v_delimlen;
i:= i+1; End loop ;
return v_result;
End ;drop table test_csdn;
create table test_csdn
(hm number(5),
group_no number(5),
xghm varchar2(30))
;
insert into test_csdn
values (23,12,'34,56,678,89');
insert into test_csdn
values (45,78,'12,44,34');
insert into test_csdn
values (78,99,'33,67') ;select t1.*,t2.column_value from test_csdn t1,
table(cast(convertstring(t1.xghm,',') as t_vc)) t2
/* 将一个字符串 p_str 根据 p_delim 分割成 单个字符串
-- 并且不包含 p_delim值
--- 查询 select * from table(cast(convertstring('ad,s,a,d,f,d,a',',') as t_vc))
--- order by 1 nulls first
create function by Perray on 20060602 15:34
*/
return t_vc
as
v_result t_vc;
v_delimlen number := length(p_delim);
i Number :=1 ;
j Number;
p integer; --point to p_str
pd integer; --point to p_delim
begin
If p_str is null Then
return Null;
ElsIf p_delim is null then
return t_vc(p_str);
End if;
v_result := t_vc(); --付值为一个空表
p:=1;
loop
pd := instr(p_str,p_delim,p);
v_result.extend;
If pd > 0 then
v_result(i) := substr(p_str,p,pd-p);
Else
v_result(i) := substr(p_str,p);
Exit;
End if; /*
如果相同的两个值 则做出错处理
现在因为可以有两个相同数值所以注释这点的数据
for j in 1..i loop
if j<> i and v_result(i)=v_result(j) Then
raise_application_error(-20000, 'Duplicate value on '||j||' and '||i||' :'||v_result(i));
End if;
End loop ;
*/
P := pd + v_delimlen;
i:= i+1; End loop ;
return v_result;
End ;drop table test_csdn;
create table test_csdn
(hm number(5),
group_no number(5),
xghm varchar2(30))
;
insert into test_csdn
values (23,12,'34,56,678,89');
insert into test_csdn
values (45,78,'12,44,34');
insert into test_csdn
values (78,99,'33,67') ;select t1.*,t2.column_value from test_csdn t1,
table(cast(convertstring(t1.xghm,',') as t_vc)) t2
23 12 34,56,678,89 34
23 12 34,56,678,89 56
23 12 34,56,678,89 678
23 12 34,56,678,89 89
45 78 12,44,34 12
45 78 12,44,34 44
45 78 12,44,34 34
78 99 33,67 33
78 99 33,67 67