--包头
create or replace package test
as
type myCursors is ref cursor;
function get(id number) return myCursors;
end test;
--包体
create or replace package body test
as
--******************************************************************
--输入ID 返回记录集的函数
function get(id number) return myCursors is
rc myCursors;
strsql varchar2(200);
begin
if id=0 then
open rc for select t.name from T_user t ;
else
strsql:='select t.name from T_user t where t.id=:id';
open rc for strsql using id;
end if;
return rc;
end get;
end test;
--调用
set serverout on
declare
w_rc test.myCursors;
w_name varchar2(100);
begin
w_rc:=test.get(0);
loop
fetch w_rc into w_name;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;
/
create or replace package test
as
type myCursors is ref cursor;
function get(id number) return myCursors;
end test;
--包体
create or replace package body test
as
--******************************************************************
--输入ID 返回记录集的函数
function get(id number) return myCursors is
rc myCursors;
strsql varchar2(200);
begin
if id=0 then
open rc for select t.name from T_user t ;
else
strsql:='select t.name from T_user t where t.id=:id';
open rc for strsql using id;
end if;
return rc;
end get;
end test;
--调用
set serverout on
declare
w_rc test.myCursors;
w_name varchar2(100);
begin
w_rc:=test.get(0);
loop
fetch w_rc into w_name;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;
/