本文章来介绍了关于在oracle中ref cursor用法，有需要的朋友可以参考一下下。
本文章来介绍了关于在oracle中ref cursor用法，有需要的朋友可以参考一下下。
1，什么是 ref游标 ？
动态关联结果集的临时对象。即在运行的时候动态决定执行查询。
2，ref 游标 有什么作用？
实现在程序间传递结果集的功能，利用ref cursor也可以实现bulk sql，从而提高sql性能。
3，静态游标和ref 游标的区别是什么？
①静态游标是静态定义，ref 游标是动态关联；
②使用ref 游标需ref 游标变量。
③ref 游标能做为参数进行传递，而静态游标是不可能的。
4，什么是ref 游标变量？
ref游标变量是一种 引用 ref游标类型 的变量，指向动态关联的结果集。
5，怎么使用 ref游标 ？
①声明ref 游标类型，确定ref 游标类型；
⑴强类型ref游标：指定retrun type，ref 游标变量的类型必须和return type一致。
语法：type ref游标名 is ref cursor return 结果集返回记录类型；
⑵弱类型ref游标：不指定return type，能和任何类型的cursor变量匹配，用于获取任何结果集。
语法：type ref游标名 is ref cursor；
②声明ref 游标类型变量；
语法：变量名 已声明ref 游标类型；
③打开ref游标，关联结果集 ；
语法：open ref 游标类型变量 for 查询语句返回结果集；
④获取记录，操作记录；
语法：fatch ref游标名 into 临时记录类型变量或属性类型变量列表；
⑤关闭游标，完全释放资源；
语法：close ref游标名；
例子：强类型ref游标
代码如下 复制代码
/*conn scott/tiger*/
declare
type myrefcura is ref cursor return emp%rowtype;
type myrefcurb is ref cursor return emp.ename%type;
vrefcura myrefcura;
vrefcurb myrefcurb;
vtempa vrefcura%rowtype;
vtempb vrefcurb.ename%type;
begin
open vrefcura for select * from emp where sal > 2000;
loop
fatch vrefcura into vtempa;
exit when vrefcura%notfound;
dbms_output.put_line(vrefcura%rowcount||' '|| vtempa.eno||' '||vtempa.ename ||' '||vtempa.sal)
end loop;
close vrefcura;
dbms_output.put_line('-------------------------------------------------------------------------------------------------------');
open vrefcurb for select ename from emp where sal > 2000;
loop
fatch vrefcurb into vtempb;
exit when vrefcurb%notfound;
dbms_output.put_line(vrefcurb%rowcount||' '||vtempb)
end loop;
close vrefcurb;
dbms_output.put_line('-------------------------------------------------------------------------------------------------------');
open vrefcura for select * from emp where job = 'clerk';
loop
fatch vrefcura into vtempa;
exit when vrefcura%notfound;
dbms_output.put_line(vrefcura%rowcount||' '|| vtempa.eno||' '||vtempa.ename ||' '||vtempa.sal)
end loop;
close vrefcura;
end;
例子：弱类型ref游标
代码如下 复制代码
/*conn scott/tiger*/
declare
type myrefcur is ref cursor;
vrefcur myrefcur;
vtemp vrefcur%rowtype;
begin
case(&n)
when 1 then open vrefcur for select * from emp;
when 2 then open vrefcur for select * from dept;
else
open vrefcur for select eno, ename from emp where job = 'clerk';
end case;
close vrefcur;
end;
6，怎样让ref游标作为参数传递？
代码如下 复制代码
--作为函数返回值
create or replace function returnacursor return sys_refcursor
is
v_csr sys_refcursor;
begin
open v_csr for select a1 from test3;
return v_csr;
end;
/
declare
c sys_refcursor;
a1 char(2);
begin
c:=returnacursor;
loop
fetch c into a1;
exit when c%notfound;
dbms_output.put_line(a1);
end loop;
close c;
end;
/
--作为参数
create or replace procedure proc_ref_cursor (rc in sys_refcursor) as
v_a number;
v_b varchar2(10);
begin
loop
fetch rc into v_a, v_b;
exit when rc%notfound;
dbms_output.put_line(v_a || ' ' || v_b);
end loop;
end;
/
declare
v_rc sys_refcursor;
begin
open v_rc for
select a1,a2 from test3;
proc_ref_cursor(v_rc);
close v_rc;
end;
/
ref cursor 示例包括下列三个 visual basic 示例，演示如何使用 ref cursor。
示例 说明
在 oracledatareader 中检索 ref cursor 参数
此示例执行一个 pl/sql 存储过程，返回 ref cursor 参数，并将值作为 oracledatareader 读取。
使用 oracledatareader 从多个 ref cursor 检索数据
此示例执行一个 pl/sql 存储过程，返回两个 ref cursor 参数，并使用 oracledatareader 读取值。
使用一个或多个 ref cursor 填充 dataset
此示例执行一个 pl/sql 存储过程，返回两个 ref cursor 参数，并使用返回的行填充 dataset。
要使用这些示例，可能需要创建 oracle 表，并且必须创建 pl/sql 包和包正文。
创建 oracle 表
这些示例使用 oracle scott/tiger 架构中定义的表。大多数 oracle 安装均包括 oracle scott/tiger 架构。如果此架构不存在，可以使用 {oraclehome}rdbmsadminscott.sql 中的 sql 命令文件创建供这些示例使用的表和索引。
创建 oracle 包和包正文
这些示例要求服务器上存在以下 pl/sql 包和包正文。在 oracle 服务器上创建以下 oracle 包
代码如下 复制代码
create or replace package body curspkg as
procedure open_one_cursor (n_empno in number,
io_cursor in out t_cursor)
is
v_cursor t_cursor;
begin
if n_empno 0
then
open v_cursor for
select emp.empno, emp.ename, dept.deptno, dept.dname
from emp, dept
where emp.deptno = dept.deptno
and emp.empno = n_empno;
else
open v_cursor for
select emp.empno, emp.ename, dept.deptno, dept.dname
from emp, dept
where emp.deptno = dept.deptno;
end if;
io_cursor := v_cursor;
end open_one_cursor;
procedure open_two_cursors (empcursor out t_cursor,
deptcursor out t_cursor)
is
v_cursor1 t_cursor;
v_cursor2 t_cursor;
begin
open v_cursor1 for select * from emp;
open v_cursor2 for select * from dept;
empcursor := v_cursor1;
deptcursor := v_cursor2;
end open_two_cursors;
end curspkg;
/
oracle提供ref cursor，通过该功能可以实现在程序间传递结果集的功能，利用ref cursor也可以实现bulk sql，从而提高sql性能。
使用scott用户的emp表实现以下测试案例：
代码如下 复制代码
sql> desc emp
name null? type
----------------------------------------- -------- ----------------------------
empno not null number(4)
ename varchar2(10)
job varchar2(9)
mgr number(4)
hiredate date
sal number(7,2)
comm number(7,2)
deptno number(2)
使用ref cursor获得结果集输出：
sql> set serveroutput on
sql> declare
2 type mytable is table of emp%rowtype;
3 l_data mytable;
4 l_refc sys_refcursor;
5 begin
6 open l_refc for
7 select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;
8
9 fetch l_refc bulk collect into l_data;
10
11 close l_refc;
12
13 for i in 1 .. l_data.count
14 loop
15 dbms_output.put_line ( l_data (i).ename
16 || ' was hired since '
17 || l_data (i).hiredate
18 );
19 end loop;
20 end;
21 /
smith was hired since 17-dec-80
allen was hired since 20-feb-81
ward was hired since 22-feb-81
jones was hired since 02-apr-81
martin was hired since 28-sep-81
blake was hired since 01-may-81
clark was hired since 09-jun-81
scott was hired since 19-apr-87
king was hired since 17-nov-81
turner was hired since 08-sep-81
adams was hired since 23-may-87
james was hired since 03-dec-81
ford was hired since 03-dec-81
miller was hired since 23-jan-82
pl/sql procedure successfully completed.
-the end-