sid sname book ---- ------ --------------------------------------------- 01 WY AAA 01 WY BBB 01 WY CCCselect A.Filed A.book,B.book,C.book from Table1 A ,Table2 B,Table1 C where A.条件 =B.条件 and A.条件 =C.条件 group by A.book,B.book,C.book
create or replace function get_book(p_id in varchar2) return varchar2 is Result varchar2(100); cursor rad_cursor(v_id varchar2) is select book from student where id=v_id; begin Result:=''; for v_sor in rad_cursor(p_id) loop Result:=Result||','|| v_sor.book; end loop; return(Result); end get_book; / SQL> select id,sname,get_book(id) book from student group by id,sname; ID SNAME BOOK ---------- -------------------- -------------------------------------------------------------------------------- 1 WY ,AAA,BBB,CCC
beckhambobo(beckham)写的很好我把 beckhambobo(beckham)写的修正了一下:如去掉多余逗号,经过修改表test可以有完全一样的字段,即去重复记录。create or replace function get_book(p_id in varchar2) return varchar2 is Result varchar2(100); cursor rad_cursor(v_id varchar2) is select distinct book from test where sid=v_id order by book desc; v_count Number; begin Result:=''; v_count:=0 for v_sor in rad_cursor(p_id) loop if v_count=0 Result:=v_sor.book; else Result:=v_sor.book||','||Result; end loop; return(Result); end get_book; /
呵呵,zhangshunshi(宇轩)兄 现在上班时间,偷偷上网小心老板!!!
重修改如下:create or replace function get_book(p_id in varchar2) return varchar2 is Result varchar2(100);
cursor rad_cursor(v_id varchar2) is select book from student where id=v_id order by book desc; v_count Number; begin v_count:=0; Result:=''; for v_sor in rad_cursor(p_id) loop if v_count=0 then Result:=v_sor.book; v_count:=v_count+1; else Result:=v_sor.book||','||Result; end if; end loop; return(Result); end get_book; /SQL> select id,sname,get_book(id) book from student group by id,sname; ID SNAME BOOK ---------- -------------------- -------------------------------------------------------------------------------- 1 WY AAA,BBB,CCC
还有一个表“TEST1”,叫返回的记录插入“TEST1”,得到以下结果
sid sname book1 book2 boo3
---- ------ ---------- ---------- ----------
01 WY AAA BBB CCC
---- ------ ---------------------------------------------
01 WY AAA
01 WY BBB
01 WY CCCselect A.Filed A.book,B.book,C.book
from Table1 A ,Table2 B,Table1 C
where A.条件 =B.条件
and A.条件 =C.条件
group by
A.book,B.book,C.book
Result varchar2(100);
cursor rad_cursor(v_id varchar2) is
select book from student
where id=v_id;
begin
Result:='';
for v_sor in rad_cursor(p_id) loop
Result:=Result||','|| v_sor.book;
end loop;
return(Result);
end get_book;
/
SQL> select id,sname,get_book(id) book from student group by id,sname; ID SNAME BOOK
---------- -------------------- --------------------------------------------------------------------------------
1 WY ,AAA,BBB,CCC
Result varchar2(100);
cursor rad_cursor(v_id varchar2) is
select distinct book from test
where sid=v_id order by book desc;
v_count Number;
begin
Result:='';
v_count:=0
for v_sor in rad_cursor(p_id) loop
if v_count=0
Result:=v_sor.book;
else
Result:=v_sor.book||','||Result;
end loop;
return(Result);
end get_book;
/
Result varchar2(100);
cursor rad_cursor(v_id varchar2) is
select book from student
where id=v_id order by book desc;
v_count Number;
begin
v_count:=0;
Result:='';
for v_sor in rad_cursor(p_id) loop
if v_count=0 then
Result:=v_sor.book;
v_count:=v_count+1;
else
Result:=v_sor.book||','||Result;
end if;
end loop;
return(Result);
end get_book;
/SQL> select id,sname,get_book(id) book from student group by id,sname; ID SNAME BOOK
---------- -------------------- --------------------------------------------------------------------------------
1 WY AAA,BBB,CCC