oracle ref类型,ORACLE PL/SQL REF参照类型

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);