给你一个例子: /* 存储过程返回数据集 *//* 1.建立带ref cursor定义的包和包体及过程 */ create or replace package pkg_test as type myrctype is ref cursor; procedure display(p_empno char,p_rc out myrctype); end;create or replace package body pkg_test as procedure display(p_empno char,p_rc out myrctype) is sqlst varchar2(100); begin if p_empno is null then open p_rc for select emp_name from student; else sqlst := 'select emp_name from student where emp_no = :w_empno'; open p_rc for sqlst using p_empno; end if; end; end;调用: declare w_rc pkg_test.myrctype; w_empname student.emp_name%type; begin pkg_test.display('0001', w_rc); loop fetch w_rc into w_empname; exit when w_rc%notfound; dbms_output.put_line(w_empname); end loop; end;
/* 存储过程返回数据集 *//* 1.建立带ref cursor定义的包和包体及过程 */
create or replace package pkg_test
as
type myrctype is ref cursor;
procedure display(p_empno char,p_rc out myrctype);
end;create or replace package body pkg_test
as
procedure display(p_empno char,p_rc out myrctype)
is
sqlst varchar2(100);
begin
if p_empno is null then
open p_rc for select emp_name from student;
else
sqlst := 'select emp_name from student where emp_no = :w_empno';
open p_rc for sqlst using p_empno;
end if;
end;
end;调用:
declare
w_rc pkg_test.myrctype;
w_empname student.emp_name%type;
begin
pkg_test.display('0001', w_rc); loop
fetch w_rc into w_empname;
exit when w_rc%notfound;
dbms_output.put_line(w_empname);
end loop;
end;