oracle中REF Cursor用法-MySQL-Tutorial-php.cn

Heim

Datenbank

MySQL-Tutorial

oracle中REF Cursor用法

Jun 07, 2016 pm 05:46 PM

本文章来介绍了关于在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-

Erklärung dieser Website

Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn