参考下面的返回记录的做法:--包头
create or replace package dinya_pkg_test
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end dinya_pkg_test;
--包体
create or replace package body dinya_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 dinya_pkg_test;--调用
set serverout on
declare
w_rc dinya_pkg_test.myCursor;
w_name varchar2(100);
begin
w_rc:=dinya_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 package dinya_pkg_test
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end dinya_pkg_test;
--包体
create or replace package body dinya_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 dinya_pkg_test;--调用
set serverout on
declare
w_rc dinya_pkg_test.myCursor;
w_name varchar2(100);
begin
w_rc:=dinya_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;PRAGMA RESTRICT_REFERENCES (get, WNDS );----函数级别end dinya_pkg_test;
type myrctype is table of myrecord;
--函数申明
function get(parorgcode varchar2)
return myrctype;
PRAGMA RESTRICT_REFERENCES (get, WNDS );----函数级别
end pkg_test;CREATE OR REPLACE PACKAGE BODY "PKG_TEST" as
--函数体
function get(parorgcode varchar2)
return myrctype
is
rc myrctype;
i integer:=0;
cursor suborg1 is select orgcode from TB_SYS_ORG where superiororgcode=parorgcode;
suborg2 suborg1%rowtype;
begin
open suborg1;
fetch suborg1 into suborg2;
if nvl(suborg2.orgcode,'')<>'' then
rc(i).ORGCODE:=suborg2.orgcode;
i:=i+1;
end if;
close suborg1;
return rc;
end get; end pkg_test;最终执行:select pkg_test.get('szzb') from dual;
报错,还说数据类型无效?
谢谢你的回复!原因是需要把pkg_test.get('szzb')的结果再与其它表关联,方便直接取数据。详细是:不同的身份进入系统,它只可查看他权限内的相关资料,如机构、客户、仓库等。如果能提取出机构等的代码,再与其它表关联,才会有意义。
ufn_getcustf() 返回字符串,被用于截取
select语句:
select custcode from TB_SYS_CUSTOMER
where instr((select ufn_getcust() as result from dual),custcode)>0