求解一个oracle查询笔试题

Question

表A 三列
a   b    c
1   han  li
2   qing zhang
3   hong han表B 两列
c      d
1      hi
1      he
2      ha
2      ho要一个查询语句得到
1   han   li       'hi','he'
2   qing  zhang    'ha','ho'
请大虾指教 不知道能不能一个sql语句得到？？

Answer 1

select a.a,a.b,b.d
from a,
(
select b.c,WMSYS.WM_CONCAT(b.c) d
from b
) b
where a.a=b.c不过出来的是
hi,he
不是'hi','he'
如果想出来带引号的，要自己写存储过程了

Answer 2

CREATE OR REPLACE TYPE "S_TABLE"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  is
table of varchar2(1000)create or replace function translate_into_string (pv in S_TABLE) return varchar2
is
ls long;
begin
   for i in 1..pv.count loop
      ls := ls || pv(i);
   end loop;
   return ls;
end;
select A.b,A.c,
       (translate_into_string(cast(multiset(select b1.d from B b1 where b1.c = B.c) as strings_table)))
from A, B
where A.a=B.c

Answer 3

我的是9i 用不了WMSYS.WM_CONCAT 而且用它是不是应该加group by？ 
还有别的方法吗 

Answer 4

declare
       type ref_type is ref cursor;
       ref_a ref_type;
       str varchar(20);
       str1 varchar(20);
       cursor cur_a is select distinct t.a,t.b,t.c from (select a.*,b.b d from a a right join b b on a.a = b.a) t;  
begin
     for var_a in cur_a
     loop
         str := '"';
         open ref_a for select b.b from b b where b.a = var_a.a;
         loop
             fetch ref_a into str1;
             exit when ref_a%notfound;
             str := str||str1||'","';
         end loop;
         close ref_a;
         select substr(str,0,length(str)-2) into str from dual;
         dbms_output.put_line(var_a.a||' '||var_a.b||' '||var_a.c||' '||str);
     end loop;
end;