--包头
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
--输入ID 返回记录集的函数
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;
/
用游标似乎可以。
create or replace function sp_ListEmp return types.cursortype as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;
return l_cursor;
end;
但是不明白,这个东西,可以和MS SQL的select ename, empno from emp order by ename存储过程等价么??还有,效率怎么样?可以支持超大数量的的并发访问么????