oracle 游标变量ref cursor详解

一 介绍

     像游标cursor一样，游标变量ref cursor指向指定查询结果集当前行。游标变量显得更加灵活因为其声明并不绑定指定查询。

其主要运用于PLSQL函数或存储过程以及其他编程语言java等程序之间作为参数传递。

    不像游标的一点，游标变量没有参数。

    游标变量具有以下属性：

    (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT)

二 用法介绍：

1、声明格式：

    DECLARE  

           TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;

2、游标变量又分为强类型strong(with a return type)和弱类型(with no return type)：

    DECLARE
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- 强类型
   TYPE genericcurtyp IS REF CURSOR; -- 弱类型
   cursor1 empcurtyp;
   cursor2 genericcurtyp;
   my_cursor SYS_REFCURSOR; -- 使用预定义游标变量sys_refcursor
   TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;
   dept_cv deptcurtyp; -- 声明游标变量

   或是返回record类型：

   DECLARE
   TYPE EmpRecTyp IS RECORD (
   employee_id NUMBER,
   last_name VARCHAR2(25),
   salary NUMBER(8,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
   emp_cv EmpCurTyp; -- declare cursor variable 

3、使用游标变量作为参数传递：

DECLARETYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;emp empcurtyp;-- after result set is built, process all the rows inside a single procedure-- rather than calling a procedure for each rowPROCEDURE process_emp_cv (emp_cv IN empcurtyp) ISperson employees%ROWTYPE;BEGINDBMS_OUTPUT.PUT_LINE('-----');DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');LOOPFETCH emp_cv INTO person;EXIT WHEN emp_cv%NOTFOUND;DBMS_OUTPUT.PUT_LINE('Name = ' || person.first_name ||' ' || person.last_name);END LOOP;END;BEGIN-- First find 10 arbitrary employees.OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;process_emp_cv(emp);CLOSE emp;-- find employees matching a condition.OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';process_emp_cv(emp);CLOSE emp;END;/

4、使用游标熟悉检查游标变量是否打开

DECLARETYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;emp_cv empcurtyp;BEGINIF NOT emp_cv%ISOPEN THEN -- open cursor variableOPEN emp_cv FOR SELECT * FROM employees;END IF;CLOSE emp_cv;END;/

5、在包package中声明游标变量：

CREATE PACKAGE emp_data ASTYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);END emp_data;/CREATE PACKAGE BODY emp_data ASPROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) ISBEGINOPEN emp_cv FOR SELECT * FROM employees;END open_emp_cv;END emp_data;/

6、提取游标变量到集合类型collection：

DECLARETYPE empcurtyp IS REF CURSOR;TYPE namelist IS TABLE OF employees.last_name%TYPE;TYPE sallist IS TABLE OF employees.salary%TYPE;emp_cv empcurtyp;names namelist;sals sallist;BEGINOPEN emp_cv FOR SELECT last_name, salary FROM employeesWHERE job_id = 'SA_REP';FETCH emp_cv BULK COLLECT INTO names, sals;CLOSE emp_cv;-- loop through the names and sals collectionsFOR i IN names.FIRST .. names.LASTLOOPDBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));END LOOP;END;/

三 游标变量的使用限制：

 1、不能再包说明中声明游标变量；

 2、不能用“=”运算符比较游标变量相等性、不等性及是否为空；

 3、不能存储于表列中；

 4、不能将游标变量存在于关联数组、嵌套表或数组；

 5、游标和游标变量之前是不可互操作的！

--------------------------------------------------------------------------------------

附：

------------------------

1、强类型游标：

CREATE OR REPLACE PACKAGE strongly_typed ISTYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;PROCEDURE child(p_return_rec OUT return_cur);PROCEDURE parent(p_NumRecs PLS_INTEGER);END strongly_typed;/

CREATE OR REPLACE PACKAGE BODY strongly_typed ISPROCEDURE child(p_return_rec OUT return_cur) ISBEGIN  OPEN p_return_rec FOR   SELECT * FROM all_tables; END child;--==================================================PROCEDURE parent (p_NumRecs PLS_INTEGER) IS  p_retcur return_cur; at_rec   all_tables%ROWTYPE;BEGIN  child(p_retcur);  FOR i IN 1 .. p_NumRecs  LOOP    FETCH p_retcur    INTO at_rec;    dbms_output.put_line(at_rec.table_name ||     ' - ' || at_rec.tablespace_name ||     ' - ' || TO_CHAR(at_rec.initial_extent) ||     ' - ' || TO_CHAR(at_rec.next_extent));  END LOOP;END parent;END strongly_typed;/

set serveroutput onexec strongly_typed.parent(1);exec strongly_typed.parent(8);

2、弱类型游标：

CREATE OR REPLACE PROCEDURE child ( p_NumRecs IN PLS_INTEGER, p_return_cur OUT SYS_REFCURSOR)ISBEGIN  OPEN p_return_cur FOR  'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;END child;/CREATE OR REPLACE PROCEDURE parent (pNumRecs VARCHAR2) IS p_retcur  SYS_REFCURSOR; at_rec    all_tables%ROWTYPE;BEGIN  child(pNumRecs, p_retcur);  FOR i IN 1 .. pNumRecs  LOOP    FETCH p_retcur    INTO at_rec;    dbms_output.put_line(at_rec.table_name ||    ' - ' || at_rec.tablespace_name ||    ' - ' || TO_CHAR(at_rec.initial_extent) ||    ' - ' || TO_CHAR(at_rec.next_extent));  END LOOP;END parent;/set serveroutput onexec parent(1);exec parent(17);

3、预定义游标变量：

CREATE TABLE employees (empid   NUMBER(5),empname VARCHAR2(30));INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');COMMIT;CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) ISTYPE array_t IS TABLE OF VARCHAR2(4000)INDEX BY BINARY_INTEGER;rec_array array_t;BEGIN  FETCH p_cursor BULK COLLECT INTO rec_array;  FOR i IN rec_array.FIRST .. rec_array.LAST  LOOP    dbms_output.put_line(rec_array(i));  END LOOP;END pass_ref_cur;/set serveroutput onDECLARE rec_array SYS_REFCURSOR;BEGIN  OPEN rec_array FOR  'SELECT empname FROM employees';  pass_ref_cur(rec_array);  CLOSE rec_array;END;/

-----------------------------------------------------

Dylan    presents.