给个例子。仅供参考CREATE OR REPLACE PROCEDURE PTEST as strname varchar2(30); str varchar2(1000); strid varchar2(10); cursor cur is select * from tb; begin str:=''; open cur; loop fetch cur into strid,strname; exit when cur%notfound; str:=str||strname||','; end loop; str:=substr(str,1,length(str)-1); dbms_output.put_line(str); end;
比如: select a.id, a.name, GetAllAddressFromB(a.id) from a;
function可以参照楼上高人 ORARichard(没钱的日子......) 的做法。
我把函数写成下面这样: CREATE OR REPLACE function PTEST ( id varchar2 --adds out varchar2 ) RETURN VARCHAR2 as str varchar2(1000); strname varchar2(30); strid varchar2(10); cursor cur is select * from test2 a where a.id = id; begin str:=''; open cur; loop fetch cur into strid,strname; exit when cur%notfound; str:=str||strname||','; end loop; str:=substr(str,1,length(str)-1); dbms_output.put_line(str); return str; end;然后用下面这条查询语句查询: select t.*,ptest(t.id) from test1 t 结果发现地址竟然是所有的地址都连了起来,而不是根据id来的
select t.*,ptest(t.id) from (select distinct id from test1) t
cursor cur is select * from test2 a where a.id = id; 改成 type type_cur is ref cursor; cur type_cur for 'select * from test2 a where a.id ='||id;
上面错了cursor cur is select * from test2 a where a.id = id; 改成 type type_cur is ref cursor; cur type_cur; open cur; 改成 open cur for 'select * from test2 a where a.id ='||id;
呵呵,select * from test2 a where a.id = id, 查出来的是所有的纪录。 'select * from test2 a where a.id =' || id; 查出来的是id等于参数的纪录。
SQL817> ed 已写入文件 afiedt.buf 1 CREATE OR REPLACE function fTEST 2 ( 3 id varchar2 4 ) 5 RETURN VARCHAR2 6 as 7 str varchar2(1000); 8 strname varchar2(30); 9 strid varchar2(10); 10 type type_cur is ref cursor; 11 cur type_cur; 12 begin 13 str:=''; 14 open cur for 'select * from test2 a where a.id ='||id; 15 loop 16 fetch cur into strid,strname; 17 exit when cur%notfound; 18 str:=str||strname||','; 19 end loop; 20 str:=substr(str,1,length(str)-1); 21 close cur; 22 return str; 23* end; SQL817> /函数已创建。SQL817> select * from test1;ID NAME -- ------ 01 杨过 02 小龙女SQL817> select * from test2;ID ADDR -- ------ 01 绝情谷 01 上海摊 02 abc 02 123 02 hahaSQL817> select id,name,ftest(id) from test1;ID NAME FTEST(ID) -- ------ -------------------- 01 杨过 绝情谷,上海摊 02 小龙女 abc ,123 ,hahaSQL817> desc test1 名称 空? 类型 ----------------------------------------- -------- ---------------------------- ID CHAR(2) NAME VARCHAR2(6)SQL817> desc test2 名称 空? 类型 ----------------------------------------- -------- ---------------------------- ID CHAR(2) ADDR CHAR(6)
参考这个 http://community.csdn.net/Expert/topic/3222/3222974.xml?temp=.5841181测试: create table tb1(id char(2),name varchar2(6)); insert into tb1 values('01','张一'); insert into tb1 values('02','李二');create table tb2(id char(2),name varchar2(6)); insert into tb2 values('01','张三'); insert into tb2 values('01','张四'); insert into tb2 values('02','李五'); insert into tb2 values('02','李六'); insert into tb2 values('02','李七');create or replace function f_test(id in varchar2) return varchar2 is str_return varchar2(100); cursor c_test(v_id varchar2) is select name from tb2 where id = v_id; begin for str_temp in c_test(id) loop str_return := str_return||','||str_temp.name; end loop; return str_return; end; / select id,name,f_test(id) from (select distinct id,name from tb1); drop table tb1; drop table tb2; drop function f_test; /*ID NAME -- ------ F_TEST(ID)01 张一 ,张三,张四02 李二 ,李五,李六,李七 */
反正我没写出来
from 表A A,
表B B,
表B C
where A.ID = B.ID
and B.ID = C.ID
and B.ID = 01
你自己做一个function, 这个function用起来就像oracle的内置函数一样,他的输入参数是每一行的ID, 输出是b表具有a表那个ID的地址连接串,不就可以了吗?
至于这个function怎么写,知道PL/SQL的,都应该会。
strname varchar2(30);
str varchar2(1000);
strid varchar2(10);
cursor cur is select * from tb;
begin
str:='';
open cur;
loop
fetch cur into strid,strname;
exit when cur%notfound;
str:=str||strname||',';
end loop;
str:=substr(str,1,length(str)-1);
dbms_output.put_line(str);
end;
select a.id, a.name, GetAllAddressFromB(a.id) from a;
CREATE OR REPLACE function PTEST
(
id varchar2
--adds out varchar2
)
RETURN VARCHAR2
as
str varchar2(1000);
strname varchar2(30);
strid varchar2(10);
cursor cur is select * from test2 a where a.id = id;
begin
str:='';
open cur;
loop
fetch cur into strid,strname;
exit when cur%notfound;
str:=str||strname||',';
end loop;
str:=substr(str,1,length(str)-1);
dbms_output.put_line(str);
return str;
end;然后用下面这条查询语句查询:
select t.*,ptest(t.id) from test1 t
结果发现地址竟然是所有的地址都连了起来,而不是根据id来的
01 name1
02 name2
B表建了4条记录:
01 add1
01 add2
02 add3
02 add4
查出来的结果却是:
01 name1 add1,add2,add3,add4
02 name2 add1,add2,add3,add4
改成
type type_cur is ref cursor;
cur type_cur for 'select * from test2 a where a.id ='||id;
改成
type type_cur is ref cursor;
cur type_cur;
open cur;
改成
open cur for 'select * from test2 a where a.id ='||id;
'select * from test2 a where a.id =' || id; 查出来的是id等于参数的纪录。
已写入文件 afiedt.buf 1 CREATE OR REPLACE function fTEST
2 (
3 id varchar2
4 )
5 RETURN VARCHAR2
6 as
7 str varchar2(1000);
8 strname varchar2(30);
9 strid varchar2(10);
10 type type_cur is ref cursor;
11 cur type_cur;
12 begin
13 str:='';
14 open cur for 'select * from test2 a where a.id ='||id;
15 loop
16 fetch cur into strid,strname;
17 exit when cur%notfound;
18 str:=str||strname||',';
19 end loop;
20 str:=substr(str,1,length(str)-1);
21 close cur;
22 return str;
23* end;
SQL817> /函数已创建。SQL817> select * from test1;ID NAME
-- ------
01 杨过
02 小龙女SQL817> select * from test2;ID ADDR
-- ------
01 绝情谷
01 上海摊
02 abc
02 123
02 hahaSQL817> select id,name,ftest(id) from test1;ID NAME FTEST(ID)
-- ------ --------------------
01 杨过 绝情谷,上海摊
02 小龙女 abc ,123 ,hahaSQL817> desc test1
名称 空? 类型
----------------------------------------- -------- ----------------------------
ID CHAR(2)
NAME VARCHAR2(6)SQL817> desc test2
名称 空? 类型
----------------------------------------- -------- ----------------------------
ID CHAR(2)
ADDR CHAR(6)
http://community.csdn.net/Expert/topic/3222/3222974.xml?temp=.5841181测试:
create table tb1(id char(2),name varchar2(6));
insert into tb1 values('01','张一');
insert into tb1 values('02','李二');create table tb2(id char(2),name varchar2(6));
insert into tb2 values('01','张三');
insert into tb2 values('01','张四');
insert into tb2 values('02','李五');
insert into tb2 values('02','李六');
insert into tb2 values('02','李七');create or replace function f_test(id in varchar2)
return varchar2
is
str_return varchar2(100);
cursor c_test(v_id varchar2) is select name from tb2 where id = v_id;
begin
for str_temp in c_test(id) loop
str_return := str_return||','||str_temp.name;
end loop;
return str_return;
end;
/
select id,name,f_test(id) from (select distinct id,name from tb1);
drop table tb1;
drop table tb2;
drop function f_test;
/*ID NAME
-- ------
F_TEST(ID)01 张一
,张三,张四02 李二
,李五,李六,李七
*/