比如我在一個package中定義了2function (function A 和function B) 2函數都是cursor的返回類型
那么我要在B中呼叫A 怎么呼叫?我是這么寫的 對麼?
cursor b1 is select A(參數) from dual;
.
.
.
.
.
open b1;
close b1;這樣對么?
那么我要在B中呼叫A 怎么呼叫?我是這么寫的 對麼?
cursor b1 is select A(參數) from dual;
.
.
.
.
.
open b1;
close b1;這樣對么?
create table curTest(i number);
--测试数据
insert into curTest select 1 from dual union all select 2 from dual;
--测试包
create or replace package sp_test is
type ResultData is ref cursor;
procedure getcurTest(rst out ResultData);
end sp_test;
/
create or replace package body sp_test is
procedure getcurTest(rst out ResultData) is
begin
open rst for select * from curTest;--返回curTest的内容
end;
end sp_test;
/
--调试包中的getcurTest
declare
cur sp_test.ResultData;
i number;
begin
sp_test.getcurTest(cur);
loop
fetch cur into i;
exit when cur%notfound;
dbms_output.put_line(i);
end loop;
end;
--输出结果
1
2
type ResultData is ref cursor;
procedure getcurTest(rst out ResultData);
function getcur(i number) return ResultData;
end sp_test;
/
create or replace package body sp_test is
procedure getcurTest(rst out ResultData) is
begin
open rst for select * from curTest;
end;
function getcur(i number) return ResultData is
rst ResultData;
begin
open rst for select * from curTest;
return rst;
end;
end sp_test;
/
declare
cur sp_test.ResultData;
i number;
begin
cur:=sp_test.getcur(1);
loop
fetch cur into i;
exit when cur%notfound;
dbms_output.put_line(i);
end loop;
end;
IS
cur sys_refcursor;
str varchar2(20);
BEGIN
cur := df();
fetch cur into str;
close cur ;
END;CREATE OR REPLACE FUNCTION DF RETURN sys_refcursor
IS
cr sys_refcursor;
BEGIN
open cr for select to_char(sysdate,'YYYYMMDD') from dual;
return cr ;
END;
Create Or Replace Package saveTest As
type report_row_type is ref cursor;
function test(VloginID in varchar2) return report_row_type;
function alert(ss in varchar2) return report_row_type;
end saveTest;
/
Create Or Replace Package body saveTest As
function test(VloginID in varchar2)return report_row_type is result report_row_type;
rr report_row_type;
begin
select alert(VloginID)
into rr
from dual; open result for
select 'sdfs' from dual;
return result;
end;function alert(ss in varchar2) return report_row_type is result report_row_type;
begin
dbms_output.put_line('ss==================='||ss);
open result for
select 'sdfs' from dual;
return result;
end;
end saveTest;