下面是返回记录集的例子,估计你是要返回人记录集,看看有没有帮助--参考create or replace package pkg_test
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end pkg_test;create or replace package body pkg_test
as
function get(p_id number) return myCursor is
rc myCursor;
strsql varchar2(200);
begin
if p_id=0 then
open rc for select a.user_name from fnd_user a ;
else
strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
open rc for strsql using p_id;
end if;
return rc;
end get;
end pkg_test;
--调用
set serverout on
declare
w_rc pkg_test.myCursor;
w_name varchar2(100);
begin
w_rc:=pkg_test.get(0);
loop
fetch w_rc into w_name;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;
/
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end pkg_test;create or replace package body pkg_test
as
function get(p_id number) return myCursor is
rc myCursor;
strsql varchar2(200);
begin
if p_id=0 then
open rc for select a.user_name from fnd_user a ;
else
strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
open rc for strsql using p_id;
end if;
return rc;
end get;
end pkg_test;
--调用
set serverout on
declare
w_rc pkg_test.myCursor;
w_name varchar2(100);
begin
w_rc:=pkg_test.get(0);
loop
fetch w_rc into w_name;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;
/
select * from user_source where name='abc.def.ghi' ????
只有这样才能针对性的定义,并取得结果。 与sys_refcursor(9i以上版本),
则在sqlplus中必须
declare
r sys_refcurosr;
begin
r:=abc.def.ghi(6000001,200505,200506,-10);
fetch r into ...;
...end;如果太多不清楚,那爱莫能助了,哈哈!还是回去啃一下cursor部分的简单资料
是不是调用错了呀?还有要搞清楚是procedure还是function.
call可以调用proceudre, 但不能调用function的