有存储过程如下
create or replace package pck_test is
type out_cur is ref cursor;
create or replace procedure prc_test(
cur out pck_test.out_cur;
)
is
begin
open cur
for select 1 from dual;
end;
请问,我在oracle中怎么能够得到这个输出参数cur的内容呢?
create or replace package pck_test is
type out_cur is ref cursor;
create or replace procedure prc_test(
cur out pck_test.out_cur;
)
is
begin
open cur
for select 1 from dual;
end;
请问,我在oracle中怎么能够得到这个输出参数cur的内容呢?
create or replace FUNCTION Frc_test RETURN pck_test.out_cur is
V_CURSOR pck_test.out_cur
begin
prc_test(V_CURSOR);
RETURN V_CURSOR;
end;
我是这么写的。
create type typ_test as object (id varchar(10),name varchar(10));
create type tbl_test as table of typ_test;create package pck_test is
type out_cur is ref cursor;
end ;create or replace procedure prc_test (
cur out pck_test.out_cur;
)
as
aobj typ_test := typ_test(null,null);
atbl tbl_test := tbl_test();
begin
for i in 1..6 loop
aobj.id := i;
aobj.name := 'name'||i;
atbl.extend;
atbl(i) := aobj;
end loop;
open cur for
select * from table(cast(atbl as tbl_test));
loop
fetch cur into aobj;
exit when cur%notfound;
dbms_output.put_line(aobj.id||aobj.name);
end loop;
end;
然后在执行存储过程的时候报错说【结果集变量或查询的类型不匹配】。
create or replace procedure prc_test (
cur out pck_test.out_cur;
)
as
aobj typ_test := typ_test(null,null);
atbl tbl_test := tbl_test();
type rec is record(id varchar(10),name varchar(10));
ARec rec;
begin
for i in 1..6 loop
aobj.id := i;
aobj.name := 'name ' ¦ ¦i;
atbl.extend;
atbl(i) := aobj;
end loop;
open cur for
select * from table(cast(atbl as tbl_test));
loop
fetch cur into Arec;
exit when cur%notfound;
dbms_output.put_line(arec.id ¦ ¦arec.name);
end loop;
end;