有这样两个表:
table1:
f1 f2 f3
1 liu a,b,d
2 jin b,d
3 cai a,ctable2:
f4 f5
a northsnow
b precipitant
c 塞北的雪
d liujincai我想生成一个视图,返回这样的结果:v1 v2 v3
1 liu northsnow,precipitant,liujincai
2 jin precipitant,liujincai
3 cai northsnow,塞北的雪高手指点
table1:
f1 f2 f3
1 liu a,b,d
2 jin b,d
3 cai a,ctable2:
f4 f5
a northsnow
b precipitant
c 塞北的雪
d liujincai我想生成一个视图,返回这样的结果:v1 v2 v3
1 liu northsnow,precipitant,liujincai
2 jin precipitant,liujincai
3 cai northsnow,塞北的雪高手指点
SQL> create table table1(f1 varchar2(4),f2 varchar2(10),f3 varchar2(20));表已创建。SQL> begin
2 insert into table1 values(1,'liu','a,b,d');
3 insert into table1 values(2,'jin','b,d');
4 insert into table1 values(3,'cai','a,c');
5 commit;
6 end;
7 /PL/SQL 过程已成功完成。SQL> select * from table1;F1 F2 F3
---- ---------- --------------------
1 liu a,b,d
2 jin b,d
3 cai a,c已选择3行。SQL> create table table2(f4 varchar2(4),f5 varchar2(20));表已创建。SQL> begin
2 insert into table2 values('a','northsnow');
3 insert into table2 values('b','precipitant');
4 insert into table2 values('c','塞北的雪');
5 insert into table2 values('d','liujincai');
6 commit;
7 end;
8 /PL/SQL 过程已成功完成。SQL> select * from table2;F4 F5
---- --------------------
a northsnow
b precipitant
c 塞北的雪
d liujincai已选择4行。SQL> create or replace function show_str(
2 p_str in varchar2
3 )
4 return varchar2
5 as
6 l_str varchar2(500) default p_str;
7 l_substr varchar2(50);
8 r_str varchar2(500);
9 begin
10 while instr(l_str,',')>0 loop
11 l_substr:=substr(l_str,1,instr(l_str,',')-1);
12 for x in (select f5 from table2 where f4=l_substr) loop
13 r_str:=r_str||','||x.f5;
14 end loop;
15 l_str:=substr(l_str,instr(l_str,',')+1);
16 end loop;
17 for x in (select f5 from table2 where f4=l_str) loop
18 r_str:=r_str||','||x.f5;
19 end loop;
20 r_str:=substr(r_str,2);
21 return r_str;
22 end;
23 /函数已创建。SQL> select f1 v1,f2 v2,show_str(f3) v3 from table1;V1 V2 V3
---- ---------- ----------------------------------------
1 liu northsnow,precipitant,liujincai
2 jin precipitant,liujincai
3 cai northsnow,塞北的雪已选择3行。