REF
参照类型(ref cursor 程序间传递结果集)
create or replace package ref_package
as
TYPE emp_record_type IS RECORD
(ename VARCHAR2(25),
job VARCHAR2(10),
sal NUMBER(7,2));
TYPE weak_ref_cursor IS REF CURSOR;--弱类型，不规定返回值
TYPE strong_ref_cursor IS REF CURSOR return emp%rowtype;--强类型，规定返回值
TYPE strong_ref2_cursor IS REF CURSOR return emp_record_type;--强类型，规定返回值
end ref_package;
/
弱类型ref测试:
create or replace procedure test_ref_weak
(p_deptno emp.deptno%type, p_cursor out ref_package.weak_ref_cursor)
is
begin
case p_deptno
when 10 then
open p_cursor for
select empno,ename,sal,deptno
from emp where deptno=p_deptno;
when 20 then
open p_cursor for
select *
from emp where deptno=p_deptno;
end case;
end;
/
var c refcursor
exec test_ref_weak(10,:c); --传入不同形式参数，走不同分支，返回不同结果集！
print c
exec test_ref_weak(20,:c);
print c
*oracle 9i 中定义了系统弱游标类型 sys_refcursor
create or replace procedure test_p
( p_deptno number, p_cursor out sys_refcursor)
is
begin
open p_cursor for
select *
from emp
where deptno = p_deptno;
end test_p;
/
create or replace function getemp
return sys_refcursor
as
emp_cursor sys_refcursor;
begin
open emp_cursor for select * from scott.emp;
return emp_cursor;
end;
/
select getemp from dual;
强类型ref测试:查询结构必须符合游标返回值结构，否则报错：
PLS-00382: expression is of wrong type
create or replace procedure test_ref_strong
(p_deptno emp.deptno%type, p_cursor out ref_package.strong_ref_cursor)
is
begin
open p_cursor for
select *
from emp where deptno=p_deptno;
end test_ref_strong;
/
var c refcursor
exec test_ref_strong(10,:c);
create or replace procedure test_call
is
c_cursor ref_package.strong_ref_cursor;
r_emp emp%rowtype;
begin
test_ref_strong(10,c_cursor);
loop
fetch c_cursor into r_emp;
exit when c_cursor%notfound;
dbms_output.put_line(r_emp.ename);
end loop;
close c_cursor;
end test_call;
/
exec test_call;
强类型ref测试:
create or replace procedure test_ref2_strong
(p_deptno emp.deptno%type, p_cursor out ref_package.strong_ref2_cursor)
is
begin
open p_cursor for
select ename,job,sal
from emp where deptno=p_deptno;
end test_ref2_strong;
/
var c refcursor
exec test_ref2_strong(10,:c);
