create or replace function list_betweenand ( ageFrom in number, ageTo in number ) as v_name poets.name%type; cursor c_list is SELECT a.name from poets a,deaths b WHERE a.id=b.id and b.age between ageFrom and ageTo;begin open c_list; loop fetch c_list into v_name; exit when c_list%notfound; --你的处理 END loop;
close c_list; end;
啊,你的函数还没有返回值类型,在函数后加一句:
create or replace function list_betweenand ( ageFrom in number, ageTo in number ) return varchar2 as v_name poets.name%type; cursor c_list is SELECT a.name from poets a,deaths b WHERE a.id=b.id and b.age between ageFrom and ageTo;begin open c_list; loop fetch c_list into v_name; exit when c_list%notfound; --你的处理 END loop;
close c_list; end;
谢谢给我回复! 我不是要对选出的每条纪录作处理,我是想得到select返回的多条纪录,然后在JAVA中得到ResultSet. 而且返回的每条纪录可能是多个字段的。如:select id,name from poets;
create or replace function list_early_deaths ( ageFrom in number, ageTo in number ) return refcursor is toesup refcursor; begin open toesup for SELECT a.name,b.age from poets a,deaths b WHERE a.id=b.id and b.age between ageFrom and ageTo; return toesup; end list_early_deaths; /提示错误:function已经创建了,但是有编译错误。
单独运行,不要和函数在一起,你用的是SQL*PLUS吧,这个是SQL*PLUS的命令,不是PL/SQL的。
(
ageFrom in number,
ageTo in number
)
as
v_name poets.name%type;
cursor c_list is
SELECT a.name from poets a,deaths b WHERE a.id=b.id and b.age between ageFrom and ageTo;begin
open c_list;
loop
fetch c_list into v_name;
exit when c_list%notfound;
--你的处理
END loop;
close c_list;
end;
create or replace function list_betweenand
(
ageFrom in number,
ageTo in number
) return varchar2
as
v_name poets.name%type;
cursor c_list is
SELECT a.name from poets a,deaths b WHERE a.id=b.id and b.age between ageFrom and ageTo;begin
open c_list;
loop
fetch c_list into v_name;
exit when c_list%notfound;
--你的处理
END loop;
close c_list;
end;
我不是要对选出的每条纪录作处理,我是想得到select返回的多条纪录,然后在JAVA中得到ResultSet.
而且返回的每条纪录可能是多个字段的。如:select id,name from poets;
(
ageFrom in number,
ageTo in number
) return refcursor is
toesup refcursor;
begin
open toesup for SELECT a.name,b.age from poets a,deaths b WHERE a.id=b.id and b.age between ageFrom and ageTo;
return toesup;
end list_early_deaths;
/提示错误:function已经创建了,但是有编译错误。